Skip to content
DevOps AI ToolKit
Newsletter
PostgreSQL Troubleshooting Toolkit

PostgreSQL Troubleshooting Toolkit

Use this PostgreSQL troubleshooting toolkit to diagnose connection exhaustion, locks and deadlocks, slow queries, replication lag, autovacuum bloat, and disk-full incidents.

Paste your error and get a triage plan.

Paste a log line, CLI error, stack trace, service failure, or config snippet and get a structured troubleshooting plan. Your snippet is carried straight into the AI Incident Response Assistant with PostgreSQL context prefilled.

Open the PostgreSQL Incident Assistant → Browse PostgreSQL prompts

Do not paste secrets, tokens, private keys, passwords, or customer data. Your snippet stays in your browser until you open the assistant.

Top 25 PostgreSQL Errors and Failure Modes

The production failures engineers hit most — each links to a full cause → fix → prevention guide.

FATAL: sorry, too many clients already

All available connection slots are in use, usually because clients open connections faster than they close them or no pooler sits in front of Postgres.

FATAL: too many connections for role

A specific role has hit its per-role CONNECTION LIMIT even though the server still has global slots free.

FATAL: remaining connection slots are reserved for superusers

Normal clients are locked out because the last few slots are held back by superuser_reserved_connections for emergency admin access.

could not connect to server: Connection refused

The client cannot reach Postgres because the server is down, bound to the wrong address, or blocked by listen_addresses, firewall, or the wrong port.

deadlock detected

Two or more transactions each hold a lock the other needs, so Postgres aborts one of them to break the cycle.

could not serialize access due to concurrent update

A SERIALIZABLE or REPEATABLE READ transaction hit a serialization failure and must be retried by the application.

out of shared memory

Postgres ran out of shared memory, most often because max_locks_per_transaction is too low for a workload touching many partitions or objects at once.

could not extend file / No space left on device

The data or WAL filesystem is full, so Postgres cannot extend relations and writes begin to fail.

canceling statement due to lock timeout

A statement waited longer than lock_timeout to acquire a lock and was aborted, typically during contended DDL or migrations.

canceling statement due to statement timeout

A query exceeded the configured statement_timeout and was killed to protect the server from runaway execution.

database is not accepting commands to avoid wraparound data loss

Transaction ID wraparound is imminent because autovacuum fell behind on freezing, forcing the database into a protective read-only state.

relation does not exist

A query references a table or view that is missing, misspelled, or not on the current search_path or schema.

permission denied for table / relation

The connecting role lacks the required GRANT on the object or its schema.

password authentication failed / pg_hba.conf rejects connection

Authentication failed due to a bad credential or a pg_hba.conf rule that does not match the client, database, user, or auth method.

SSL connection closed unexpectedly / SSL required

The TLS handshake failed or was dropped because of an sslmode mismatch, expired certificate, or a server that requires SSL.

invalid page in block (data/index corruption)

Postgres detected a corrupt page, usually from bad storage or an unclean shutdown, and may require reindexing or restore from backup.

duplicate key value violates unique constraint

An INSERT or UPDATE tried to write a value that collides with an existing unique or primary key, often a sequence or upsert issue.

canceling statement due to conflict with recovery

A query on a hot standby was cancelled because replay of the primary's changes conflicted with the reading transaction.

autovacuum not keeping up / table and index bloat

Dead tuples accumulate faster than autovacuum can reclaim them, inflating table and index size and degrading performance.

slow query / bad execution plan

A query runs far slower than expected due to missing indexes, stale statistics, or a planner misestimate choosing a poor plan.

replication lag / WAL bloat

A standby falls behind the primary or WAL accumulates on disk, risking stale reads, failover gaps, and a full pg_wal directory.

lock contention / could not obtain lock

Transactions pile up waiting on heavily contended row or table locks, stalling throughput even without a full deadlock.

idle in transaction session timeout / long-lived transactions guide coming soon

Sessions left open in a transaction hold locks and pin the xmin horizon, blocking vacuum and bloating the database.

out of memory / killed by OOM killer guide coming soon

A backend or the whole cluster is terminated when work_mem times concurrency or a large query exhausts host memory.

PANIC: could not write to WAL / checkpoint pressure guide coming soon

The server panics or stalls when it cannot write WAL, often from a full pg_wal volume, undersized max_wal_size, or overly frequent checkpoints.

Validate your config before you redeploy

Catch the structural mistakes that cause outages — 100% in your browser, nothing uploaded.

YAML Validator (config / manifests) Not sure? Start with the Incident Assistant →

Best PostgreSQL Prompts

Turn symptoms, logs, and config into a structured plan with prompts tuned for PostgreSQL.

Postgres Slow Query EXPLAIN Triage

Diagnose slow queries and read EXPLAIN plans

Use this prompt

Postgres Lock Contention & Deadlock Investigation

Trace blocking chains and resolve deadlocks

Use this prompt

Postgres Replication Lag Debug

Find and fix standby lag and WAL buildup

Use this prompt

PgBouncer Pool Sizing & Tuning

Right-size connection pooling and modes

Use this prompt

Postgres Vacuum, Bloat & Autovacuum Tuning

Tame bloat and tune autovacuum thresholds

Use this prompt

Postgres Index Advisor with pg_stat_statements

Recommend and prune indexes from real workload

Use this prompt

Postgres Statement Timeout & Runaway Query Governance

Set timeouts to cap runaway queries

Use this prompt

Postgres XID Wraparound Prevention

Monitor freeze age and prevent wraparound

Use this prompt

Download the PostgreSQL Troubleshooting Runbook Pack

