PostgreSQL Troubleshooting with Practical Examples

PostgreSQL troubleshooting moves from an observed symptom to a verified resolution through systematic investigation. This guide covers version identification, log analysis, configuration validation, performance diagnostics, and recovery procedures for developers, DevOps engineers, and technical teams operating PostgreSQL in production. Each section provides concrete commands, expected outputs, failure signals, and rollback paths so you can act with confidence rather than guesswork.

Version and Environment Inventory

Before investigating any issue, establish a precise baseline of what you are running and where.

Identify PostgreSQL Version and Build Details

Run the version command from the psql client or the server binary:

psql --version
# psql (PostgreSQL) 16.2

# Or from the server binary
postgres --version
# postgres (PostgreSQL) 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit

Inside a connected session, query the server version and compile-time options:

SELECT version();
-- PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit

SHOW server_version_num;
-- 160002

Expected result: Major.minor.patch version string matching your deployment manifest. Failure signal: Version mismatch between client and server, or an unsupported major version (e.g., PostgreSQL 12 reached EOL in November 2024).

Capture Deployment Topology

Determine whether you are running a single instance, streaming replication, logical replication, Patroni, pg_auto_failover, or a managed service (RDS, Cloud SQL, Azure Database). Run:

SELECT * FROM pg_stat_replication;
-- Empty result = no streaming replicas connected

SELECT * FROM pg_stat_subscription;
-- Shows logical replication subscriptions if any

Check for connection pooling (PgBouncer, pgpool-II) and note the pool mode (session, transaction, statement).

Record Critical Settings and Prerequisites

Snapshot the active configuration with sources:

SELECT name, setting, unit, source, context
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

Export to a timestamped file for later comparison:

psql -c "SELECT name, setting, unit, source FROM pg_settings WHERE source != 'default' ORDER BY name;" > pg_settings_$(date +%F_%H%M).txt

Prerequisites: Superuser or pg_read_all_settings role. Blast radius: Read-only. Verification: File exists and contains non-default entries.

Safe Configuration Path

Configuration changes should be observable, minimal, and reversible. Never edit postgresql.conf directly without a version-controlled backup.

Observe Current Configuration State

Use pg_settings to see the effective value, pending restart requirements, and source:

SELECT name, setting, pending_restart, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size', 'max_connections', 'wal_level', 'max_wal_senders');

Apply a Single Scoped Change

Example: Increase work_mem from 4MB to 16MB for a reporting workload.

Backup current config:

   cp /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.$(date +%F_%H%M).bak

Edit using ALTER SYSTEM (preferred for persistence across restarts):

   ALTER SYSTEM SET work_mem = '16MB';
   SELECT pg_reload_conf();

Verify the change took effect:

   SHOW work_mem;
   -- work_mem: 16MB

Expected result: SHOW returns the new value; pg_settings.pending_restart is false for this parameter. Failure signal: pg_reload_conf() returns false, or the parameter still shows the old value (context = postmaster requires restart). Rollback: ALTER SYSTEM RESET work_mem; SELECT pg_reload_conf(); then verify.

Parameters Requiring Restart

For shared_buffers, max_connections, wal_level, or max_wal_senders, a restart is mandatory. Plan a maintenance window, notify stakeholders, and verify the cluster comes up cleanly:

systemctl restart postgresql@16-main
# or
pg_ctlcluster 16 main restart

Check the log for successful startup:

tail -n 50 /var/log/postgresql/postgresql-16-main.log
-- LOG:  database system is ready to accept connections

Verification and Diagnostics

When symptoms appear (slow queries, connection exhaustion, replication lag, vacuum issues), use these diagnostic pathways.

Connection and Session Analysis

Active sessions with duration and state:

SELECT pid, usename, application_name, client_addr, state,
       now() - backend_start AS session_duration,
       now() - state_change AS state_duration,
       wait_event_type, wait_event,
       LEFT(query, 120) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY backend_start;

Connection count vs. limit:

