ERROR: cannot execute UPDATE in a read-only transaction

Something has made this session read-only, and there are only four candidates. You are connected to a standby. The transaction was explicitly declared read only. The session inherited a read-only default from its role, its database or the configuration. Or the whole cluster is in that mode. The first is the most common by a wide margin, and it is the one nothing on your screen hints at.

Narrowing it down in the right order

Ask the server whether it is in recovery. That single question settles the standby case and costs nothing, and it should be the first thing any application logs when it sees this error. A connection string pointing at a read endpoint, a load balancer that has started distributing writes across replicas, or a failover that promoted a different node than the one you assume, all produce the same message from a server that is working perfectly.

If it is not a standby, the cause is a default that was set somewhere other than the configuration file. Defaults attached to a role or a database live in the catalog, apply at connection time, and are invisible to anybody reading the file on disk. This is the same class of problem as a setting that was edited and never reloaded, covered in which settings need a restart.

The cluster-wide form is rarer and usually deliberate: somebody put the database into read-only mode during an incident or a migration and did not put it back.

What the message does and does not mean

Every writing statement produces the same error with its own verb, so an insert, a delete or a schema change on a standby all read the same way once you know the shape.

Temporary tables remain writable. The documented rule is that a read-only transaction cannot alter non-temporary tables, so a session can still create and populate its own scratch space, which is occasionally useful and occasionally confusing when a report half works.

A full disk does not cause this. Neither does a permission problem, which produces a permission error naming the object. If you see this one, the session’s mode is the cause and there is no third explanation.

On a pair where writes are supposed to reach only one node, the durable fix is routing that cannot send a write to a replica, plus configuration the two nodes agree on, which is what configuration drift between a primary and its standby is about. If the reason for the read endpoint was to offload reporting, the difference between the two replication styles and what each can serve is in physical versus logical replication.