Re: [GENERAL] Transaction logging

Hi !

Peter T Mount <peter(at)retep(dot)org(dot)uk> writes:
> > Has anyone implemented transaction logging in Postgres? Any suggestions on
> > how to easily implement transaction logging? Storing the log file in a text
> > file seems best but I am not sure out to open and write to a text file from
> > a trigger. I would also be nice to post this transaction log against a back
> > up server.

> Just a quick thought, but how about using syslog? That can be used to post
> queries to a remote server, and it can be told to store the "postgres"
> stuff to a seperate file on that server.

> Just an idea...

Why not, but I think it's a bad idea. Syslog is used to log
events coming from the system. It stores every connection to the
system, and any event which can affect the system (such as power
shutdown).

The transaction logging is a different taste of log : it must
store every transaction made to the database, and in case of deletion
of records, or change to data, it must save the old values. So it
generates a lot of traffic, and is closely dependant of the database
system.

Syslog is not strong enough to deal with so much data, and the
use of an external process to get the transaction logging would
generate too much traffic (the cost in time would be too high). The
logging facility would, I think, be made by the database itself.

Anything else : the logging facility is used to recover the
database after a crash (mainly). This kind of log _must_ be easy to
use in case of crash. Syslog is very well when you won't to know what
append, but not to deal with the problem. Don't forget that Syslog add
some data to the events we send to him (such as the sender and the
date of the message). These data, in case of recovery by transaction
logging mechanism, are noise, which get the recovery (a little bit)
harder.

I don't think that we could get a logging facility with the
use of triggers. I think it would be better to hack the postgres
backend, and supersedes the access to SQL primitives (like insert or
update). It would be a little harder to implement, but faster and
totally transparent to the user.

regards.

--
___
{~._.~} Stephane - DUST - Dupille
( Y ) You were dust and you shall turn into dust
()~*~() email : sdupille(at)i-france(dot)com
(_)-(_)