Home / server / Questions / 376699
Accepted
arod
Asked: 2012-04-05 07:17:58 +0800 CST

postgresql log queries single database

  • 772

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

postgresql

2 Answers

  1. Best Answer

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

    ALTER DATABASE your_database_name
SET log_statement = 'all';
    • 21

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

    • 1