🚨 PostgreSQL Error Guide: 'cannot execute INSERT in a read-only transaction' — Fix Read-Only Writes

Overview

PostgreSQL raises this error when a statement that modifies data runs inside a transaction that is marked read-only:

ERROR:  cannot execute INSERT in a read-only transaction

The same error appears for UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, and any other write — for example cannot execute UPDATE in a read-only transaction or cannot execute CREATE TABLE in a read-only transaction. The SQLSTATE is 25006 (read_only_sql_transaction).

There are three distinct reasons a transaction is read-only, and telling them apart is the whole job: (1) you are connected to a hot standby / physical replica, which is read-only by nature; (2) the server, database, or role has default_transaction_read_only = on; or (3) the session explicitly ran SET TRANSACTION READ ONLY or SET default_transaction_read_only = on. A very common variant is an application whose connection pool or load balancer routed a write to a replica instead of the primary.

Symptoms

• Writes fail with cannot execute <VERB> in a read-only transaction while SELECT queries on the same connection succeed.
• The failure is intermittent in a load-balanced setup — some connections write fine, others fail — depending on which node they landed on.
• After a failover or a replica promotion, writes that used to work start failing (or vice versa).
• All writes fail consistently for one role or one database but work for another.
• Health checks and reads look perfectly healthy; only mutations break.

Common Root Causes

• Connected to a hot standby replica. A physical streaming replica is always read-only; any write attempt raises this error. The most frequent real-world cause.
• Load balancer / pooler routing a write to a replica. Read/write splitting (or a stale endpoint) sent an INSERT/UPDATE to a read-only node.
• default_transaction_read_only = on set globally in postgresql.conf, per-database (ALTER DATABASE), or per-role (ALTER ROLE).
• Explicit session setting. Code (or a connection init hook) ran SET TRANSACTION READ ONLY or BEGIN READ ONLY and then attempted a write.
• A read-only replica endpoint used by mistake. The app’s write path was pointed at the reader DNS name / port instead of the primary.
• Disk-full protection or admin action. An operator deliberately set the cluster read-only during an incident and it was not reverted.

Diagnostic Workflow

First and most important: determine whether you are on a primary or a standby. A standby returns t:

SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 t

If that returns t, you are connected to a read-only replica — the fix is routing, not a setting. Confirm which host/port you actually reached:

SELECT inet_server_addr() AS server_ip, inet_server_port() AS port,
       current_database(), current_user;

If you are on the primary (pg_is_in_recovery() returns f), check the read-only settings at every scope:

-- Effective value in this session, plus where it came from
SHOW default_transaction_read_only;
SHOW transaction_read_only;

SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name IN ('default_transaction_read_only','transaction_read_only');

Check for per-role and per-database overrides that pin read-only:

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig IS NOT NULL;

SELECT datname, datconfig
FROM pg_db_role_setting s
JOIN pg_database d ON d.oid = s.setdatabase;  -- per-db/role settings

If it is intermittent, capture which node each failing connection hit by logging inet_server_addr() and pg_is_in_recovery() from the application on connect.

Example Root Cause Analysis

A service started throwing cannot execute INSERT in a read-only transaction for roughly half of its write requests after an infrastructure change, while reads were completely healthy. The database team confirmed the primary was writable and default_transaction_read_only was off everywhere.

The clue was the “half of requests” pattern. The application connected through a load balancer DNS name that had recently been updated to include the read replica in its rotation for read scaling. The pooler had no read/write awareness, so write transactions were being distributed round-robin across the primary and the replica; every connection that landed on the replica failed, because pg_is_in_recovery() was t there.

The fix was to split the endpoints: the application’s write pool was pointed exclusively at the primary’s writer endpoint, and only the read-only query path used the reader endpoint. As a defensive measure, the app added a connection-init check that logs a warning if pg_is_in_recovery() returns true on a connection intended for writes, so a future mis-route is caught immediately. After separating the endpoints, the errors vanished. The root cause was topology/routing — a write path pointed at a hot standby — not any Postgres configuration.

Prevention Best Practices

• Separate reader and writer endpoints explicitly. Send writes to the primary’s writer endpoint and only route read-only queries to replicas; don’t rely on a round-robin DNS that includes standbys.
• Make the app read/write aware. On connect, check pg_is_in_recovery() for pools meant to write, and fail fast (or re-route) if it returns true.
• Audit default_transaction_read_only at all scopes. Verify it is off on the primary globally, per-database, and per-role unless you intend otherwise.
• Handle failover in the app. After a promotion the roles flip; ensure your driver/pooler re-resolves the primary (target_session_attrs=read-write in libpq/JDBC helps).
• Don’t set BEGIN READ ONLY on write paths. Keep read-only transaction hints on genuinely read-only code paths only.
• Alert on the SQLSTATE. Monitor for 25006; a spike usually means a routing or failover problem, not application logic.

Quick Command Reference

-- Am I on a read-only standby? (t = yes, this is the #1 check)
SELECT pg_is_in_recovery();

-- Which server did I actually connect to?
SELECT inet_server_addr(), inet_server_port(), current_database(), current_user;

-- Effective read-only settings and where they came from
SHOW default_transaction_read_only;
SELECT name, setting, source FROM pg_settings
WHERE name = 'default_transaction_read_only';

-- Per-role / per-database overrides
SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;

# libpq: force connecting only to a writable primary
psql "host=db-a,db-b target_session_attrs=read-write" -c "SELECT pg_is_in_recovery();"

Conclusion

cannot execute INSERT in a read-only transaction (SQLSTATE 25006) has three roots, and the first diagnostic settles most cases: run SELECT pg_is_in_recovery(). If it returns t, you reached a hot standby and the fix is routing writes to the primary — most often a load balancer or pool sending mutations to a replica. If you are on the primary, hunt down default_transaction_read_only at the global, database, and role scopes, or a stray SET TRANSACTION READ ONLY in the session. Prevent recurrence by separating writer and reader endpoints, making the app read/write aware with target_session_attrs=read-write, and re-resolving the primary after failover. The free incident assistant can turn this error plus your topology into a concrete routing fix.