First-response checklists for a struggling Postgres instance — connections, locks, queries, replication, and vacuum.

  • Connection exhaustion & pooling
  • Locks & deadlock triage
  • Slow query & EXPLAIN analysis
  • Replication lag & WAL
  • Autovacuum, bloat & wraparound
Download the PostgreSQL Runbook Pack

All PostgreSQL Troubleshooting Guides

Grouped by failure-mode type — each guide covers cause, fix, validation, and prevention.

Authentication & TLS (3)

Postgres 'permission denied for table': Fix GRANTs, Schema USAGE, and Roles SSL connection has been closed unexpectedly password authentication failed for user

Networking (7)

Postgres Error: 'remaining connection slots are reserved terminating connection due to administrator command too many connections for role Connection refused remaining connection slots are reserved for non-replication superuser connections too many clients already Postgres Connection Pooling With PgBouncer and AI

Storage (3)

cannot execute INSERT in a read-only transaction No space left on device Postgres Point-in-Time Recovery and WAL Archiving With AI

Deployment & Builds (3)

Building Full-Text Search in Postgres With AI Query Plan Hints and Steering the Postgres Planner With AI Zero-Downtime, Lock-Aware Postgres Schema Migrations With AI

Performance (9)

out of memory could not resize shared memory segment canceling statement due to lock timeout out of shared memory deadlock detected canceling statement due to statement timeout Debugging Slow Postgres Queries With AI and EXPLAIN ANALYZE Diagnosing Postgres Lock Contention and Deadlocks With AI Taming Postgres Bloat and Autovacuum With AI

Configuration (4)

Postgres Error: 'invalid byte sequence for encoding Postgres Error: 'syntax error at or near invalid input syntax for type integer invalid page in block

Other (39)

Postgres Error: 'cannot drop ... because other objects depend on it Postgres Error: 'could not serialize access due to concurrent update Postgres Error: 'current transaction is aborted, commands ignored until end of transaction block Postgres Error: 'database "..." does not exist Postgres Error: 'division by zero Postgres 'duplicate key value violates unique constraint': Causes and Fix Postgres Error: 'insert or update on table violates foreign key constraint Postgres Error: 'more than one row returned by a subquery used as an expression Postgres Error: 'must appear in the GROUP BY clause or be used in an aggregate function Postgres Error: 'no pg_hba.conf entry for host Postgres Error: 'operator does not exist Postgres Error: 'there is already a transaction in progress Postgres Error: 'type "..." does not exist Postgres Error: 'update or delete on table violates foreign key constraint the database system is starting up integer out of range null value in column violates not-null constraint role value too long for type character varying(n) violates foreign key constraint column there is no unique constraint matching given keys prepared statement index row size exceeds btree maximum canceling statement due to conflict with recovery could not serialize access due to concurrent update duplicate key value violates unique constraint Change Data Capture From Postgres With Logical Replication and AI Designing JSONB Columns in Postgres With AI Essential Postgres Extensions: pg_stat_statements, pg_repack, TimescaleDB With AI Multi-Tenant Isolation With Postgres Row-Level Security and AI Postgres 'relation does not exist': Fix search_path, Case, and Schema Issues database is not accepting commands to avoid wraparound data loss Querying External Data With Postgres Foreign Data Wrappers and AI Speeding Up Dashboards With Postgres Materialized Views and AI AI-Assisted Postgres Index Design and Killing Redundant Indexes Partitioning Large Postgres Tables With AI Setting Up and Debugging Postgres Replication With AI Tuning postgresql.conf for Your Workload With AI
Browse the full PostgreSQL category

PostgreSQL troubleshooting FAQ

How do I fix 'too many clients already' in Postgres?
This means all max_connections slots are in use. Put a pooler like PgBouncer in front of Postgres so many app connections share a small pool of backend connections, close idle sessions, set idle_in_transaction_session_timeout, and only raise max_connections if the server has memory to spare (each connection costs RAM).
Why is my Postgres query slow?
Run EXPLAIN (ANALYZE, BUFFERS) on the query. Slowness usually comes from a missing or unusable index, a sequential scan over a large table, stale statistics after a bulk change (fix with ANALYZE), or a planner misestimate. Add the right index, refresh statistics, and check for lock waits or bloat if plans look reasonable but runtime is still high.
How do I resolve a Postgres deadlock?
Postgres automatically aborts one transaction in a deadlock and reports 'deadlock detected'. To prevent recurrence, have all transactions lock rows and tables in a consistent order, keep transactions short, use SELECT ... FOR UPDATE deliberately, and add application-level retry logic since a deadlock victim can safely be retried.
What causes Postgres replication lag?
Lag comes from a slow or overloaded standby, long-running queries on the replica delaying WAL replay, network bottlenecks, or a write-heavy primary generating WAL faster than the standby can apply it. Check pg_stat_replication for write/flush/replay LSN gaps, tune the standby's I/O and hot_standby_feedback, and ensure replication slots aren't retaining excess WAL.
How do I fix transaction ID wraparound in Postgres?
Wraparound happens when autovacuum can't freeze old rows fast enough. Identify the tables with the oldest relfrozenxid via age(relfrozenxid), run VACUUM (or VACUUM FREEZE) on them, and clear anything blocking vacuum such as long-idle transactions or full disks. Then tune autovacuum to be more aggressive and monitor freeze age so it never approaches the limit again.
Why does Postgres say 'No space left on device' or 'could not extend file'?
The data directory or the pg_wal volume is full. Free space immediately by archiving or removing old logs, check for unbounded WAL caused by an inactive replication slot or failing archive_command, and look for table and index bloat. Longer term, drop stale replication slots, fix WAL archiving, add disk, and monitor free space with alerts.