Thread: Logging all queries
Attachment
I don't know if there is a better way, but you could enable log_connections and log_pid.
This way, you will get in your log file something like this:
2004-01-28 10:39:28 [79227] LOG: connection authorized: user=admin database=test
...
2004-01-28 10:39:33 [79227] LOG: statement: select * from people;
...
2004-01-28 10:39:35 [79227] LOG: statement: delete from people where name like 'f%';
...
you could use grep to filter connections (and their pids), and the you could grep again to find out what queries each pid executed.
If you are interested only in a particular table, then you could use a trigger AFTER INSERT OR UPDATE OR DELETE (not select), and there you could code a function to register what changes are being made to your table.
hope this helps
On Wed, 2004-01-28 at 09:36, MaRCeLO PeReiRA wrote:
This way, you will get in your log file something like this:
2004-01-28 10:39:28 [79227] LOG: connection authorized: user=admin database=test
...
2004-01-28 10:39:33 [79227] LOG: statement: select * from people;
...
2004-01-28 10:39:35 [79227] LOG: statement: delete from people where name like 'f%';
...
you could use grep to filter connections (and their pids), and the you could grep again to find out what queries each pid executed.
If you are interested only in a particular table, then you could use a trigger AFTER INSERT OR UPDATE OR DELETE (not select), and there you could code a function to register what changes are being made to your table.
hope this helps
On Wed, 2004-01-28 at 09:36, MaRCeLO PeReiRA wrote:
Hi All, I have set [log_statement=true] in the postgresql.conf file and now I have all the statements logged. But, there is a problem: Once I have more than one database, I had to know in which database a statement was executed on. The log file is full of: "select * from stuff", but as I have more than one database, how can I know where the [stuff] table is?????? Regards, Marcelo --- MaRCeLO PeReiRA <gandalf_mp@yahoo.com.br> escreveu: > Hi Franco, > > If I set 'log_statement=true', will *all* the > queries > sent to the backend be logged??? > > In which table will it be used to do this log?? > > Regards, > > Marcelo > > --- Franco Bruno Borghesi > <franco@akyasociados.com.ar> escreveu: > you can set > the log_statement parameter to true in > > the configuration > > file ($PGDATA/postgresql.conf). > > > > On Tue, 2004-01-27 at 16:39, MaRCeLO PeReiRA > wrote: > > > > > Hi All, > > > > > > Is there any way to log (save) "all" the queries > > > (select, insert, updade, everything) executed on > a > > > database, in a table?? > > > > > > Regards, > > > > > > Marcelo > > > > > > > > > ______________________________________________________________________ > > > > > > Yahoo! GeoCities: a maneira mais fácil de criar > > seu web site grátis! > > > http://br.geocities.yahoo.com/ > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > > ATTACHMENT part 2 application/pgp-signature > name=signature.asc > > > ______________________________________________________________________ > > Yahoo! GeoCities: a maneira mais fácil de criar seu > web site grátis! > http://br.geocities.yahoo.com/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ______________________________________________________________________ Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis! http://br.geocities.yahoo.com/