This error occurs when PostgreSQL is performing recovery after an improper shutdown or when WAL control functions are executed on a standby/replica server. Most cases resolve automatically once recovery completes.
PostgreSQL enters recovery mode in two main scenarios. First, when the database is started after an unclean shutdown (crash, power loss, or forced termination), PostgreSQL must replay Write-Ahead Log (WAL) records from the last checkpoint to restore consistency. Second, standby/replica servers are perpetually in recovery mode while replicating from a primary server. During recovery, certain WAL control functions (like pg_current_wal_lsn(), pg_start_backup(), and pg_replication_slots queries) are restricted because they can only run on the primary server.
If PostgreSQL just started, allow the automatic recovery process to complete. Watch the logs:
psql -U postgres -d postgres -c "SELECT pg_is_in_recovery()"Returns t (true) if in recovery, f (false) if fully started. The recovery process typically completes within minutes unless the WAL is very large.
While PostgreSQL is recovering, you can monitor progress:
# Check if recovery is still running
ps aux | grep "startup recovering"
# Use pg_controldata to see recovery state (if available)
pg_controldata -D $PGDATALook for incrementing WAL positions to confirm recovery is progressing.
Do not restart PostgreSQL or interrupt the process. Let it complete fully. For very large databases with extensive WAL files, recovery can take hours. Check logs for:
LOG: database system was interrupted; last known up at 2024-12-28 10:15:23
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/...
LOG: redo done at 0/...
LOG: database system is ready to accept connectionsIf the error occurs on a standby server, use functions designed for standby nodes:
-- Instead of:
SELECT pg_current_wal_lsn(); -- ERROR on standby
-- Use on standby:
SELECT pg_last_wal_receive_lsn(); -- Safe on standby
SELECT pg_last_wal_replay_lsn(); -- Safe on standby
SELECT pg_is_in_recovery(); -- Safe on standbyUpdate monitoring scripts to check recovery mode before running restricted functions:
-- Safe way to check replication slots on any server:
IF NOT pg_is_in_recovery() THEN
SELECT slot_name, database FROM pg_replication_slots;
END IF;
-- Or use the appropriate standby functions:
SELECT slot_name, database, active FROM pg_replication_slots
WHERE NOT pg_is_in_recovery() OR function_name != 'pg_wal_lsn_diff';For PostgreSQL 15+, use the log_startup_progress_interval parameter to get periodic progress updates during recovery: ALTER SYSTEM SET log_startup_progress_interval = '10s'; SELECT pg_ctl_restart();. Recovery time depends on WAL volume and I/O performance; on systems with many transactions, recovery can legitimately take hours. If recovery appears stuck (no progress for extended time), check for disk I/O bottlenecks, corrupted WAL files, or hardware failures. Use pg_wal_replay_pause() on primary (not standby) to temporarily pause WAL replay during streaming replication if needed.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL