Skip site navigation (1) Skip section navigation (2)

Re: Logging all queries (almost there)

From: Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar>
To: MaRCeLO PeReiRA <gandalf_mp(at)yahoo(dot)com(dot)br>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Re: Logging all queries (almost there)
Date: 2004-01-28 13:52:32
Message-ID: 1075297952.3304.14.camel@taz.oficina (view raw or flat)
Thread:
Lists: pgsql-general
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:

> 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(at)yahoo(dot)com(dot)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(at)akyasociados(dot)com(dot)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(at)postgresql(dot)org 
> 
> ______________________________________________________________________
> 
> Yahoo! GeoCities: a maneira mais fácil de criar seu web site grátis!
> http://br.geocities.yahoo.com/
> 

In response to

pgsql-general by date

Next:From: Net Llama!Date: 2004-01-28 14:04:48
Subject: Re: Is autocommit=true bad?
Previous:From: Marco LazzeriDate: 2004-01-28 13:51:15
Subject: Re: Seaching without accents

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group