DB‎ > ‎Postgres‎ > ‎

PostgreSQL log setup and analyze

Thank you for visiting this page, this page has been update in another link PostgreSQL log setup for later analyze
One of the most effective ways to know what your postgreSQL has done is to analyze the logs of what queries it executed. I show you the basic log setup, then followed by some analyze ways.
1.  In postgresql.conf, here are main parameters, before changing them, better to understand them first
# - 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_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
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.

As you can see that in this case, log_collector=on, so stderr output will be written into log_filename. Need to mention is that log_filename can include strftime() escapes, so you can also specify it as
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

To be noted, this way, you need to take care of log rotation by your self. Because PostgreSQL doesn't clean up log files. Default way will surely overwrite old log files after a week.

Another parameter is log_line_prefix, here is my example. Also, you an also make it for yourself.
log_line_prefix = '%t|%d|%u|%x|%e|%c|'                  # special values:
#log_line_prefix = ''                   # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d> '

To be noted is that there are three basic formats of line prefix that will match the standard of sorts introduced by the requirements of pgFouine, a popular log parsing utility. So, if you'd like to use the tool, the following three are available for you in below.
log_line_prefix = '%t [%p]: [%l-1] '
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d'
log_line_prefix = '%t [%p]: [%l-1]  user=%u,db=%d,remote=%r'
The first line logs basic information, The second upgrades that to also include the user and database information for the connection, the final line also adds the remote host the connection came from.

Note: if you use CSV format logs, the timestamp and process information will be inserted for you automatically, you don't need to include it into the prefix of the PostgreSQL log output.

Good, enough, but sometime you need to be careful with multi-line queries, which mostly happens in psql. So other types of logging can be alternate logging formats. Syslog or CSV logging. They are easier to be enabled.

Moveing a bit ahead, logging every query is a heavy burden on server, so most of time, you may only want to log complex,long running queries on your server, so here are the parameters.
log_min_duration_statement = 10000      # -1 is disabled, 0 logs all statements
log_duration = on
log_statement = 'all'                 # none, ddl, mod, all

The first line tells server to log every query that takes over 1000ms(one second), the second default is 'off' and the third is none by default.

Auto_explain, after PostgreSQL 8.4, there is a feature auto_explain, it's a contrib module, you need to install it , after installatin, add the following three lines into postgres.conf.
shared_preload_libraries ='auto_explain'
custom_variable_classes ='auto_explain'

This tells system that, any query takes longer than 1s will be logged with a full explain plan for the output.

At last, there is lock waits option, let you capature deadlock queiries.
log_lock_waits = on                     # log lock waits >= deadlock_timeout
By default, 1s is the deadlock timeout setting.
#deadlock_timeout = 1s