Followup to some questions about the Postgres activity log

by gorthx

Answers to three questions that have come up from my various logging & monitoring talks over the years:

1. I recommend putting your activity logs on their own partition, just like you would with the transaction logs and your data directory. Rob W asked “What happens when the logger runs out of space?” The answer: nothing drastic. Postgres won’t write any more log entries, but the database will stay up. Make sure you have appropriate monitoring in place to alert you if that partition fills up.

2. “Can you set multiple log_destinations?” Well, I’ve logged to csv + stderr and csv + eventlog, just to see if it could be done. I don’t know what would happen if you tried csv + syslog.

3. “Can you set the logging GUCs on a per-database level?” Yes, some of them can be set to different values per-database or per-user, even. This can be quite useful to answer, for example, the “I don’t think your application is sending the SQL statements you think it’s sending” type of questions:

— for a specific database:
ALTER DATABASE tedsdb SET log_statement = 'all';
— for a specific user:
ALTER ROLE bill SET log_statement = 'all';

To undo this change:
ALTER ROLE bill SET log_statement = default;
ALTER ROLE bill RESET log_statement;

To check the settings for the database, join pg_db_role_setting, pg_database, and pg_authid:
postgres=# SELECT d.datname, COALESCE(r.rolname, 'ALL') AS rolname, s.setconfig
FROM pg_database d
JOIN pg_db_role_setting s
ON s.setdatabase = d.oid
LEFT JOIN pg_authid r
ON s.setrole = r.oid;
datname | rolname | setconfig
tedsdb | ALL | {log_statement=all}

To check the settings for a particular user, look in pg_roles:
postgres=# SELECT rolname, rolconfig
FROM pg_roles
ORDER BY rolname;
rolname | rolconfig
bill | {log_statement=all}
postgres |


One Comment to “Followup to some questions about the Postgres activity log”

  1. You certainly can log to multiple destinations, though I’ve never done an exhaustive test of all the combinations. One example use case I’ve done in the past is to have standard logging going to syslog, and then flipping all kinds of debug parameters and also turning on a second log location, so that you get a clean logfile capture of your debug info.

%d bloggers like this: