Professional Web Applications Themes

Logging question - PostgreSQL / PGSQL

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 ...

  1. #1

    Default 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

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Madison Kelly Guest

  2. #2

    Default Re: 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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  3. #3

    Default Re: Logging question

    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.
    [url]http://www.ethereal.com/[/url]

    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

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Paul Tillotson Guest

  4. #4

    Default Re: Logging question

    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.
    >
    >
    >

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Tony Caduto Guest

  5. #5

    Default Re: Logging question

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Michael Fuhr Guest

  6. #6

    Default Re: Logging question

    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
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Michael Fuhr Guest

  7. #7

    Default Re: Logging question

    Michael Fuhr <mikefuhr.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

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Tom Lane Guest

  8. #8

    Default Re: Logging question

    Tom Lane wrote:
    > Michael Fuhr <mikefuhr.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

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Madison Kelly Guest

  9. #9

    Default Re: Logging question

    Madison Kelly <linuxalteeve.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

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Tom Lane Guest

  10. #10

    Default Re: Logging question

    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 doentation...that's what I get for not
    checking.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Fuhr Guest

  11. #11

    Default Re: Logging question


    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 doentation...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


    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Glaesemann Guest

  12. #12

    Default Re: Logging question

    Tom Lane wrote:
    > Madison Kelly <linuxalteeve.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

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Madison Kelly Guest

  13. #13

    Default Re: Logging question

    On Mon, 17 Jan 2005 23:44:37 -0500, Madison Kelly <linuxalteeve.com> wrote:
    > Tom Lane wrote:
    > > Michael Fuhr <mikefuhr.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 [email]netllama[/email]
    LlamaLand [url]http://netllama.linux-sxs.org[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Lonni J Friedman Guest

Similar Threads

  1. Logging Settings Question
    By cfsmith in forum Coldfusion Server Administration
    Replies: 0
    Last Post: February 23rd, 06:44 PM
  2. logging proftpd question
    By David Banning in forum FreeBSD
    Replies: 3
    Last Post: February 24th, 01:00 AM
  3. logging
    By j in forum PERL Beginners
    Replies: 5
    Last Post: October 28th, 04:23 PM
  4. Logging on to XP pro????
    By fotoguy007 in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 11th, 01:56 AM
  5. Logging in help
    By Tiffany in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: June 30th, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139