Transaction logging

On Mon, 22 Feb 1999, Michael Davis wrote:

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...

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

Hi !

Peter T Mount <peter@retep.org.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@i-france.com
(_)-(_)

This is a great idea. I have not been successful in tracking any
information on syslog. Any suggestions on where I might find some good
documentation on syslog?

-----Original Message-----
From: Peter T Mount [SMTP:peter@retep.org.uk]
Sent: Tuesday, February 23, 1999 3:40 AM
To: Michael Davis
Cc: 'pgsql-general@postgreSQL.org'
Subject: Re: [GENERAL] Transaction logging

On Mon, 22 Feb 1999, Michael Davis wrote:

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...

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

This is a great idea. I have not been successful in tracking any
information on syslog. Any suggestions on where I might find some good
documentation on syslog?

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.

i don't know that syslog is robust enough for what you want.

i don't think it has any error checking to verify that a message was
received, etc.

most BSD's and linux would have good refernce ports of syslogd, etc.

--
[ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]

Don't use syslog under any circumstances!!!!

1. Syslog breaks up records longer than X number of bytes - you'd
have to piece them together.

2. Communication to syslog is over udp - (i.e. unreliable message
sends) thus message loss can occur.

3. All the other complaints about syslog already mentioned: additional
data added to the logs, etc.

Jim Mercer wrote:

This is a great idea. I have not been successful in tracking any
information on syslog. Any suggestions on where I might find some good
documentation on syslog?

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.

i don't know that syslog is robust enough for what you want.

i don't think it has any error checking to verify that a message was
received, etc.

most BSD's and linux would have good refernce ports of syslogd, etc.

--
[ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]

--
------------------------------------------------------------
Thomas Reinke Tel: (416) 460-7021
Director of Technology Fax: (416) 598-2319
E-Soft Inc. http://www.e-softinc.com

Don't use syslog under any circumstances!!!!

on a slightly different tact, it would be a good thing(tm) if postmaster
and the postgres backends used syslog for logging debug and other information.

i've tried enabling debug via the postmaster command, and haven't ever seen
anything overly useful.

--
[ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]

On Tue, 23 Feb 1999, Jim Mercer wrote:

# on a slightly different tact, it would be a good thing(tm) if postmaster
# and the postgres backends used syslog for logging debug and other
# information.
#
# i've tried enabling debug via the postmaster command, and haven't ever
# seen anything overly useful.

For this type of logging, I pipe my postmaster to logger.

--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

I evoke postmaster this way from an rc script:

su - postgres -c "/usr/local/pgsql/bin/postmaster -d 2 -i 2>&1 |
logger -p local7.notice -tPOSTMASTER &"

And I get copious debugging information (-d takes 1, 2 or 3)...

Don't forget to edit /etc/syslog.conf to direct local7.notice to a
file, ala:

local7.notice /var/adm/postmaster.log

DwD

On Tue, 23 Feb 1999, Thomas Reinke wrote:

Don't use syslog under any circumstances!!!!

Which is why I added: Just an Idea.

At least it got people thinking about the problem.

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf