Fix PostgreSQL: cannot execute INSERT in a read-only transaction

The error usually looks blunt: ERROR: cannot execute INSERT in a read-only transaction. You may see the same pattern for UPDATE, DELETE, CREATE TABLE, migrations, or background jobs that suddenly stop writing. The database is telling the truth: the session you are using is not allowed to change data.

The practical problem is finding out why that session is read-only. In production PostgreSQL, this often comes from connecting to a read replica, reusing a connection after a failover or role change, setting default_transaction_read_only, or running a framework migration through the wrong database URL. The fix is not to retry the write forever. The fix is to identify whether the read-only state is session-level, transaction-level, or server-role-level, then route writes to the primary.

Fast diagnosis

Start by asking PostgreSQL what kind of session you have. The most useful checks are small and safe:

SHOW transaction_read_only;
SHOW default_transaction_read_only;
SELECT pg_is_in_recovery();
SELECT inet_server_addr(), inet_server_port();

If pg_is_in_recovery() returns true, you are connected to a standby server. PostgreSQL hot standby allows queries on a standby, but the server cannot accept writes because it is replaying changes from the primary. In a managed PostgreSQL setup, that usually means a read-replica endpoint, analytics endpoint, or stale connection string has reached write code.

If pg_is_in_recovery() is false but transaction_read_only is on, the primary is writable but your current transaction is not. That can happen when application code runs SET TRANSACTION READ ONLY, opens an ORM transaction marked read-only, or inherits a session setting from a connection pool. If default_transaction_read_only is on, every new transaction in that session starts read-only until the setting is changed.

Fix the routing first

If the session is connected to a standby, no SQL trick in that connection should be used to force a write. Close the connection and use the primary writer endpoint. This is especially important in applications that split reads and writes. A replica is a good place for reporting queries, dashboards, and low-risk analytics reads. It is the wrong place for signups, billing changes, queue acknowledgements, schema migrations, and anything that must commit new state.

Check the database URL used by the failing process, not only the web app. Migration runners, queue workers, scheduled jobs, admin scripts, and preview deployments often have separate secrets. A common incident shape is that the web app uses the writer endpoint while a background worker or migration job still has a replica URL copied from staging.

For ArmorDB users, keep this distinction close to your operational docs: use the normal connection instructions for application writes and reserve read-only paths for workloads that can tolerate replica behavior. If connection churn is part of the incident, review the PgBouncer documentation as well, because pool reuse can make a bad session setting look intermittent.

Fix session-level read-only settings

When pg_is_in_recovery() is false, inspect the application transaction path. Some ORMs and database libraries expose read-only transactions for consistency or optimization. That setting is useful for deliberate read paths, but it becomes a bug when a write happens inside the same block. End the current transaction, remove the read-only option from the write path, and make sure pooled connections are reset before reuse.

For a one-off SQL session on a primary, SET default_transaction_read_only = off; can make future transactions writable again if you have permission and the setting was changed only for that session. It does not help when the server is a standby, and it should not be used as a blanket application fix without finding where the setting came from.

Validate the fix

After changing routing or settings, rerun the same diagnostic queries from the failing process environment. The successful state for writes is usually pg_is_in_recovery() = false and transaction_read_only = off. Then perform a small representative write in a safe table or staging environment before restarting a large migration.

If the error appeared after failover, restart application pools or force reconnection so stale sessions do not continue using an old target. If the error appeared only in CI or deployment automation, print the sanitized hostname or endpoint class during the migration step so future failures are obvious without exposing credentials.

Takeaway

cannot execute INSERT in a read-only transaction is a routing and transaction-state problem, not a query syntax problem. Confirm whether you are on a standby with pg_is_in_recovery(), check the read-only settings for the current session, then send writes to the primary and keep read replicas limited to read workloads. The safest fix is precise: correct the endpoint or transaction option, reconnect pools, and validate from the exact process that failed.

Sources and further reading

• PostgreSQL hot standby documentation
• PostgreSQL client connection defaults, including default_transaction_read_only
• PostgreSQL administration functions, including pg_is_in_recovery()
• PostgreSQL libpq connection string documentation