Log Files

I l @ ve RuBoard

By default, the postmaster process sends logging information to stdout . However, it is common to redirect output to a specified log file.

 >postmaster -D /usr/local/pgsql/data >pglog 2>&1 & 

This will redirect the stdout of the postmaster process to the file named pglog and will also redirect the stderr facility of postmaster to stdout (which is then itself redirected to the specified log file).

This type of arrangement is convenient but presents some long- term problems:

• Log files tend to grow very large if untended.

• The system log files and the database log files would reside in separate areas. This can make debugging system failures problematic .

• It is difficult to redirect these log files to an external "logging" server designed for this purpose.

Depending on the size of your database, the frequency of its use, and your networking architecture, this might be a fine solution. However, there are two methods for dealing with the problems previously presented:

• Implement a custom log-rotation solution.

• Configure PostgreSQL to use syslog .

Customized Log Rotation

A customized log-rotation solution is possible if some amount of downtime is permitted. This is due to the fact that the postmaster process must be stopped for the logs to be rotated .

This type of solution usually mandates the use of cron and shell scripting. The process usually occurs like this:

1. At a specified hour (usually late at night), postmaster is stopped.

2. The log-rotation script is run.

3. After a few minutes of delay, postmaster is restarted.

Typically, this can be completed in one or two minutes, depending of course on the

system hardware.

Configuring cron to perform these tasks is outside the scope of this book, but generally it is a straightforward process.

The script to handle the actual log rotation can be done either as a simple shell script or in a language like Perl or Python. A typical rotation scheme usually renames (using mv ) files, keeping only a specific amount of history. For instance:

 logfile (current)  logfile.1  logfile.1  logfile.2  logfile.2  logfile.3  logfile.3  logfile.4  logfile.4  logfile.5  logfile.5  /dev/null 

Typically, these events are run daily or weekly; however, for heavily used systems, a more frequent schedule could be advisable.

Configuring PostgreSQL to Use syslog

The preceding approach would be fine for most PostgreSQL server installations; however, a number of issues are still not addressed by this example, namely:

• Log files are not integrated with other system log files (making debugging more difficult).

• It is still relatively difficult to redirect log files to an external logging system.

The solution is to use the syslog facility present on most UNIX (Linux) systems.

Generally, configuring PostgreSQL to use syslog requires three steps:

1. Compile the source using the appropriate options to enable logging (that is, --enable-syslog ) or download the appropriate RPMs that have that functionality enabled.

2. Enable the syslog option in the pg_options (or equivalent) file (that is, syslog=1 ).

3. Edit the /etc/syslog.conf file to correctly capture PostgreSQL syslogging calls, like this:

 local0.*                       /var/log/postgresql 

Using syslog is suggested for larger installations or when remote monitoring of the database system is a priority.

I l @ ve RuBoard