Feb
4
Some PostgreSQL reminders
From Chander Ganesan via email:
/etc/init.d/postgresql reload
This rereads postgresql.conf, and new backend processes get the new settings. Existing backends are unchanged.
alter database db_name set log_min_duration_statement 250;
Immediately causes all backends to log statements taking longer than 250 seconds to run (assuming logging is active already).
alter database db_name reset log_min_duration_statement;
Immediately resets to use the default value from postgresql.conf.
We are planning to use CSV logging for our main PostgreSQL database, rotating hourly and loading the CSVs into another database for analysis. But we would only need to see ALL statements if we’re troubleshooting. In normal operations, we’d want to see only long-running queries (candidates for tuning).
Being able to “poke” a new value for the minimum duration logged makes this very easy to change on the fly.
