PostgreSQL- LOGS LOCATION

PostgreSQL- LOGS LOCATION

PostgreSQL users can select any of several different ways to handle database logs,

or even choose a combination.

 But especially for new users, or those getting used to an unfamiliar system,

just finding the logs can be difficult.

Log Entries

-------------

postgres=# show log_destination ;

 log_destination

-----------------

 stderr

(1 row)

The log_destination setting tells PostgreSQL where log entries should go.

In most cases it will be one of four values, though it can also be a comma-separated list of any of those four values.

SYSLOG

Syslog is a complex beast, and if your logs are going here, you’ll want more than this blog post to help you.

Different systems have different syslog daemons, those daemons have different capabilities and require different configurations,

and we simply can’t cover them all here. Your syslog may be configured to send PostgreSQL logs anywhere on the system,

or even to an external server. For your purposes, though, you’ll need to know what “ident” and “facility” you’re using.

These values tag each syslog message coming from PostgreSQL, and allow the syslog daemon to sort out where the message should go.

 You can find them like this:

postgres=# show log_destination ;

 log_destination

-----------------

 stderr

(1 row)

postgres=# show syslog_facility ;

 syslog_facility

-----------------

 local0

(1 row)

postgres=# show syslog_ident ;

 syslog_ident

--------------

 postgres

(1 row)

STDERR

This is probably the most common log destination (it’s the default, after all) and can get fairly complicated in itself.

Selecting “stderr” instructs PostgreSQL to send log data to the “stderr” (short for “standard error”)

 output pipe most operating systems give every new process by default.

 The difficulty is that PostgreSQL or the applications that launch it can then redirect this pipe to all kinds of different places.

If you start PostgreSQL manually with no particular redirection in place, log entries will be written to your terminal:

[~]$ pg_ctl -D $PGDATA start

server starting

[~]$ LOG:  database system was shut down at 2014-11-05 12:48:40 MST

LOG:  database system is ready to accept connections

LOG:  autovacuum launcher started

LOG:  statement: select syntax error;

ERROR:  column "syntax" does not exist at character 8

STATEMENT:  select syntax error;

In these logs you’ll see the logs from me starting the database, connecting to it from some other terminal, and issuing the obviously erroneous command “select syntax error”. But there are several ways to redirect this elsewhere. The easiest is with pg_ctl’s -l option, which essentially redirects stderr to a file, in which case the startup looks like this:

[ ~]$ pg_ctl -l logfile -D $PGDATA start

server starting

In this system, logging_collector is turned on, which means we have to find out where it’s collecting logs.

First, check log_directory. In my case, below, it’s an absolute path, but by default it’s the relative path “pg_log”.

This is relative to the PostgreSQL data directory. Log files are named according to a pattern in log_filename.

Each of these settings is shown below:

postgres=# show logging_collector ;

 logging_collector

-------------------

 on

(1 row)

postgres=# show log_directory ;

 log_directory

---------------

 pg_log

(1 row)

postgres=# show data_directory ;

   data_directory

---------------------

 /var/lib/pgsql/data

(1 row)

postgres=# show log_filename ;

   log_filename

-------------------

 postgresql-%a.log

(1 row)