ERROR: cannot execute CREATE TABLE in a read-only transaction

Normally the most plausible reasons for this kind of error are :

• trying create statements on a read-only replica (the entire instance is read-only).

• <username> has default_transaction_read_only set to ON

• the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
\c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>. And it wouldn't work if any of the reasons above was directly applicable.

One possibility that would technically explain this would be that default_transaction_read_only would be ON in the postgresql.conf file, and set to OFF for the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with \c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

Reached out to pgexercises.com and they were able to help me.

I ran these commands(separately):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
\q

Then I dropped the database from the terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage! Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don't know if the same effect would happen if I was on a dedicated server.