How to fix Read only SQL transaction in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 27, 2025· 386 views

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.

Quick fix

Fixes Read only SQL transaction

SELECT pg_is_in_recovery();
  1. Check if the database is in recovery or standby mode
  2. Verify the default transaction read-only setting
  3. Disable read-only mode at the database level
  4. Set the transaction to read-write mode
postgresql — error
SELECT pg_is_in_recovery();
Read only SQL transaction

What this error means

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.

Typical symptoms

  • INSERT, UPDATE, or DELETE statements fail immediately with the error message
  • CREATE, ALTER, or DROP commands are rejected in read-only mode
  • COPY FROM operations fail if copying to permanent tables
  • Connections to database replicas or standby servers cannot perform writes
  • Application continues to connect successfully but fails on any write attempt

Common causes

  1. The transaction was explicitly set to read-only mode using SET TRANSACTION READ ONLY
  2. The database has default_transaction_read_only parameter enabled
  3. You are connected to a PostgreSQL standby (replica) server or hot standby, which is inherently read-only
  4. Connection string points to a read-only replica endpoint instead of the primary database
  5. PostgreSQL is in recovery mode after a crash or during replication setup
  6. Transaction wraparound condition has triggered read-only mode as a protective measure

How to fix "Read only SQL transaction"

1Check if the database is in recovery or standby mode

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

sql
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.

2Verify the default transaction read-only setting

Check the current setting at the session and database level:

sql
SHOW default_transaction_read_only;

Also check the server configuration:

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

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

3Disable read-only mode at the database level

If your database has default_transaction_read_only enabled, disable it with:

sql
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.

4Set the transaction to read-write mode

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

sql
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.

5Verify connection endpoint for cloud databases

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)

6Disable read-only mode in postgresql.conf (if needed permanently)

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:

sql
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.

Advanced notes

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

Frequently asked questions

What does "Read only SQL transaction" mean?
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.
How do I fix "Read only SQL transaction"?
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.
What causes "Read only SQL transaction"?
The transaction was explicitly set to read-only mode using SET TRANSACTION READ ONLY The database has default_transaction_read_only parameter enabled You are connected to a PostgreSQL standby (replica) server or hot standby, which is inherently read-only
Is the "Read only SQL transaction" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading