This error occurs when attempting to execute write operations (INSERT, UPDATE, DELETE) on a read-only transaction. Common causes include connecting to a read-only replica, hot standby server, or default_transaction_read_only setting enabled.
Fixes Read only SQL transaction
SELECT pg_is_in_recovery();SELECT pg_is_in_recovery();Read only SQL transaction
PostgreSQL error 25006 indicates that you are attempting to execute a Data Manipulation Language (DML) statement such as INSERT, UPDATE, DELETE, MERGE, or COPY on a transaction that has been marked as read-only. This is a safety mechanism to prevent accidental modifications to data in read-only contexts. The error can occur at the transaction level, session level, or server configuration level.
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.
Transaction Wraparound: In rare cases, PostgreSQL may enter read-only mode due to transaction ID wraparound (reaching the 2-billion transaction ID limit). This is a critical maintenance issue requiring immediate attention to the VACUUM process. Contact your database administrator if you suspect this condition.
HA/Replication Failover: When using Patroni, pgpool-II, or similar HA solutions, if a failover occurs while connections are still active, the former primary (now standby) may still have write connections that fail. Monitor your connection pooler and failover configuration.
JDBC/Driver Behavior: Some database drivers execute "SET TRANSACTION" in a separate implicit transaction. If experiencing issues, disable auto-commit in your connection string: jdbc:postgresql://host:5432/db?autoCommit=false