Thread: Logging question
Hi all, This is probably an easy question but I couldn't my answer in the docs (I probably looked right at it...). Is there any way I can log and/or display database calls for a specific database? I am trying to debug a third party program and I can see that the problem probably has something to do with the DB call (and insert statement). I normally would use a print inside the program to see what is being called but this program uses DB calls in a way I am not familiar with. If could see what is actually being sent it would make my life a lot easier. I don't want to just enable logging though because there is another quite active database on the same system. Thanks!! Madison
On Mon, Jan 17, 2005 at 09:03:17PM -0500, Madison Kelly wrote: > Is there any way I can log and/or display database calls for a > specific database? I don't know of a way to enable logging for a specific database, but you can enable logging for a specific user or session. For example, you could cause johndoe's statements to be logged by doing one of the following: ALTER USER johndoe SET log_statement TO TRUE; -- 7.x ALTER USER johndoe SET log_statement TO 'all'; -- 8.0 All new connections that johndoe makes will now have statement logging enabled. To enable logging for a particular session, execute the appropriate "SET log_statement" statement in that session. If you have permission problems then you could wrap that operation in a function defined as SECURITY DEFINER and create the function as a database superuser. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Madison Kelly wrote: > Hi all, > > This is probably an easy question but I couldn't my answer in the > docs (I probably looked right at it...). > > Is there any way I can log and/or display database calls for a > specific database? I am trying to debug a third party program and I > can see that the problem probably has something to do with the DB call > (and insert statement). I normally would use a print inside the > program to see what is being called but this program uses DB calls in > a way I am not familiar with. If could see what is actually being sent > it would make my life a lot easier. http://www.ethereal.com/ I use ethereal (on the client--although it would possibly work on the server too) in such a case. It's a bit messy but if you just want to see what SQL statements were being run, then it works. I also saw some where that the new version of ethereal has a dissector for Postgres, which would presumably show you the communication between the client and server in a nicely-formatted way. Paul Tillotson
Michael, This is great info, is there a way to get the log back via a SQL call? It would be pretty cool if there was a way to have the log go to a table instead of a file. Tony Caduto Michael Fuhr wrote: > >ALTER USER johndoe SET log_statement TO TRUE; -- 7.x >ALTER USER johndoe SET log_statement TO 'all'; -- 8.0 > >All new connections that johndoe makes will now have statement >logging enabled. > >To enable logging for a particular session, execute the appropriate >"SET log_statement" statement in that session. If you have permission >problems then you could wrap that operation in a function defined >as SECURITY DEFINER and create the function as a database superuser. > > >
On Mon, Jan 17, 2005 at 10:06:20PM -0500, Paul Tillotson wrote: > I use ethereal (on the client--although it would possibly work on the > server too) in such a case. It's a bit messy but if you just want to > see what SQL statements were being run, then it works. Caveats: sniffers like ethereal or tcpdump won't work over connections that use local (Unix domain) sockets, and SSL connections might be problematic. > I also saw some where that the new version of ethereal has a dissector > for Postgres, which would presumably show you the communication > between the client and server in a nicely-formatted way. Ethereal 0.10.8 appears to have a PostgreSQL dissector: Request "create table foo (x integer); Response CREATE TABLE Request insert into foo values (1); Response INSERT 0 1 Request insert into foo values (2); Response INSERT 0 1 Request insert into foo values (3); Response INSERT 0 1 Request select * from foo; Response 1D 2D 3C SELECT -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, Jan 17, 2005 at 09:13:19PM -0600, Tony Caduto wrote: > This is great info, is there a way to get the log back via a SQL call? > It would be pretty cool if there was a way to have the log go to a table > instead of a file. Not that I'm aware of, but you could write a function to read the log file if you know where it is. That would be easy in a language like PL/Python or PL/Perl. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Jan 17, 2005 at 09:03:17PM -0500, Madison Kelly wrote:
>> Is there any way I can log and/or display database calls for a
>> specific database?
> I don't know of a way to enable logging for a specific database,
> but you can enable logging for a specific user or session.
> ALTER USER johndoe SET log_statement TO TRUE; -- 7.x
You forgot that ALTER DATABASE has this same option. It might be that
ALTER USER is just as convenient, or even more so, for Madison's problem
... but it *can* be set at the database scope if needed.
regards, tom lane
Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > >>On Mon, Jan 17, 2005 at 09:03:17PM -0500, Madison Kelly wrote: >> >>>Is there any way I can log and/or display database calls for a >>>specific database? > > >>I don't know of a way to enable logging for a specific database, >>but you can enable logging for a specific user or session. > > >>ALTER USER johndoe SET log_statement TO TRUE; -- 7.x > > > You forgot that ALTER DATABASE has this same option. It might be that > ALTER USER is just as convenient, or even more so, for Madison's problem > ... but it *can* be set at the database scope if needed. > > regards, tom lane > Can I ask a horribly embarrising question? Where /is/ the log file? I've looked in the config file, in the init file, in /var/log, on google... no luck! ^.^; Madison
Madison Kelly <linux@alteeve.com> writes:
> Where /is/ the log file?
It depends. If you selected syslog logging then it's wherever syslog is
configured to put the messages. Otherwise, it's wherever the
postmaster's stderr output is being sent.
A fairly annoying property of the current RPM packagings is that their
startup script sends postmaster stderr to /dev/null. We have (finally)
fixed that for 8.0, but in current releases you need to alter the
startup script in order to get useful log output.
regards, tom lane
On Mon, Jan 17, 2005 at 11:41:34PM -0500, Tom Lane wrote: > > You forgot that ALTER DATABASE has this same option. It might be that > ALTER USER is just as convenient, or even more so, for Madison's problem > ... but it *can* be set at the database scope if needed. Drat, thanks for the reminder. And yep, there it is, right at the top of the ALTER DATABASE documentation...that's what I get for not checking. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Jan 18, 2005, at 13:57, Michael Fuhr wrote: > On Mon, Jan 17, 2005 at 11:41:34PM -0500, Tom Lane wrote: >> >> You forgot that ALTER DATABASE has this same option. It might be that >> ALTER USER is just as convenient, or even more so, for Madison's >> problem >> ... but it *can* be set at the database scope if needed. > > Drat, thanks for the reminder. And yep, there it is, right at the > top of the ALTER DATABASE documentation...that's what I get for not > checking. Michael, Just for the record, I'd like to state that I've been incredibly impressed with your fast response time and depth and accuracy of answers. You're a machine :) I've been learning a lot lurking. Thanks! Michael Glaesemann grzm myrealbox com
Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>>Where /is/ the log file?
>
>
> It depends. If you selected syslog logging then it's wherever syslog is
> configured to put the messages. Otherwise, it's wherever the
> postmaster's stderr output is being sent.
>
> A fairly annoying property of the current RPM packagings is that their
> startup script sends postmaster stderr to /dev/null. We have (finally)
> fixed that for 8.0, but in current releases you need to alter the
> startup script in order to get useful log output.
>
> regards, tom lane
woohoo!!
Thank you, I would have taken forever to figure that out on my own...
For the record on Fedora Core 1 the line to change is:
'/etc/rc.d/rc3.d/s85postgresql' line 171
Which I changed from:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p
/usr/bin/postmaster -o '-p ${PGPORT}' start > /dev/null 2>&1" < $
to...
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p
/usr/bin/postmaster -o '-p ${PGPORT}' start > /var/log/psql.log $#
I had to touch the 'psql.log' file and then 'chown' it to
'postgres:postgres' but now my queries are being logged. Adding the earlier:
ALTER USER johndoe SET log_statement TO TRUE;
that Michael Fuhr mentioned and I have exactly what I wanted happening.
Thank you all -very- much!
Madison
On Mon, 17 Jan 2005 23:44:37 -0500, Madison Kelly <linux@alteeve.com> wrote: > Tom Lane wrote: > > Michael Fuhr <mike@fuhr.org> writes: > > > >>On Mon, Jan 17, 2005 at 09:03:17PM -0500, Madison Kelly wrote: > >> > >>>Is there any way I can log and/or display database calls for a > >>>specific database? > > > > > >>I don't know of a way to enable logging for a specific database, > >>but you can enable logging for a specific user or session. > > > > > >>ALTER USER johndoe SET log_statement TO TRUE; -- 7.x > > > > > > You forgot that ALTER DATABASE has this same option. It might be that > > ALTER USER is just as convenient, or even more so, for Madison's problem > > ... but it *can* be set at the database scope if needed. > > > > regards, tom lane > > > > Can I ask a horribly embarrising question? > > Where /is/ the log file? I've looked in the config file, in the init > file, in /var/log, on google... no luck! ^.^; In the 'official' 7.4.x RPMs look for the PGLOG variable in /etc/init.d/postgresql and set that to where you want to generate the log. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org