PostgreSQL module

The postgresql module collects and parses logs created by PostgreSQL.

When you run the module, it performs a few tasks under the hood:

This module comes in two flavours: a parser of log files based on Linux distribution defaults, and a CSV log parser, that you need to enable in database configuration.

The postgresql module using .log was tested with logs from versions 9.5 on Ubuntu, 9.6 on Debian, and finally 10.11, 11.4 and 12.2 on Arch Linux 9.3.

The postgresql module using .csv was tested using versions 11 and 13 (distro is not relevant here).

This module can collect any logs from PostgreSQL servers, but to be able to better analyze their contents and extract more information, they should be formatted in a determined way.

There are some settings to take into account for the log format.

Log lines should be preffixed with the timestamp in milliseconds, the process id, the user id and the database name. This uses to be the default in most distributions, and is translated to this setting in the configuration file:

log_line_prefix = '%m [%p] %q%u@%d '

PostgreSQL server can be configured to log statements and their durations and this module is able to collect this information. To be able to correlate each duration with their statements, they must be logged in the same line. This happens when the following options are used:

log_duration = 'on'
log_statement = 'none'
log_min_duration_statement = 0

Setting a zero value in log_min_duration_statement will log all statements executed by a client. You probably want to configure it to a higher value, so it logs only slower statements. This value is configured in milliseconds.

When using log_statement and log_duration together, statements and durations are logged in different lines, and Filebeat is not able to correlate both values, for this reason it is recommended to disable log_statement.

Other logging options that you may consider to enable are the following ones:

log_checkpoints = 'on';
log_connections = 'on';
log_disconnections = 'on';
log_lock_waits = 'on';

Both log_connections and log_disconnections can cause a lot of events if you don’t have persistent connections, so enable with care.

Since the PostgreSQL CSV log file is a well-defined format, there is almost no configuration to be done in Filebeat, just the filepath.

On the other hand, it’s necessary to configure postgresql to emit .csv logs. The recommended parameters are:

logging_collector = 'on';
log_destination = 'csvlog';

You can further refine the behavior of the postgresql module by specifying variable settings in the modules.d/postgresql.yml file, or overriding settings at the command line.

The following example shows how to set paths in the modules.d/postgresql.yml file to override the default paths for PostgreSQL logs:

- module: postgresql
  log:
    enabled: true
    var.paths: ["/path/to/log/postgres/*.log*"]

To specify the same settings at the command line, you use:

-M "postgresql.log.var.paths=[/path/to/log/postgres/*.log*]"

Each fileset has separate variable settings for configuring the behavior of the module. If you don’t specify variable settings, the postgresql module uses the defaults.

For advanced use cases, you can also override input settings. See Override input settings.

This module comes with two sample dashboards.

The first dashboard is for regular logs.

The second one shows the slowlogs of PostgreSQL. If log_min_duration_statement is not used, this dashboard will show incomplete or no data.

For a description of each field in the module, see the exported fields section.