How to fix cannot execute in recovery mode in PostgreSQL

Connect to the database and run:

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.

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

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.

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

SELECT current_setting('default_transaction_read_only');

If it shows on, disable it:

ALTER DATABASE your_database SET default_transaction_read_only = off;

Then reconnect to apply the change.

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

SELECT pg_is_wal_replay_paused();
SELECT pg_last_wal_receive_lsn();

If recovery is paused, resume it:

SELECT pg_wal_replay_resume();

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

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

On the standby server:

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

Or use SQL on the standby:

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.

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

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.