Thread: log file?

I'm sorry about asking this here, since I'm sure it is a FAQ.

I'm using an ecommerce app (interchange) and it's giving me some database
errors (unterminated quoted string, blah blah).  I would like to see the
SQL code as it is being executed (or given) to postgresql.  Since my app
doesn't have a log that tells exactly what it is saying to postgresql, I
assume postgresql has this kind of logging capability.  (I was thinking of
a file that I could just 'tail -f'.)  It would really help me debug my app.

Thanks.


Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com

Hi,

This is what I do to get all queries in a log file:

1.  create a file called pg_options in your postgres home directory. In this
file, enter the word "query".  Nothing else (if you look at the docs, there
are other things you can put in here).

2.  Find out where stdout/stderr are going from postgres.  This depends on
how you are starting postgres.  I start my postmaster with the line:

/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster start >
~postgres/logs/postmaster.log 2>&1

so my log file is ~postgres/logs/postmaster.log and contains all the
queries.

Hope this helps!

Tamsin


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Dan B
> Sent: 15 January 2001 07:37
> To: pgsql-novice@postgresql.org
> Cc: danb@cyclonecomputers.com
> Subject: [NOVICE] log file?
>
>
> I'm sorry about asking this here, since I'm sure it is a FAQ.
>
> I'm using an ecommerce app (interchange) and it's giving me some database
> errors (unterminated quoted string, blah blah).  I would like to see the
> SQL code as it is being executed (or given) to postgresql.  Since my app
> doesn't have a log that tells exactly what it is saying to postgresql, I
> assume postgresql has this kind of logging capability.  (I was
> thinking of
> a file that I could just 'tail -f'.)  It would really help me
> debug my app.
>
> Thanks.
>
>
> Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
>
>

Dan B <db@cyclonehq.dnsalias.net> writes:
> I'm sorry about asking this here, since I'm sure it is a FAQ.
> I'm using an ecommerce app (interchange) and it's giving me some database
> errors (unterminated quoted string, blah blah).  I would like to see the
> SQL code as it is being executed (or given) to postgresql.  Since my app
> doesn't have a log that tells exactly what it is saying to postgresql, I
> assume postgresql has this kind of logging capability.  (I was thinking of
> a file that I could just 'tail -f'.)  It would really help me debug my app.

(1) Make sure the postmaster is being started without -S switch, and
redirect its stdout and stderr into a log file of your choice.  For
example,
    postmaster -i -D /whatever >/path/to/logfile 2>&1 &

(2) To cause queries to be logged, you also need -d2 or higher.  You
can add that to the postmaster's startup command, but your log file
will probably grow rapidly if you turn it on for everything.  There is a
way to establish -d2 just for a selected client, however, which is to
set the PGOPTIONS environment variable for the client:
    export PGOPTIONS="-d2"
    psql (or other client program)
libpq will see the PGOPTIONS setting and pass it over to the backend.

            regards, tom lane

>(2) To cause queries to be logged, you also need -d2 or higher.  You
>can add that to the postmaster's startup command, but your log file
>will probably grow rapidly if you turn it on for everything.

This doesn't seem to catch ERROR or NOTICE messages however, are these not
generated by the backend but something higher up towards the calling
interface? So how can you catch such messages? I found something regarding
using syslog in the docs but it dodn't go into a lot of detail on how to
grab these.

cheers

Jason Davis

Jason Davis <jdavis@tassie.net.au> writes:
>> (2) To cause queries to be logged, you also need -d2 or higher.  You
>> can add that to the postmaster's startup command, but your log file
>> will probably grow rapidly if you turn it on for everything.

> This doesn't seem to catch ERROR or NOTICE messages however,

It doesn't?  The logfile should collect ERROR/NOTICE messages regardless
of the -d switch level.  It does for me anyway.

            regards, tom lane