Activity Log

The setting (in postgresql.conf) below will capture the stderr and write it to $PGDATA/pg_log/<postgresql-%a.log> (%a is one of Mon, Tue,Wed,Thu,Fri, Sat ,Sun).

The log will rotate every day, and every seventh day, the old log file is reused after truncation.

#-----------------------------------------------------------------

# ERROR REPORTING AND LOGGING

#-----------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'

# Valid values are combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform. csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:

logging_collector = on

# Enable capturing of stderr and csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)

# These are only used if logging_collector is on:

log_directory = 'pg_log'

# directory where log files are written,

# can be absolute or relative to PGDATA

log_filename = 'postgresql-%a.log'

# log file name pattern,

# can include strftime() escapes

log_truncate_on_rotation = on

# If on, an existing log file with the

# same name as the new log file will be

# truncated rather than appended to.

# But such truncation only occurs on

# time-driven rotation, not on restarts

# or size-driven rotation. Default is

# off, meaning append to existing files

# in all cases.

log_rotation_age = 1d

# Automatic rotation of logfiles will

# happen after that time. 0 disables.

log_rotation_size = 0

# Automatic rotation of logfiles will

# happen after that much log output.

# 0 disables.

log_min_duration_statement = 20

# use this parameter to check performance, sql with >=

# duration in ms will be logged

$ cd /u01/app/pgdata/pg_log

-bash-4.1$ ls -la

-rw------- 1 enterprisedb enterprisdb 108013907 Oct 31 13:39 postgresql-Fri.log

-rw------- 1 enterprisedb enterprisdb 0 Nov 3 00:00 postgresql-Mon.log

-rw------- 1 enterprisedb enterprisdb 0 Nov 1 00:00 postgresql-Sat.log

-rw------- 1 enterprisedb enterprisdb 0 Nov 2 00:00 postgresql-Sun.log

-rw------- 1 enterprisedb enterprisdb 0 Oct 30 00:00 postgresql-Thu.log

-rw------- 1 enterprisedb enterprisdb 2037917 Oct 28 10:24 postgresql-Tue.log

-rw------- 1 enterprisedb enterprisdb 34976 Oct 29 13:09 postgresql-Wed.log