PostgreSQL Common Errors and Fixes with Practical Examples

Intro

PostgreSQL is a robust, feature-rich database, but like any complex system it generates errors that can frustrate developers and operators. Many errors have simple causes and well-defined fixes, yet without a systematic approach they can consume hours of investigation. This guide addresses the most common PostgreSQL errors, explains why they happen, and provides practical, step-by-step fixes with commands and expected outputs. Whether you are a developer encountering a cryptic message in your application or an operator troubleshooting a production issue, this article will help you diagnose and resolve problems efficiently. We cover connection and authentication failures, SQL and schema mistakes, resource exhaustion, and data corruption, along with verification techniques and rollback strategies.

Version and Environment Inventory

Before diving into specific errors, establish a clear picture of your PostgreSQL environment. Knowing exact versions and configuration prevents misdiagnosis, as behavior can vary across releases.

Check PostgreSQL Version

Run the following command to see the server version:

psql -U postgres -c 'SELECT version();'

Expected output (example):

version
-----------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc...

Check Configuration Files

Locate the main configuration file, postgresql.conf, and the client authentication file, pg_hba.conf. Typically, they are in the data directory (/var/lib/postgresql/data/ on Debian/Ubuntu or /var/lib/pgsql/data/ on RHEL/CentOS). You can find their exact locations with:

SHOW config_file;
SHOW hba_file;

Example output:

config_file
------------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)

 hba_file
-------------------------------------
 /etc/postgresql/14/main/pg_hba.conf
(1 row)

Verify Connectivity

Ensure you can connect locally before attempting remote fixes:

psql -U postgres -h localhost -c 'SELECT 1;'

If this fails, your problem may be network or authentication related, which we cover next.

Connection and Authentication Errors

Connection issues are among the most frequent problems. They usually manifest as connection refused, no pg_hba.conf entry, or password authentication failed. We address each with causes and fixes.

Case 1: "Connection refused"

Symptom:

psql: error: could not connect to server: Connection refused
       Is the server running on host "localhost" and accepting
       TCP/IP connections on port 5432?

Cause: The PostgreSQL server is not running, or it is not listening on the expected interface/port.

Diagnosis:

1. Check if the server process is alive:

pg_isready

Example output if server is down:

/tmp:5432 - no response

1. Check if the port is open:

ss -tlnp | grep 5432

No output means nothing is listening.

Fix:

Start the server using the appropriate service command:

sudo systemctl start postgresql

Or, if using pg_ctl:

pg_ctl -D /var/lib/postgresql/data start

Verify with pg_isready again; you should see:

/tmp:5432 - accepting connections

If the server fails to start, check the log file (usually in /var/log/postgresql/ or the data directory) for errors.

Case 2: "no pg_hba.conf entry for host"

Symptom:

FATAL: no pg_hba.conf entry for host "192.168.1.100", user "appuser", database "appdb", SSL off

Cause: The client's IP address, user, or database is not allowed by the authentication rules in pg_hba.conf.

Fix:

Edit pg_hba.conf to add an appropriate entry. For example, to allow password authentication for a specific user from a specific subnet:

# TYPE  DATABASE USER     ADDRESS        METHOD
host    appdb    appuser  192.168.1.0/24 scram-sha-256

After editing, reload the configuration:

sudo systemctl reload postgresql

Or, as a superuser:

SELECT pg_reload_conf();

Then test the connection again.

Case 3: "password authentication failed"

Symptom:

FATAL: password authentication failed for user "appuser"

Cause: Incorrect password, wrong authentication method, or the role does not exist.

Diagnosis:

First, verify the role exists:

SELECT rolname FROM pg_roles WHERE rolname='appuser';

If no row is returned, create the role:

CREATE ROLE appuser LOGIN PASSWORD 'strongpassword';

If the role exists, reset the password:

ALTER ROLE appuser WITH PASSWORD 'newstrongpassword';

Also, ensure the authentication method in pg_hba.conf matches the password encryption. For example, if the password was set with md5 but the method is scram-sha-256, you may need to reset the password with the new method or adjust the password_encryption setting. The default in PostgreSQL 14+ is scram-sha-256.

After changes, reload and test.

Case 4: "too many connections"

Symptom:

FATAL: sorry, too many clients already

Cause: The number of concurrent connections has reached max_connections.

Fix:

First, check current connections and the limit:

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

Example:

max_connections
-----------------
 100
(1 row)

 count
-------
   100
(1 row)

If the count is at the limit, you can either increase max_connections (requires restart) or, better, implement connection pooling. To increase, edit postgresql.conf:

max_connections = 200

Then restart:

sudo systemctl restart postgresql

But be cautious: increasing max_connections consumes more memory. Consider using a pooler like PgBouncer.

SQL and Schema Errors

These errors occur during query execution and often point to typos, missing objects, or permission issues.

Case 1: "relation does not exist"

Symptom:

ERROR: relation "users" does not exist
LINE 1: SELECT * FROM users;

Cause: The table is not visible in the current schema, or the table name is misspelled, or the user lacks privileges.

Fix:

Check the current search path:

SHOW search_path;

Example output:

   search_path
-----------------
 "$user", public
(1 row)

If your table is in a non-default schema, either qualify the table name (myschema.users) or add the schema to search_path:

SET search_path TO myschema, public;

To make it permanent for a user:

ALTER ROLE appuser SET search_path TO myschema, public;

Also, verify the table exists:

SELECT * FROM information_schema.tables WHERE table_name='users';

Case 2: "column does not exist"

Symptom:

ERROR: column "emial" does not exist

Cause: Typo in column name or column not created.

Fix:

List columns of the table:

\d users

Or:

SELECT column_name FROM information_schema.columns WHERE table_name='users';

Correct the query or add the missing column with ALTER TABLE ... ADD COLUMN.

Case 3: "syntax error at or near"

Symptom:

ERROR: syntax error at or near "FROM"
LINE 2: FROM users;

Cause: SQL statement is malformed, often missing a keyword or misplaced comma.

Fix:

Carefully review the query. Use a SQL client with syntax highlighting to spot issues. For complex queries, break them into smaller parts and test incrementally.

Case 4: "permission denied"

Symptom:

ERROR: permission denied for table users

Cause: The role executing the query lacks necessary privileges.

Fix:

Grant the required permissions. For example, to grant SELECT on a table:

GRANT SELECT ON users TO appuser;

For all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

Verify with:

SELECT * FROM information_schema.role_table_grants WHERE grantee='appuser' AND table_name='users';

Resource and Performance Errors

Resource exhaustion can bring a database to its knees. Common errors include out of shared memory, out of disk space, and lock timeouts.

Case 1: "out of shared memory"

Symptom:

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

Cause: The shared memory allocated for lock tracking is exhausted. This can happen with large transactions that touch many objects.

Fix:

Increase max_locks_per_transaction in postgresql.conf. The default is 64. A higher value may require a restart. For example:

max_locks_per_transaction = 128

After changing, restart PostgreSQL:

sudo systemctl restart postgresql

To monitor lock usage:

SELECT count(*) FROM pg_locks;

Case 2: "canceling statement due to statement timeout"

Symptom:

ERROR: canceling statement due to statement timeout

Cause: The statement exceeded the statement_timeout setting.

Fix:

Either optimize the query to run faster or increase the timeout. To see the current setting:

SHOW statement_timeout;

To increase for the session:

SET statement_timeout = '5min';

To set globally in postgresql.conf:

statement_timeout = '2min'

Then reload:

sudo systemctl reload postgresql

Case 3: "deadlock detected"

Symptom:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 678; blocked by process 12346.

Cause: Two or more transactions are waiting on each other, causing a cycle.

Fix:

PostgreSQL automatically resolves deadlocks by aborting one of the transactions. The application should catch the error and retry. To reduce deadlocks, ensure consistent locking order in transactions and keep transactions short.

Case 4: "could not write to file ... No space left on device"

Symptom:

ERROR: could not write to file "pg_wal/...": No space left on device

Cause: Disk full, likely due to large WAL files, logs, or data growth.

Fix:

Check disk usage:

df -h /var/lib/postgresql/data

Example output:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1       100G  100G   0G  100% /var/lib/postgresql/data

Free space by deleting old backups, archiving WALs, or expanding the volume. In emergency, you can remove old WAL files only if you are sure they are not needed for replication or PITR. Do not manually delete files unless necessary; use pg_archivecleanup or configure archive_command properly.

Data Corruption and Recovery

Data corruption is rare but serious. It can be caused by hardware failures, improper shutdowns, or filesystem issues.

Case 1: "invalid page in block"

Symptom:

ERROR: invalid page in block 12345 of relation "users"

Cause: A data page is corrupted.

Diagnosis:

Run a consistency check on the table:

pg_dump -t users dbname > /tmp/users.sql

