Mastering PostgreSQL Log Management: Configuration, Analysis, and Alerting

At 2:47 AM, your on-call engineer gets paged: API response times have spiked from 80ms to 12 seconds. By the time the team digs in, the slow query has completed and pg_stat_activity shows nothing suspicious. Without proper logging configured, you are left guessing — was it a missing index, a lock wait, a runaway autovacuum, or a sudden spike in connections? This scenario plays out on teams every week, and the fix is almost always the same: PostgreSQL was never configured to log what matters.

PostgreSQL ships with logging largely disabled by default. Out of the box, you will catch crashes and startup messages, but slow queries, lock waits, temporary file spills, and connection storms will pass silently. Getting logging right means balancing signal against noise — capturing enough detail to diagnose incidents without flooding disk or burying critical events in chatter.

This guide covers the exact parameters that production PostgreSQL deployments need, how to structure log output for parsing, how to run pgBadger for analysis, and how to set up alerting so the next 2:47 AM incident comes with a clear paper trail.

PostgreSQL Logging Fundamentals

PostgreSQL supports three log destinations, controlled by the log_destination parameter: stderr, csvlog, and syslog. Most production deployments use stderr collected by systemd or a process supervisor, or csvlog when structured parsing is a priority.

Log Destinations

# postgresql.conf

# Send logs to stderr (collected by systemd/journald or redirected to file)
log_destination = 'stderr'

# Or use csvlog for structured output — enables pg_read_file() parsing
# log_destination = 'csvlog'

# Enable the built-in log collector when writing to files directly
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0640

When logging_collector = on, PostgreSQL spawns a dedicated background process that captures stderr output and writes it to files under log_directory. This avoids the risk of log writes blocking query execution. If you are running PostgreSQL under systemd without logging_collector, logs go to journald — searchable with journalctl -u postgresql but harder to feed into pgBadger.

Choosing a Log Directory

Use a dedicated partition or mount point for log_directory when possible. Log volume spikes during incidents — exactly when disk space matters most. On RDS and Aurora, log destination configuration is abstracted; you pull logs via the AWS Console, CLI, or CloudWatch Logs, but the underlying parameters still apply.

Key Logging Parameters

The following parameters have the highest impact on production observability. Each has a meaningful performance and disk cost — tune them based on your query volume and storage budget.

log_min_duration_statement

This is the single most important logging parameter for performance investigations. Any query taking longer than the specified threshold (in milliseconds) is logged with its full text and duration.

# Log queries taking longer than 500ms
log_min_duration_statement = 500

# Log ALL queries (use only in development or low-traffic environments)
# log_min_duration_statement = 0

# Disable slow query logging (default)
# log_min_duration_statement = -1

Start at 500ms for a production OLTP system. After running pgBadger for a week, you will see the distribution of query durations and can decide whether to tighten the threshold to 200ms or 100ms. For analytics databases with expected long-running queries, 5000ms or higher prevents log flooding.

log_connections and log_disconnections

# Log each new connection attempt and successful authentication
log_connections = on

# Log session duration on disconnect
log_disconnections = on

These parameters are essential for connection pool debugging. If your application is leaking connections, misconfiguring PgBouncer, or hitting max_connections limits, connection logs provide a clear timeline. The output shows the client IP, database, user, and authentication method — enough to identify rogue clients or connection storms from a misconfigured deployment.

On very high-connection-rate systems (thousands of short-lived connections per second), log_connections generates substantial volume. In those cases, rely on PgBouncer logs at the proxy layer and disable log_connections at the PostgreSQL level.

log_lock_waits

# Log when a session waits longer than deadlock_timeout for a lock
log_lock_waits = on

# deadlock_timeout controls when lock waits are logged AND when deadlock detection runs
deadlock_timeout = 1s

Lock waits are a leading cause of production slowdowns that are invisible without this parameter. When a session waits longer than deadlock_timeout to acquire a lock, PostgreSQL logs the waiting query, the blocking query, and the lock type. This is invaluable for diagnosing long-running transactions holding locks, DDL running during peak traffic, or row-level lock contention in high-concurrency tables.

log_temp_files

# Log any temporary file creation larger than 0 bytes (log all temp files)
log_temp_files = 0

# Or set a threshold in kilobytes — log temp files larger than 10MB
# log_temp_files = 10240

When PostgreSQL cannot fit a sort or hash operation in work_mem, it spills to a temporary file on disk. These spills dramatically slow down query execution and indicate that work_mem is undersized for certain query patterns. Enabling log_temp_files = 0 logs every temp file creation, giving you the query and file size — a direct signal that a query needs more memory or a better execution plan.

log_autovacuum_min_duration

# Log autovacuum runs taking longer than 250ms
log_autovacuum_min_duration = 250

# Log all autovacuum runs
# log_autovacuum_min_duration = 0

# Disable autovacuum logging (default is 250ms in newer versions)
# log_autovacuum_min_duration = -1

Autovacuum is critical for table health and query plan accuracy, but runaway autovacuum jobs compete for I/O with your application. Logging autovacuum activity shows which tables are vacuumed most frequently, how long each run takes, and how many dead tuples were removed. This data directly informs decisions about autovacuum cost limits and per-table storage parameters.

Additional Parameters Worth Enabling

# Log DDL statements (CREATE, ALTER, DROP) — important for audit trails
log_ddl = on  # PostgreSQL 17+
# For older versions, use:
log_min_messages = warning
log_statement = 'ddl'

# Log checkpoints — useful for I/O pattern analysis
log_checkpoints = on

# Duration of each completed statement (alternative to log_min_duration_statement)
# log_duration = on  # Logs ALL durations — very verbose, avoid in production

Log Format and Parsing

log_line_prefix Best Practices

The log_line_prefix parameter controls the metadata prepended to each log line. A well-structured prefix makes automated parsing reliable and gives engineers the context they need at a glance.

# Recommended production log_line_prefix
log_line_prefix = '%m [%p] %q%u@%d %a '

# Field reference:
# %m  — timestamp with milliseconds
# %p  — process ID (PID)
# %q  — nothing (quiet) if not in a session; prevents blank lines
# %u  — database user name
# %d  — database name
# %a  — application name (set by client via application_name parameter)

Example output with this prefix:

2025-11-14 02:47:33.812 UTC [18423] app_user@orders pgbouncer LOG:  duration: 12847.203 ms  statement: SELECT o.*, c.email FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending' ORDER BY o.created_at;

The application name (%a) is particularly valuable when you use PgBouncer or connection pools — set application_name in your connection string to identify which service generated each query.

CSV Logging for Structured Parsing

# Enable both stderr and csvlog simultaneously
log_destination = 'stderr,csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'

CSV logs write a fixed schema with 23 columns — timestamp, user, database, pid, query, error_severity, and more — making them easier to import into log aggregators or PostgreSQL itself via the pg_read_file() function. pgBadger supports both formats; use the --format csv flag when parsing CSV logs.

Analyzing Logs with pgBadger

pgBadger is a Perl-based log analyzer that processes PostgreSQL log files and generates detailed HTML reports covering slow queries, connection counts, lock waits, autovacuum activity, and error frequency.

Installation

# macOS
brew install pgbadger

# Debian/Ubuntu
apt-get install pgbadger

# From source (latest version)
wget https://github.com/darold/pgbadger/archive/refs/tags/v12.2.tar.gz
tar xzf v12.2.tar.gz
cd pgbadger-12.2/
perl Makefile.PL
make && make install

Running pgBadger

# Basic analysis of a single log file
pgbadger /var/log/postgresql/postgresql-2025-11-14_000000.log \
  --outfile /var/www/reports/pgbadger-2025-11-14.html

# Parse multiple files matching a glob pattern
pgbadger /var/log/postgresql/postgresql-*.log \
  --outfile /var/www/reports/pgbadger-weekly.html \
  --begin "2025-11-08 00:00:00" \
  --end "2025-11-14 23:59:59"

