How to fix Cannot execute in recovery in PostgreSQL

First, check if your connection is pointing to a standby or primary server using the pg_is_in_recovery() function:

SELECT pg_is_in_recovery();

If this returns true, you are connected to a standby/replica server. If it returns false, you are connected to the primary/master server.

Update your application connection string or database configuration to point to the primary/master server instead of the standby.

For direct connections:

primary_host = your-primary-server.example.com
primary_port = 5432

For AWS RDS:
Use the writer endpoint instead of the read replica endpoint:

# Wrong (read replica):
postgres://read-replica-instance.abc123.us-east-1.rds.amazonaws.com:5432/dbname

# Correct (primary):
postgres://my-instance.abc123.us-east-1.rds.amazonaws.com:5432/dbname

If using a connection pool or application framework, ensure read-only queries can use replicas while write operations always use the primary.

Example with node-postgres (pg):

const primaryPool = new Pool({
  host: 'primary.example.com',
  user: 'postgres',
  password: 'password',
  database: 'mydb',
});

const replicaPool = new Pool({
  host: 'replica.example.com',
  user: 'postgres',
  password: 'password',
  database: 'mydb',
});

// Use primaryPool for write operations
await primaryPool.query('INSERT INTO users VALUES (...)');

// Use replicaPool for read-only operations
const result = await replicaPool.query('SELECT * FROM users');

If using a load balancer or AWS Route 53, verify that your database endpoint routes to the primary server for write operations.

For AWS Route 53:
- Ensure write operations go to the primary DNS endpoint
- Configure read replicas with separate endpoints for read-only traffic

For HAProxy or pgBouncer:

# pgBouncer configuration example
[databases]
mydb_primary = host=primary.example.com dbname=mydb
mydb_replica = host=replica.example.com dbname=mydb

After updating your connection to the primary server, test a write operation:

INSERT INTO test_table (name) VALUES ('test');
UPDATE test_table SET name = 'updated' WHERE id = 1;
DELETE FROM test_table WHERE id = 1;

These operations should now execute successfully without the "Cannot execute in recovery" error.