13

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 ?

thanks

2
  • 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

29

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

ALTER DATABASE your_database_name
SET log_statement = 'all';
9
  • I'm embarrassed to admit I've never seen this before -- has it been around a while?
    – voretaq7
    Apr 9, 2012 at 17:23
  • 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
  • 1
    make sure to set log_statement = 'none'; in postgresql.conf otherwise you will still see all databases in your logs.
    – simUser
    Jun 16, 2020 at 8:46
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .