I'm using PostgreSQL (8.3) with multiple databases... I'm wondering if there is some way to log the queries made only in one of the databases (not all of them).

Or to have one logfile per database...

I know I can use log_line_prefix = "%d" to log the name of the database, and then filter, but that is not the issue.

Should I maybe use a log_analyzer to get around this ? Do you have any recommendations ?


  • should I post this elsewhere ? where ?
    – arod
    Apr 4, 2012 at 17:44
  • If you're not satisfied with any of the answers you've gotten here let me know and I can move it to our DBA site (dba.stackexchange.com). Frank's answer seems to be exactly what you're looking for though.
    – voretaq7
    Apr 9, 2012 at 17:24

2 Answers 2


Yes, this is possible, you can set the configuration parameter log_statement per database:

ALTER DATABASE your_database_name
SET log_statement = 'all';
  • I'm embarrassed to admit I've never seen this before -- has it been around a while?
    – voretaq7
    Apr 9, 2012 at 17:23
  • I really didn't try out this answer, since I don't need it right away anymore. But if it works its the best thing to do ! : ) I'll update accordingly on the right moment
    – arod
    Apr 9, 2012 at 21:46
  • 1
    Yes he was wrong, check the manual: postgresql.org/docs/current/interactive/… This setting can be changed by the superuser at any time for any database. May 7, 2012 at 14:30
  • 1
    @Stefano Yup, I'm wrong (or at least in the case of using a view/log trigger extremely sub-optimal) - I haven't tried this myself though so I'm not sure if it puts the DB name into the log line or not - that's only a concern if you're enabling it for multiple DBs though and still involves less post-processing than logging every DB in a busy cluster.
    – voretaq7
    May 7, 2012 at 15:11
  • 1
    @Stefano I don't mind if that answer accumulates downvotes - it's a solution, but certainly not the best one. I'd much rather see Frank's solution upvoted more though - it should really be outscoring my answer by a lot more than one vote!
    – voretaq7
    May 7, 2012 at 21:28

If you're logging statements via Postgres there's no way to do this per-database that I'm aware of (short of writing a view that calls a logging trigger for every table -- obviously not realistic).

The best available solution is what you've described (prefix each line with the database name) and feed the data to something like syslog-ng to split the query log up per database.

Post-processing the log file is also an option, but be aware of potential problems (OS/filesystem max file size limits, disk space exhaustion) for database clusters that have large numbers of queries.
Also note that you do pay a performance penalty for logging all queries - how big a penalty depends on how big the queries are...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.