Just a Theory

By David E. Wheeler

Enable CSV Logging in PostgreSQL

One of the cooler features of recent versions of PostgreSQL is support for CSV-formatted logging. I’ve never had a chance to use it, but after reading Josh’s cool hack for determining sums of concurrent queries using windowing functions in PostgreSQL 8.4 to query a table generated from a CSV log, I just had to give it a try. But while there is decent documentation for loading up the contents of a CSV-formatted log file, there I couldn’t find simple information on how to set it up. So I figured it out and record it here for posterity.

Configuration is pretty straight-forward. You need to edit only two configuration directives (known as “GUCs” in PostgreSQL parlance) in your postgresql.conf file: log_destination and logging_collector. I just changed them from:

log_destination = 'stderr'
#logging_collector = off

To:

log_destination = 'csvlog'
logging_collector = on

Then all I had to do was cold-restart PostgreSQL; that is, stop it and start it again. I’m told that a restart won’t due for security reasons. After that, I had a shiny new .csv log file in the pg_log subdirectory of my data directory. It looks like this:

2009-08-19 10:44:08.128 PDT,,,36596,,4a8c39e8.8ef4,1,,2009-08-19 10:44:08 PDT,,0,LOG,00000,"database system was shut down at 2009-08-19 10:44:06 PDT",,,,,,,,
2009-08-19 10:44:08.411 PDT,,,36573,,4a8c39e7.8edd,1,,2009-08-19 10:44:07 PDT,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,
2009-08-19 10:44:08.412 PDT,,,36599,,4a8c39e8.8ef7,1,,2009-08-19 10:44:08 PDT,,0,LOG,00000,"autovacuum launcher started",,,,,,,,

Cool!

The only other thing I ought to note is that, becaus I removed the “stderr” value from the log_destination GUC, the old log location I used, specified in my start script, is no longer necessary. I was even using log rotation (in the default Mac OS X start script that ships with PostgreSQL), but that’s not necessary anymore, either. So I just turned that stuff off.

Now I have something to refer back to, and so do you. Enjoy!

Looking for the comments? Try the old layout.

More about…