PostgreSQL logging with syslog-ng

Today I where about to analyze a PostgreSQL database with the wonderful pgFouine. On the machine syslog-ng where installed as logging daemon and I have never worked with PostgreSQL logging and syslog-ng. This post is about how to get them to work together.

pgFouine is pretty simple to setup and the process is where well documented at their website (http://pgfouine.projects.postgresql.org/tutorial.html).

However syslog-ng is very different if you are used to “normal” syslog daemon.

The first thing you have to do is setup PostgreSQL as the instructions says on the website. The next step is to tell syslog-ng to log you postgres data to a separate file.

The first thing to create in syslog-ng.conf is a destination. Destination blocks is used to send logs somewhere.

By adding the following row we tell syslog-ng that there is a destination called postgres and that we want this data in /var/log/pgsql.

1
destination postgres { file("/var/log/pgsql"); };

Now we are ready to add a filter to caputre our PostgreSQL data. Filters can both be used to include or exclude data from you logs. The following row in your syslog-conf captures all data sent to local0 or whatever you called it in postgresql.conf :-)

1
filter f_postgres { facility(local0); };

It’s time to join our destination with our filter and the following line tells syslog-ng to join our source, destination and filter together.

1
log { source(src); filter(f_postgres); destination(postgres); };

Put these lines in your syslog-ng.conf and restart syslog-ng and also PostgreSQL and you will have logging in a separate file!

Next step is to write a little guide to pgFouine 😉

Cheers!

Posted in Databases, Hosting, PostgreSQL at December 15th, 2008. Trackback URI: trackback Tags: , , , Written by: 
  • arag0rn

    Hello.

    I think you mean source(sys), not source(src).

    Cheers!
    Marcin

  • Mathias Stjernström

    Thanks for your comment Marcin.

    It seems that you get source(src) by default when installing syslog-ng under Gentoo. I’ve tried to find information about the difference between sys/src but from what i know/can find its just a name referring to that group of messages.

    Cheers!

It's past my bedtime is using WP-Gravatar