SELECT count(*) AS active_connections,
       setting::int AS max_connections,
       round(100.0 * count(*) / setting::int, 1) AS pct_used
FROM pg_stat_activity, pg_settings
WHERE name = 'max_connections';

Failure signal: >80% connection utilization or sessions stuck in active with wait_event_type = 'Lock' for >30 seconds.

Blocking and Lock Analysis

Identify blockers and blocked sessions:

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query,
       blocked_activity.state AS blocked_state,
       blocking_activity.state AS blocking_state
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Expected result: Zero rows (no blocking). Failure signal: Rows returned; examine blocking_query — often an uncommitted transaction or long-running DDL.

Replication Health (Streaming)

Primary side:

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_human
FROM pg_stat_replication;

Replica side:

SELECT status, receiver_state, sender_state,
       pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       pg_last_wal_replay_timestamp(),
       now() - pg_last_wal_replay_timestamp() AS replay_delay
FROM pg_stat_wal_receiver;

Failure signal: replay_lag_bytes > 1 GB or replay_delay > 5 minutes on a synchronous replica.

Vacuum and Bloat Monitoring

Check autovacuum activity and table bloat estimate:

SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC
LIMIT 20;

Failure signal: dead_pct > 20% on large tables, or last_autovacuum older than 24 hours on frequently updated tables.

Trigger manual vacuum if needed:

VACUUM (ANALYZE, VERBOSE) public.large_table;

Index Usage and Missing Indexes

Unused indexes (candidates for removal):

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 1024 * 1024  -- > 1 MB
ORDER BY pg_relation_size(indexrelid) DESC;

Sequential scans on large tables (possible missing indexes):

SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND n_live_tup > 100000
ORDER BY seq_tup_read DESC
LIMIT 20;

Log Analysis for Errors

Recent errors and fatals (last 100 lines):

grep -E "(ERROR|FATAL|PANIC)" /var/log/postgresql/postgresql-16-main.log | tail -100

Common patterns:

could not connect to server: Connection refused → postmaster down or listen_addresses/port mismatch
remaining connection slots are reserved for non-replication superuser connections → max_connections exhausted
could not write to WAL file: No space left on device → disk full on pg_wal
deadlock detected → application transaction ordering issue

Verify log destination and format:

SHOW log_destination;      -- stderr, csvlog, syslog
SHOW logging_collector;    -- on/off
SHOW log_directory;        -- log/
SHOW log_filename;         -- postgresql-%Y-%m-%d_%H%M%S.log

Failure Modes and Recovery

Scenario 1: WAL Disk Full

Symptoms: PANIC: could not write to WAL file, database stops accepting writes.

Immediate actions:

Check disk space: df -h /var/lib/postgresql/16/main/pg_wal
Identify removable files: ls -lh /var/lib/postgresql/16/main/pg_wal/ | head -20
Do not delete WAL files manually. Instead, free space elsewhere (old backups, logs, tmp) or expand the volume.

Recovery verification:

systemctl start postgresql@16-main
tail -f /var/log/postgresql/postgresql-16-main.log
-- LOG:  database system was interrupted; last known up at ...
-- LOG:  entering standby mode (if replica) or
-- LOG:  database system is ready to accept connections

Scenario 2: Replication Slot Retention Causing WAL Accumulation

Symptoms: pg_wal grows unbounded; pg_replication_slots shows inactive slot with restart_lsn far behind.

Diagnosis:

SELECT slot_name, plugin, slot_type, datoid, database,
       active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Resolution: Drop the inactive slot:

SELECT pg_drop_replication_slot('stale_slot_name');

Verification: pg_wal size stabilizes; pg_replication_slots no longer shows the slot.

Scenario 3: Corrupted Index

Symptoms: ERROR: index "idx_name" contains unexpected data, or query returns wrong results silently.

Recovery:

REINDEX INDEX CONCURRENTLY idx_name;
-- Or for entire table:
REINDEX TABLE CONCURRENTLY large_table;

Verification: Run the failing query; check pg_stat_user_indexes for idx_scan incrementing.

