PostgreSQL Troubleshooting

Introduction

PostgreSQL errors usually trace to connection settings, search_path, missing indexes, long transactions, or disk space—not mysterious engine bugs. This chapter maps common symptoms to fixes and links back to the chapters where each topic is taught.

Prerequisites

• You have run PostgreSQL locally or in Docker — Installation
• Basic terminal and log reading

Diagnostic Flow

Error → classify (connect / auth / schema / constraint / performance / disk) → reproduce in psql → fix one layer

Connection Errors

Connection refused

Cause: Server not running, wrong host/port, Docker service down.

Fix:

docker compose ps
docker exec postgres pg_isready -U postgres -d hello_demo
psql "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo" -c "SELECT 1"

See Installation and Docker ops.

could not connect to server: Connection timed out

Cause: Security group, firewall, wrong hostname in cloud, VPN.

Fix: Open port 5432 to app subnet only—not 0.0.0.0/0 with weak passwords.

Docker: could not translate host name "postgres"

Cause: App running on host uses Compose service name postgres—valid only inside Compose network.

Fix: Use 127.0.0.1 from host; use postgres from api container.

Authentication Errors

password authentication failed for user

Cause: Wrong password, wrong user, pg_hba.conf rejects method or source IP.

Fix:

• Verify POSTGRES_PASSWORD or role password
• Check pg_hba.conf line for your network (scram-sha-256)
• URL-encode special characters in connection string

See Roles and Permissions and Installation.

role "app_user" does not exist

Cause: Init script not run or wrong database cluster.

Fix: Create role or restore from backup; confirm \du in psql.

Schema and Object Errors

relation "posts" does not exist

Cause: Wrong database, wrong schema, table not created.

Fix:

SELECT current_database();
SHOW search_path;
\dt *.*

Create table in public or qualify schema.table.

See Databases, Schemas, and Types.

permission denied for table posts

Cause: App role lacks GRANT.

Fix:

GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

See Roles and Permissions.

Constraint Errors

duplicate key value violates unique constraint

Cause: Insert/update conflicts with UNIQUE or PRIMARY KEY.

Fix: Use ON CONFLICT upsert or validate in app before insert—Update and Upsert.

insert or update on table violates foreign key constraint

Cause: Child user_id (etc.) has no parent row.

Fix: Insert parent first or fix orphaned references—Constraints and Keys.

Performance Problems

Slow SELECT / Seq Scan on large table

Cause: Missing index, stale statistics, function on indexed column.

Fix:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
CREATE INDEX ...;
ANALYZE table_name;

See Indexes and EXPLAIN ANALYZE and Performance.

Query was fast, now slow

Cause: Table bloat, n_dead_tup high, long open transaction blocking vacuum.

Fix:

SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state != 'idle';
VACUUM (ANALYZE) posts;

See Isolation and Locking.

deadlock detected

Cause: Two transactions lock rows in opposite order.

Fix: Retry transaction; lock rows in consistent order; shorten transactions—Isolation and Locking.

Resource Limits

sorry, too many clients already

Cause: max_connections exceeded—connection leak or too many app workers.

Fix: Use connection pool (psycopg-pool, HikariCP, PgBouncer); close idle connections—Performance.

could not extend file: No space left on device

Cause: Disk full—WAL, logs, or bloat.

Fix: Free disk; VACUUM; expand volume; archive old WAL—Docker ops.

Encoding and Migration

Invalid byte sequence for encoding "UTF8"

Cause: Client sends wrong encoding or corrupt import file.

Fix: Ensure client UTF8; re-export source data with proper encoding. PostgreSQL avoids MySQL historic utf8 vs utf8mb4 split—UTF8 covers full Unicode.

Migrating from MySQL

Use pgloader or ETL tools for bulk migration—test on staging.

Compare MySQL track with What Is PostgreSQL.

Transaction Aborted State

current transaction is aborted, commands ignored until end of transaction block

Cause: Prior statement failed inside BEGIN; session stuck until ROLLBACK.

Fix:

ROLLBACK;
-- retry

Apps must rollback on error before new commands—Transactions Basics.

FAQ

Where are PostgreSQL logs?

docker logs, or log_directory in postgresql.conf on native installs.

psql works, app fails?

Different host, SSL mode, or role—compare exact connection strings.

ORM generates wrong SQL?

Enable SQL logging; run EXPLAIN on generated query—fix index or rewrite.

Redis cache shows stale data?

Invalidate on write—Project deploy, Redis caching.

Still stuck?

PostgreSQL mailing lists and Community with EXPLAIN ANALYZE and pg_stat_activity output.

Full chapter map?

PostgreSQL Chapter Index.