Log slow queries or all statements in PostgreSQL

Set log_min_duration_statement to capture completed statements that meet a duration threshold. Use log_statement when you need to record selected kinds of statements as they are executed. The two settings serve different purposes.

Record statements that take at least one second

In postgresql.conf:

log_min_duration_statement = 1000

The unit is milliseconds when none is specified. 0 records the duration of every completed statement; -1 disables this duration-based logging. A threshold of 1000 includes statements taking at least 1000 ms.

Reload configuration with an appropriately privileged connection:

SELECT pg_reload_conf();
SHOW log_min_duration_statement;

Reloading sends a request to reread configuration; check the server log for rejected settings. Changes to logging_collector, unlike this threshold, require a server restart.

Record SQL by statement category

log_statement = 'all'

log_min_duration_statement=0 is not identical to log_statement='all': duration logging concerns completed work, while statement logging happens earlier. With the extended query protocol, Parse, Bind, and Execute durations are logged separately. Basic syntax errors are not covered simply by turning on log_statement; error logging has its own controls.

Include useful context

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

These settings add diagnostic context but can increase volume. log_temp_files=0 logs all temporary files when they are removed; use a positive size threshold if that is too noisy. log_lock_waits reports waits that exceed the relevant deadlock-check delay, rather than every lock acquisition.

If you enable both statement and duration logging, retain the process or session identifier so you can correlate separate entries. In newer PostgreSQL releases, connection logging offers more detailed choices; on retains its compatibility meaning.

Analyze and collect the output

pgbadger /var/log/postgresql/postgresql.log -o report.html

Replace the path with the actual file and configure pgBadger for your format and prefix. It can summarize query patterns and timing from the records that were actually logged.

To compare instances, collect the files with DataKit. Validate the timestamp, database name, and duration against a known slow query, and handle sensitive SQL values before ingestion.

Scope logging and find the files

For new connections to one database:

ALTER DATABASE mydb SET log_min_duration_statement = 1000;

Reconnect to test this database default; existing sessions do not inherit it retroactively. Restore it with ALTER DATABASE mydb RESET log_min_duration_statement when appropriate.

The destination depends on log_destination, logging_collector, and service-level redirection. With the collector enabled, log_directory may be absolute or relative to data_directory; otherwise consult the configured service or syslog destination.

See PostgreSQL logging settings for version-specific behavior.