Enable logging in PostgreSQL 8.4

To get rotated file logging working on PostgreSQL 8.4 edit "postgresql.conf" on your system. On Gentoo the file is located "/var/lib/postgresql/x.x/" where x.x is your PostgreSQL major and minor version (i.e 8.3, 8.4). Mine is currently 8.4.

Before we start though, you might want to make sure that the log files are written to a directory that can be read by other users, but still writable by the user that run PostgreSQL server. That would make tailing them much easier.

So on the Gentoo system I suggest you use /var/log and create a nested folder to reflect the PostgreSQL version. Also to make sure that is owned by both "postgres" user and "postgres" group.

cd  /var/log/
sudo mkdir -p postgresql/8.4
sudo chown -R postgres:postgres postgresql/8.4

Now modify the /var/lib/postgresql/8.4/postgresql.conf

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/lib/postgresql/8.4/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_statement = 'mod'

Verbosity depends on the "log_statement" setting as mentioned here:
http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html

Here, logs are:

• written to "/var/log/postgresql/8.4" directory
• log files are time stamped
• log rotation is daily which is achieved by setting "log_rotation_age" to 1 day and log_rotation_size is disabled by setting it to 0

IMPORTANT NOTE:

The confusing part is that "logging_collector" used to be called redirect_stderr in versions before 8.3. (http://www.postgresql.org/docs/8.3/static/release-8-3.html)