Logging question

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:

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