How to fix Read only SQL transaction in PostgreSQL

Run this query to determine if your PostgreSQL instance is in recovery mode:

SELECT pg_is_in_recovery();

If this returns true, your database is a standby replica (read-only). Ensure your application is connecting to the primary database write endpoint instead.

Check the current setting at the session and database level:

SHOW default_transaction_read_only;

Also check the server configuration:

SELECT name, setting, context FROM pg_settings WHERE name = 'default_transaction_read_only';

If the setting is on, it needs to be disabled.

If your database has default_transaction_read_only enabled, disable it with:

ALTER DATABASE your_database_name SET default_transaction_read_only = off;

Replace your_database_name with your actual database name. Disconnect all existing connections and reconnect for this change to take effect.

If you need to execute a write operation, explicitly set the transaction mode before executing your statements:

BEGIN;
SET TRANSACTION READ WRITE;
INSERT INTO table_name VALUES (...);
COMMIT;

Note: The isolation level and read-write mode must be set at the beginning of the transaction, before any queries are executed.

If using AWS RDS, GCP Cloud SQL, or other managed services:
- Ensure your connection string points to the writer endpoint (primary instance)
- Do not use read-only replica endpoints for write operations
- Check your application configuration to confirm the correct endpoint is being used

Example AWS RDS:
- Writer: mydb.abc123.us-east-1.rds.amazonaws.com (accepts writes)
- Reader: mydb-ro.abc123.us-east-1.rds.amazonaws.com (read-only)

To permanently disable default_transaction_read_only:

1. Locate and edit postgresql.conf (typically in the PostgreSQL data directory)
2. Find or add the line: default_transaction_read_only = off
3. Reload the configuration:

SELECT pg_reload_conf();

Or restart the PostgreSQL server if reloading doesn't work.

Note: Use this only if you understand the implications and have proper backups in place.