PostgreSQL logging: configuration, slow queries, and auditing
Configure PostgreSQL stderr, CSV, or JSON logging, capture slow statements and lock waits, and verify audit coverage and DataKit collection.
A database dashboard shows a latency spike after a deploy. The PostgreSQL log can help separate slow execution from a session waiting on a lock, but only if the relevant events reached a destination you can read. In a container, that may be stderr collected by the runtime. On a self-managed host, it might be a JSON file managed by PostgreSQL.
The examples below use PostgreSQL 15–18. Check your major version and existing log route before changing the configuration.
Which setting gives you the evidence you need?
| Question | Start with | What to check |
|---|---|---|
| Where are the server logs? | log_destination and logging_collector |
Runtime or service logs for stderr, or current_logfiles for collector-managed files |
| Which statements took too long? | log_min_duration_statement |
A completed statement or extended-protocol phase met the configured duration |
| Were sessions waiting on locks? | log_lock_waits |
A wait passed deadlock_timeout; that alone does not establish a deadlock |
| What SQL should go into an audit trail? | log_statement or pgAudit |
Match statement classes, objects, and roles to the required audit scope |
1. Choose a log destination
# postgresql.conf
logging_collector = on
log_destination = 'jsonlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = off
Changing logging_collector requires a restart. With the collector already enabled, reload the configuration to apply a change to log_destination. Both csvlog and jsonlog require the collector. JSON output is available from PostgreSQL 15. The relative directory log is under the data directory, where current_logfiles also identifies the files the collector is currently writing.
The time and size settings trigger rotation. They do not delete old files, and 100 MB is a rotation threshold. Including the time in the file name lets a size-triggered rotation create a distinct name during the day. If a runtime or service manager already captures stderr and manages retention, you can keep that route. PostgreSQL logging settings · PostgreSQL 15 release notes
2. Give text logs a useful prefix
log_line_prefix = '%m [%p] %c %q%u@%d %a '
Here %m is a timestamp with milliseconds, %p is the process ID, and %c identifies the session. %s means process start time. %q stops the prefix for a non-session process, so the user, database, and application fields after it apply to session messages. %e adds SQLSTATE and %i adds the command tag if you need them.
A session identifier is especially useful when statement text and a later duration appear on separate lines. CSV and JSON already carry structured fields, so parse those directly.
3. Record slow statements and look for waits
log_min_duration_statement = 500ms
log_lock_waits = on
log_checkpoints = on
log_temp_files = 0
The 500 ms threshold is an example. PostgreSQL logs completed statements that take at least that long. -1 disables this threshold, and 0 logs the duration of every completed statement. The extended query protocol times Parse, Bind, and Execute separately. If volume is high, log_min_duration_sample and log_statement_sample_rate can sample faster statements. Statements that reach the full logging threshold still get logged.
Use lock-wait entries to decide whether execution time points to contention. log_lock_waits records waits longer than deadlock_timeout. Checkpoint logs show data such as buffers written and write time. log_temp_files=0 logs every temporary file's name and size when it is deleted, which can help locate sorts or hashes that spilled.
If you need a plan, load the auto_explain module in an authorized session or preload it in the required scope, then set auto_explain.log_min_duration. It has its own threshold. Enabling actual execution statistics and per-node timing can add overhead. auto_explain
4. Limit statement and connection logging to what you need
log_statement = 'ddl'
log_connections = on
log_disconnections = on
log_hostname = off
log_statement accepts none, ddl, mod, and all. The mod setting includes qualifying data changes in addition to DDL. Even all misses basic syntax errors and extended-protocol failures that occur before Execute. See log_min_error_statement for logging statements that fail.
Connection and disconnection logs help spot connection churn. In PostgreSQL 15–17, log_connections is a Boolean. PostgreSQL 18 accepts a list and still accepts on for compatibility, which covers receipt, authentication, and authorization without setup_durations. Disconnection entries include session duration. SQL text and bind parameters can carry sensitive data. Scope access, retention, and the capture window accordingly. PostgreSQL 17 logging settings · PostgreSQL 18 logging settings
5. Keep the details you need for errors
log_error_verbosity = default
log_min_error_statement = error
log_error_verbosity controls detail. verbose includes SQLSTATE and the source location. log_min_error_statement controls when the failing SQL is included, while log_min_messages sets the server-message threshold. For an error-rate rule, filter by a verified severity or SQLSTATE. A query that raises ERROR and a session terminated by FATAL are different signals.
Verify collection with one slow-query record
In a test environment, use a harmless query you can identify that meets the threshold, then inspect the emitted record. Configure DataKit file collection for the actual paths and give the collector read access. The DataKit PostgreSQL integration also supports file input. Its built-in parser example is for a particular text format. Set up a parser that matches your JSON, CSV, or text output.
Compare the database, user, time, and severity with the original record. In JSON logs the duration can appear inside message, and statement can be absent. Verify extraction and redaction before forwarding sensitive SQL. Configure Guance log detection using the fields and severity values observed in database events.
Frequently asked questions
Should I use csvlog or jsonlog?
JSON gives PostgreSQL 15 and later a record per line. Handle optional fields and verify the timestamp and its time zone. CSV can fit an existing pipeline if it understands the column order for your server version, quotes, and multiline fields. Text is also suitable when stderr is already collected and your parser matches the actual prefix.
How long can I leave log_statement=all on?
Keep a diagnostic capture short and watch disk and I/O. Log volume depends on traffic, statement length, and parameters. If you need scoped audit classes or relation-level auditing, evaluate pgAudit for your server major version. It requires preloading. Create the extension before setting pgaudit.log to include DDL object details. Confirm the configured classes, roles, and relations cover the requirement.
What does FATAL: remaining connection slots mean?
Ordinary roles have run out of usable slots. Reserved slots may still be available to eligible roles. Check connections and pool usage by database, role, and application, including open transactions. Avoid terminating sessions based only on an idle label. Changing max_connections requires a restart and affects server resource allocation. Connection settings
How do I collect logs from a PostgreSQL container?
If PostgreSQL sends logs to stderr, enable DataKit collection for the runtime or Kubernetes environment and make the source accessible. If the collector writes files inside the container, mount the log directory where the file input can read it. Choose one path for each stream to avoid duplicates.
Related reading
Contact us
Join the community
to join the community
Try Guance
Start online and pay only for what you use.
Get startedChoose a Guance plan