Scenario 4: Transaction ID Wraparound Risk

Symptoms: WARNING: database "dbname" must be vacuumed within N transactions in logs.

Immediate action:

VACUUM (FREEZE) DATABASE critical_database;
-- Or per-table for less lock contention:
VACUUM (FREEZE, ANALYZE) public.critical_table;

Monitor progress:

SELECT pid, phase, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, max_dead_tuples
FROM pg_stat_progress_vacuum;

Verification: age(datfrozenxid) decreases:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
WHERE datallowconn
ORDER BY xid_age DESC;

Scenario 5: Accidental DROP TABLE

Recovery options (in order of preference):

PITR (Point-in-Time Recovery) from base backup + WAL archive — restores to a specific timestamp.
Logical backup (pg_dump) if recent enough.
pg_dirtyread extension (last resort, reads dead tuples) — requires superuser and CREATE EXTENSION pg_dirtyread;.

PITR outline:

# 1. Stop cluster
systemctl stop postgresql@16-main

# 2. Restore base backup to new data directory
pg_basebackup -D /var/lib/postgresql/16/main_recovery -Ft -z -P -h backup_host -U replicator

# 3. Create recovery.signal and configure restore_command in postgresql.conf
echo "restore_command = 'cp /mnt/wal_archive/%f %p'" >> /var/lib/postgresql/16/main_recovery/postgresql.conf
echo "recovery_target_time = '2024-01-15 14:30:00'" >> /var/lib/postgresql/16/main_recovery/postgresql.conf
touch /var/lib/postgresql/16/main_recovery/recovery.signal

# 4. Start and verify
systemctl start postgresql@16-main_recovery

Operations Checklist

Use this checklist during routine maintenance and incident response.

Daily

[ ] Check pg_stat_replication lag < 100 MB on all replicas
[ ] Verify pg_stat_activity connection count < 70% of max_connections
[ ] Scan logs for ERROR, FATAL, PANIC in last 24 hours
[ ] Confirm pg_wal directory size stable (not growing unbounded)
[ ] Verify backup completion and WAL archive success

Weekly

[ ] Review pg_stat_user_tables for tables with dead_pct > 10%
[ ] Check pg_stat_user_indexes for unused indexes > 100 MB
[ ] Run pg_dump --schema-only of critical schemas; verify restore to staging
[ ] Test pg_basebackup from replica (validates backup integrity and replication)
[ ] Review pg_settings for drift from version-controlled config

Monthly

[ ] Full REINDEX DATABASE during maintenance window (or CONCURRENTLY per table)
[ ] Verify PITR restore to a staging cluster from latest base backup + WAL
[ ] Update PostgreSQL minor version if security patches released
[ ] Review and rotate pg_hba.conf entries; remove stale access
[ ] Capacity planning: project pg_database_size growth 90 days out

Incident Response (First 15 Minutes)

[ ] Capture pg_stat_activity snapshot: SELECT * FROM pg_stat_activity \g activity_$(date +%F_%H%M).txt
[ ] Capture pg_locks snapshot if blocking suspected
[ ] Check disk space on pg_data and pg_wal volumes
[ ] Tail PostgreSQL log for active errors
[ ] Identify change window: recent deploy, config change, schema migration, traffic spike
[ ] Communicate status to stakeholders with observed facts only (no speculation)

Conclusion

Effective PostgreSQL troubleshooting relies on a repeatable loop: observe the current state with version-appropriate commands, form a hypothesis, apply the smallest scoped change, verify the result against an expected signal, and document the rollback path before you need it. This guide gave you concrete queries for version inventory, configuration safety, connection and lock diagnostics, replication health, vacuum and bloat monitoring, index analysis, and log interpretation — plus recovery procedures for the most common failure modes. Integrate the operations checklist into your runbooks, practice PITR restores quarterly, and treat every configuration change as a deployable artifact with a test plan and a revert button. When the next incident arrives, you will move from symptom to resolution with evidence, not guesswork.