# Incremental mode — process new log entries since last run (for cron jobs)
pgbadger /var/log/postgresql/postgresql-%Y-%m-%d*.log \
  --outfile /var/www/reports/pgbadger-latest.html \
  --last-parsed /var/log/pgbadger.last \
  --incremental

# For CSV format logs
pgbadger /var/log/postgresql/postgresql-*.csv \
  --format csv \
  --outfile /var/www/reports/pgbadger.html

Interpreting pgBadger Output

The most actionable sections in a pgBadger report are:

• Slowest queries — sorted by total time or average duration. Queries with high total time are your optimization targets even if individual executions are not extreme.
• Most frequent queries — high-frequency queries with moderate duration often contribute more cumulative load than rare slow queries.
• Lock waits — tables and queries involved in lock contention.
• Temp files — queries generating disk spills, with sizes, indicating work_mem pressure.
• Connection graphs — peak connection counts by hour, useful for right-sizing connection pools.

Log Rotation with logrotate

PostgreSQL's built-in log rotation (log_rotation_age, log_rotation_size) handles file creation but does not compress or remove old files. Use logrotate for production log lifecycle management.

# /etc/logrotate.d/postgresql
/var/log/postgresql/*.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    sharedscripts
    postrotate
        # Signal PostgreSQL to reopen log files after rotation
        /usr/bin/pg_ctlcluster 16 main reload > /dev/null 2>&1 || true
    endscript
}

The postrotate script sends a reload signal so PostgreSQL opens the new log file rather than continuing to write to the rotated (renamed) file. On systemd-managed instances, you can also use systemctl reload postgresql.

# PostgreSQL built-in rotation settings (complement logrotate, do not replace it)
log_rotation_age = 1d       # Rotate after 1 day
log_rotation_size = 100MB   # Rotate when file exceeds 100MB
log_truncate_on_rotation = on  # Overwrite old files with same name pattern

Alerting on Log Events

Raw log files become actionable when paired with pattern-based alerting. The following patterns should trigger alerts in any production system.

Critical Patterns to Monitor

# Deadlocks — always investigate immediately
FATAL.*deadlock detected

# Out of connections — application will fail to connect
FATAL.*remaining connection slots are reserved

# Replication lag or slot issues
ERROR.*replication slot.*inactive
WARNING.*replication slot.*retained WAL

# Autovacuum unable to run (wraparound risk)
WARNING.*oldest xmin is far in the past
ERROR.*database is not accepting commands to avoid wraparound

# Temporary file spills above threshold
LOG.*temporary file: path .* size [0-9]{7,}

# Checkpoint warnings — I/O falling behind
LOG.*checkpoints are occurring too frequently

Integrating with Log Aggregators

For teams using centralized logging, ship PostgreSQL logs to your aggregator of choice and create structured alerts:

# Filebeat configuration for shipping PostgreSQL logs to Elasticsearch
# /etc/filebeat/inputs.d/postgresql.yml
- type: log
  enabled: true
  paths:
    - /var/log/postgresql/postgresql-*.log
  fields:
    service: postgresql
    environment: production
  multiline.pattern: '^\d{4}-\d{2}-\d{2}'
  multiline.negate: true
  multiline.match: after

In Grafana Loki, use LogQL to build slow query rate alerts:

# LogQL — alert when slow queries exceed 10 per minute
sum(rate({service="postgresql"} |= "duration:" | regexp `duration: (?P[0-9.]+) ms` | duration > 1000ms [1m])) > 10

Working with JusDB on PostgreSQL Logging

JusDB configures and monitors PostgreSQL logging for engineering teams who need actionable observability without the noise. Our DBAs set up log aggregation, pgBadger pipelines, and alerting so you catch slow queries and anomalies before they become incidents.

Explore JusDB PostgreSQL Management →  |  Talk to a DBA

Related reading:

• pg_stat_statements: Query Performance Analysis in PostgreSQL
• PostgreSQL Autovacuum Tuning: Prevent Table Bloat
• PostgreSQL Vacuum Tuning: A Comprehensive Guide