How to fix cannot execute in recovery mode in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 22, 2025· 227 views

This error occurs when PostgreSQL is in recovery mode (e.g., on a Hot Standby replica), which restricts write operations. The database must either complete recovery or be promoted to primary mode to execute write commands.

Quick fix

Fixes cannot execute in recovery mode

SELECT pg_is_in_recovery();
  1. Check if the database is in recovery mode
  2. Verify your connection target
  3. Check and disable default_transaction_read_only
  4. Wait for recovery to complete (if stuck)
postgresql — error
SELECT pg_is_in_recovery();
cannot execute in recovery mode

What this error means

PostgreSQL enters recovery mode during several scenarios: after an unclean shutdown, when operating as a Hot Standby (read-only replica), or during automatic crash recovery. When in recovery mode, the database is read-only and only allows SELECT queries. Any attempt to execute Data Manipulation Language (DML) commands like INSERT, UPDATE, DELETE, TRUNCATE, or COPY FROM will fail with this error. The database uses WAL (Write-Ahead Log) files to replay transactions, and write operations cannot be executed until recovery is complete or the standby is promoted to primary.

Typical symptoms

  • All write operations fail with "cannot execute in recovery mode" error
  • SELECT queries work normally but INSERT, UPDATE, DELETE fail
  • DDL commands like CREATE INDEX, CREATE TABLE are rejected
  • Error occurs consistently regardless of the operation type
  • In a replication setup, all operations on the standby fail

Common causes

  1. Database is a Hot Standby replica in read-only mode (most common)
  2. Database is still recovering after an unclean shutdown
  3. PostgreSQL failed to exit recovery mode due to incomplete WAL replay
  4. Incorrect connection string pointing to the standby instead of primary
  5. default_transaction_read_only is enabled on the database
  6. Hardware failure or shared memory corruption preventing full recovery

How to fix "cannot execute in recovery mode"

1Check if the database is in recovery mode

Connect to the database and run:

sql
SELECT pg_is_in_recovery();

If this returns true, your database is in recovery/read-only mode. If it returns false, the error is likely due to default_transaction_read_only being enabled.

2Verify your connection target

If you have a replication setup with a primary and standby, ensure you are connecting to the primary database for write operations:

bash
psql -h primary-host -U username -d dbname

Write operations must be executed on the primary database. The standby is strictly read-only and will always reject write operations.

3Check and disable default_transaction_read_only

If pg_is_in_recovery() returns false but you still get the error, check the database setting:

sql
SELECT current_setting('default_transaction_read_only');

If it shows on, disable it:

sql
ALTER DATABASE your_database SET default_transaction_read_only = off;

Then reconnect to apply the change.

4Wait for recovery to complete (if stuck)

If the database is still recovering after a crash, monitor the recovery progress by checking:

sql
SELECT pg_is_wal_replay_paused();
SELECT pg_last_wal_receive_lsn();

If recovery is paused, resume it:

sql
SELECT pg_wal_replay_resume();

Recovery can take significant time depending on WAL file size. Allow it to complete before attempting write operations.

5Promote standby to primary (if appropriate)

If your standby should become the new primary (e.g., after failover), promote it using the pg_ctl utility:

On the standby server:

bash
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/data

Or use SQL on the standby:

sql
SELECT pg_promote();

This will exit recovery mode and allow the database to accept write operations. Update your application connection strings to point to this server once promotion is complete.

6Verify recovery_target_action for automatic recovery

If recovery gets stuck, check and modify the recovery action setting in postgresql.conf:

sql
ALTER SYSTEM SET recovery_target_action = 'promote';
SELECT pg_reload_conf();

This setting determines what happens when recovery is complete:
- promote: Exits recovery and allows write operations (for standby)
- pause: Pauses recovery, requiring manual SELECT pg_wal_replay_resume();
- shutdown: Shuts down after recovery

For standby servers, promote is typically the right choice.

Advanced notes

Hot Standby vs. Recovery Mode: In a streaming replication setup, the standby is always technically "in recovery" (replaying WAL), but the error usually means the standby has not been promoted to primary. Hot Standby mode allows read queries but strictly forbids writes.

Temporary Tables and Locks: Even creating temporary tables or acquiring row locks (SELECT FOR UPDATE) will fail on a standby, as they require updating system catalog files.

Two-Phase Commits: PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED are not allowed during recovery, even though they might appear read-only.

dblink and External Operations: Some PL functions using dblink or external tools can still write outside the database, even in read-only mode, but are rarely used for this purpose.

Hardware Failures: If recovery is stuck indefinitely and refuses to complete, this may indicate hardware problems (RAM corruption, disk errors, or filesystem corruption). Consider checking system logs and running disk checks before trying repeated restarts.

Frequently asked questions

What does "cannot execute in recovery mode" mean?
PostgreSQL enters recovery mode during several scenarios: after an unclean shutdown, when operating as a Hot Standby (read-only replica), or during automatic crash recovery. When in recovery mode, the database is read-only and only allows SELECT queries. Any attempt to execute Data Manipulation Language (DML) commands like INSERT, UPDATE, DELETE, TRUNCATE, or COPY FROM will fail with this error.
How do I fix "cannot execute in recovery mode"?
This error occurs when PostgreSQL is in recovery mode (e.g., on a Hot Standby replica), which restricts write operations. The database must either complete recovery or be promoted to primary mode to execute write commands.
What causes "cannot execute in recovery mode"?
Database is a Hot Standby replica in read-only mode (most common) Database is still recovering after an unclean shutdown PostgreSQL failed to exit recovery mode due to incomplete WAL replay
Is the "cannot execute in recovery mode" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading