Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
should I post this elsewhere ? where ? –  arod Apr 4 '12 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 '12 at 17:24

2 Answers 2

up vote 4 down vote accepted

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

ALTER DATABASE your_database_name SET log_statement = all;
share|improve this answer
    
I'm embarrassed to admit I've never seen this before -- has it been around a while? –  voretaq7 Apr 9 '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 9 '12 at 21:46
    
so is @voretaq7 answer plainly wrong? please let us know! –  Stefano May 7 '12 at 14:03
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. –  Frank Heikens May 7 '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 7 '12 at 15:11

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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