If pg_dump fails with a similar corruption error, you may need to restore from a backup.

Fix:

Restore the table from a recent backup if available. If not, you can attempt to skip corrupted blocks using zero_damaged_pages (risky; data in those blocks is lost):

# In postgresql.conf (temporarily, after backup)
zero_damaged_pages = on

Then restart and access the table to mark damaged pages as zero. Immediately back up what you can and then restore from a clean source if possible.

Case 2: "could not read block ... read-only file system"

Symptom:

ERROR: could not read block 12345 in file "base/...": Input/output error

Cause: Underlying storage failure.

Fix:

Check system logs (dmesg) for disk errors. If the disk is failing, replace it and restore from backup. Ensure you have tested backups regularly.

Verification and Diagnostics

After applying fixes, verify that the issue is resolved and monitor for regressions.

Check Server Status

Use pg_isready as a quick check:

pg_isready -h localhost -p 5432

Expected: localhost:5432 - accepting connections

Test Query Execution

Run the previously failing query. For connection issues, connect:

psql -U appuser -d appdb -c 'SELECT 1;'

Monitor Logs

Tail the PostgreSQL log during reproduction:

tail -f /var/log/postgresql/postgresql-14-main.log

Look for ERROR or FATAL lines.

Check Active Sessions and Locks

SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';

For lock waits:

SELECT * FROM pg_locks WHERE NOT granted;

Verify Configuration Changes

After changing postgresql.conf, confirm the setting took effect:

SHOW max_connections;

Failure Modes and Recovery

Even with careful fixes, things can go wrong. Prepare rollback strategies for each change.

Configuration Changes

Always back up configuration files before editing:

sudo cp postgresql.conf postgresql.conf.bak
sudo cp pg_hba.conf pg_hba.conf.bak

If a change causes problems, restore the backup and reload or restart.

Database Alterations

For schema changes, use transactional DDL where possible:

BEGIN;
ALTER TABLE users ADD COLUMN email TEXT;
-- verify
COMMIT;

If an error occurs, ROLLBACK reverts changes atomically.

Restoring from Backup

Regularly test backups. A reliable recovery procedure is:

• Stop the database (if possible).
• Restore the backup using pg_restore or psql.
• Replay WAL archives if using point-in-time recovery.
• Verify data integrity and then restart.

Example restore from a custom-format dump:

pg_restore -U postgres -d targetdb /backups/db.dump

Common Pitfalls and Mistakes

Avoid these common mistakes when troubleshooting PostgreSQL.

1. Making configuration changes without a backup. Always keep a copy of postgresql.conf and pg_hba.conf before modifying them. A syntax error can prevent the server from starting. For example, if you accidentally add a blank line inside a parameter block, PostgreSQL will fail to parse the file and refuse to start. Keep a backup and test changes in a staging environment first.

1. Ignoring log files. PostgreSQL logs often contain the exact cause of an error. For example, if authentication fails, the log may show the client's IP, user, and database, which helps identify a missing pg_hba.conf entry. Always check logs before guessing.

1. Relying on max_connections instead of pooling. Increasing max_connections can lead to OOM errors due to per-connection memory usage. For example, setting max_connections to 500 on a 2 GB RAM server can exhaust memory. Use a connection pooler like PgBouncer to multiplex connections and keep max_connections low (e.g., 100).

1. Manually deleting WAL files. Never delete WAL files without understanding replication and backup implications. Doing so can break streaming replication and point-in-time recovery. Always use pg_archivecleanup or adjust archive management policies.

1. Not testing backups. A backup that has never been restored is not a backup. Practice restoring from backups regularly to ensure they are valid and to reduce recovery time. For example, a corrupted block error can only be fully resolved if you have a clean backup to restore from.

Operations Checklist

Use this checklist to streamline PostgreSQL troubleshooting and maintenance.

Each step should have a clear owner. Designate a database administrator (DBA) or on-call engineer as the accountable owner for checklist execution. The checklist should be reviewed monthly to incorporate new incidents and improve runbooks.

Conclusion

PostgreSQL errors can be intimidating, but with a methodical approach, most are quickly resolved. This guide covered frequent issues in connection, authentication, SQL, schema, and resource management, along with data corruption scenarios. Always start by inventorying your environment, reproduce the error, apply a narrow fix, and verify. Use the provided checklist for consistent operations, and keep rollback plans ready. By mastering these common errors, you will reduce downtime and improve the reliability of your PostgreSQL deployments.