Troubleshooting Common Issues
1. Diagnosing Slow Queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) <suspect query>;| Cause | Fix |
|---|---|
| Bad plan | ANALYZE, raise statistics target |
| Missing index | Add covering / partial index |
| Sort spill | Raise work_mem |
| Bloat | VACUUM / pg_repack |
2. Resolving Lock Contention
SELECT bl.pid AS blocked, ka.query AS blocking_query, kl.mode AS blocking_mode
FROM pg_locks bl
JOIN pg_stat_activity ba ON ba.pid = bl.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.relation IS NOT DISTINCT FROM bl.relation
AND kl.pid <> bl.pid AND kl.granted
JOIN pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;3. Fixing Connection Issues
| Error | Check |
|---|---|
| no pg_hba.conf entry | pg_hba.conf rules + reload |
| too many connections | max_connections, use PgBouncer |
| password authentication failed | password_encryption, role exists |
| SSL required | sslmode / cert paths |
| timeout | firewall, listen_addresses |
4. Handling Out of Memory Errors
# Linux: avoid OOM killer killing postgres
vm.overcommit_memory = 2
vm.overcommit_ratio = 80
# Lower work_mem, max_connections; raise per-cgroup limits5. Resolving Disk Space Issues
SELECT pg_size_pretty(pg_database_size(d.datname)), d.datname
FROM pg_database d ORDER BY pg_database_size(d.datname) DESC;
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
FROM pg_replication_slots; -- inactive slots pin WAL!Warning: Never
rm files from pg_wal. Drop unused replication slots and let checkpointer recycle WAL.6. Fixing Replication Lag
| Symptom | Action |
|---|---|
| High network lag | Tune TCP, dedicated link |
| Apply lag on standby | Faster disk, fewer queries on standby |
| WAL flood | wal_compression, batch writes |
| Logical: subscriber slow | Add subscribers, partition publications |
7. Handling Transaction ID Wraparound
SELECT datname, age(datfrozenxid),
round(age(datfrozenxid)::numeric / 2000000000, 3) AS pct
FROM pg_database ORDER BY age(datfrozenxid) DESC;
VACUUM FREEZE VERBOSE;Warning: At ~2B XIDs DB enters read-only safety mode. Tune autovacuum_freeze_max_age and ensure autovacuum keeps up.
8. Debugging Function Errors
SET client_min_messages = debug1;
SET plpgsql.extra_errors = 'all';
SET plpgsql.extra_warnings = 'all';
-- Add RAISE NOTICE in hot paths, or use GET STACKED DIAGNOSTICS9. Resolving Permission Denied Errors
\dp schema.table
SELECT has_table_privilege('alice', 'orders', 'SELECT');
SELECT has_schema_privilege('alice', 'app', 'USAGE');| Layer | Required |
|---|---|
| Database | CONNECT |
| Schema | USAGE |
| Table | SELECT/INSERT/... |
| Sequence | USAGE, SELECT (for currval/nextval) |
10. Analyzing PostgreSQL Logs
# postgresql.conf
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d.log'
log_line_prefix = '%m [%p] %u@%d %r %a '
log_min_duration_statement = 250ms
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0Note: Pipe logs through pgBadger for HTML reports of slow queries, vacuum activity, errors.