Postgres Upgrade from 9.5 to 12 cannot execute UPDATE in a read-only transaction

Question

Since moving from 9.5 to 12 we are seeing alot of errors in the database log. The query that is being run is a basic update statement which changes the date of when a user is logged in.

2021-03-03 09:23:43.235 CET [18820] ERROR: cannot execute UPDATE in a read-only transaction

We did not see these errors when we were on an older version of postgres.

I have checked the default_transaction_read_only and thats set to off which is correct.

Why would I be seeing this now?

Answer 1

Please verify that you are not in a read-only transaction:

SHOW default_transaction_read_only;
SHOW transaction_read_only;

If the first is off and the second is on, you must have explicitly started a read-only transaction with

BEGIN READ ONLY;

If you are not inside a read-only transaction, the only explanation is that the database is in recovery mode.

Perhaps you connected to a streaming replication standby server.

To verify, see if the following returns “true”:

SELECT pg_is_in_recovery();