PostgreSQL: cannot execute INSERT in a read-only transaction

What it means

A read-only transaction may read anything but change nothing. The server refused the statement named in the message (INSERT, UPDATE, DELETE, CREATE TABLE, TRUNCATE TABLE, even nextval()), and the transaction is now aborted if you were inside BEGIN.

There are two very different reasons a transaction is read-only:

The server is a standby (a read replica). Everything on it is read-only, always; no setting changes that. SELECT pg_is_in_recovery(); returns true.
Read-only mode was asked for: BEGIN READ ONLY, SET TRANSACTION READ ONLY, or the setting default_transaction_read_only = on, which makes every new transaction read-only. That setting can come from the session, the role, the database or the server’s configuration. Database clients use it to protect production connections.

This isn’t a privilege problem: the same role could write on the primary, or with the mode off. A missing privilege gives permission denied instead.

Common causes

The connection points at a replica: a reader endpoint, a replica’s host name, a load balancer that picked a standby, or a multi-host connection string without target_session_attrs=read-write.
A failover: the server you were writing to became a standby, and the application’s connections didn’t move.
default_transaction_read_only set for the role or database, often for reporting users, or left on after a maintenance window.
A client in read-only mode, such as a connection tagged as production, or a framework’s read-only transaction (@Transactional(readOnly = true), a “reader” connection).
Temporary tables in a read-only transaction: CREATE TEMP TABLE is refused too.

How to fix it

Check which case you’re in

SELECT pg_is_in_recovery();             -- true: a standby
SHOW transaction_read_only;             -- this transaction
SELECT setting, source FROM pg_settings WHERE name = 'default_transaction_read_only';

source says where the default came from: session, user (the role), database, or configuration file.

On a standby, write to the primary

Point writes at the primary’s host or the writer endpoint. With several hosts in one libpq connection string, target_session_attrs=read-write makes the client pick the one that accepts writes:

postgresql://<user>@<host1>,<host2>/<database>?target_session_attrs=read-write

SET default_transaction_read_only = off doesn’t help on a standby; BEGIN READ WRITE there fails with cannot set transaction read-write mode during recovery.

Turn read-only mode off, deliberately

For one transaction, as the first statement after BEGIN:

BEGIN;
SET TRANSACTION READ WRITE;
INSERT INTO invoices VALUES (1, 10);
COMMIT;

For the session: SET default_transaction_read_only = off;. If the role or database has it on, find out why before removing it with ALTER ROLE <role> RESET default_transaction_read_only (or ALTER DATABASE … RESET …); it may be there to protect that database.

SET TRANSACTION READ WRITE must come before the transaction’s first query; later, it fails with transaction read-write mode must be set before any query.

Reproduce it

On PostgreSQL 18.6:

SET default_transaction_read_only = on;
INSERT INTO seo_err_pg.invoices VALUES (1, 10);
UPDATE seo_err_pg.invoices SET total = 0;
TRUNCATE seo_err_pg.invoices;
SELECT nextval('seo_err_pg.legacy_id_seq');
CREATE TEMP TABLE scratch (id int);

ERROR:  cannot execute INSERT in a read-only transaction
ERROR:  cannot execute UPDATE in a read-only transaction
ERROR:  cannot execute TRUNCATE TABLE in a read-only transaction
ERROR:  cannot execute nextval() in a read-only transaction
ERROR:  cannot execute CREATE TABLE in a read-only transaction

SELECT count(*) still worked. pg_settings showed default_transaction_read_only as on with source session. Still in that session, BEGIN; SET TRANSACTION READ WRITE; let the INSERT through. With the setting reset, BEGIN READ ONLY followed by the INSERT gave the same error.

On a standby (a throwaway pair of PostgreSQL 18.6 containers, one streaming to the other), the INSERT failed the same way even after SET default_transaction_read_only = off, and:

BEGIN READ WRITE
ERROR:  cannot set transaction read-write mode during recovery

With \set VERBOSITY verbose, psql shows the code: ERROR: 25006: cannot execute INSERT in a read-only transaction. PostgreSQL 14.24 gives the same messages.

In Inlet

Connections tagged production open read-only: Inlet makes its session read-only, so the server itself refuses writes, and this is the error you see. Unlock the connection when you mean to write; unlocking lasts ten minutes. Because it’s a session setting, a pooler in transaction mode can lose it, so on those connections don’t rely on it alone.

cannot execute INSERT in a read-only transaction