14

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

arod
  • 602

2 Answers2

30

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

ALTER DATABASE your_database_name
SET log_statement = 'all';
A T
  • 397
Frank Heikens
  • 1,318
  • 7
  • 8
  • I'm embarrassed to admit I've never seen this before -- has it been around a while? – voretaq7 Apr 09 '12 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 09 '12 at 21:46
  • so is @voretaq7 answer plainly wrong? please let us know! – Stefano May 07 '12 at 14:03
  • 1
    Yes he was wrong, check the manual: http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN This setting can be changed by the superuser at any time for any database. – Frank Heikens May 07 '12 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 07 '12 at 15:11
  • thanks @voretaq7 and frank for the clarification! It might be useful to update that answer then to avoid readers' errors and possibly downvotes (though that wouldn't touch your reputation :) ) – Stefano May 07 '12 at 15:59
  • 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 07 '12 at 21:28
  • Is there any necessary options on postgresql.conf for this to work? – vmassuchetto Aug 21 '15 at 14:10
  • 1
    make sure to set log_statement = 'none'; in postgresql.conf otherwise you will still see all databases in your logs. – simUser Jun 16 '20 at 08: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...

voretaq7
  • 80,391