PostgreSQL refuses writes: cannot execute CREATE TABLE in a read-only transaction
ERROR: cannot execute CREATE TABLE in a read-only transactionShort answer
The transaction is read-only. Three things cause it: you are connected to a hot standby, default_transaction_read_only is on for your role, database or server, or the code started a READ ONLY transaction. Run SHOW in_hot_standby and SHOW transaction_read_only to tell which.
Fix it fast
1Find out which cause you have
Check whether this server is a standby and whether the session is read-only. Run both.
SHOW in_hot_standby; SHOW transaction_read_only; SHOW default_transaction_read_only;2Match what you see, then jump to its fix
- 1
SHOW in_hot_standby returns onYou are connected to a standby (replica)Fix - 2
SHOW in_hot_standby returns off, but SHOW default_transaction_read_only returns ondefault_transaction_read_only is switched onFix - 3
Both checks above say off, yet the error appears only inside one transaction blockYour code opened a READ ONLY transactionFix
- 1
What does the error look like?
ERROR: cannot execute CREATE TABLE in a read-only transaction
Cause 1 of 3
You are connected to a standby (replica)
1Check
On a hot standby, connections are strictly read-only. The manual lists INSERT, UPDATE, DELETE, COPY FROM and all CREATE, DROP and ALTER among the refused commands, and transaction_read_only is always true and cannot be changed.
SHOW in_hot_standby;
SELECT pg_is_in_recovery();2Fix
Send writes to the primary: fix the host in your connection string, or the load balancer rule that picked the replica. Only if the primary is gone and you decided to fail over, promote the standby.
-- run on the standby, as superuser, only for a deliberate failover
SELECT pg_promote();3Verify
On the server you now use, both checks say it is not a standby.
SELECT pg_is_in_recovery();Confirmed by postgresql.org
Cause 2 of 3
default_transaction_read_only is switched on
1Check
This parameter sets the read-only status of every new transaction. It can be set in postgresql.conf, per database, per role, or in the connection options. Find where.
SELECT name, setting, source
FROM pg_settings
WHERE name = 'default_transaction_read_only';2Fix
Turn it off at the level where it is set. For a database, the database owner or a superuser can reset it. If the value comes from postgresql.conf, edit it there and reload.
ALTER DATABASE mydb RESET default_transaction_read_only;
-- or for this session only
SET default_transaction_read_only = off;3Verify
Open a new session and run the write.
SHOW default_transaction_read_only;Confirmed by postgresql.org
Cause 3 of 3
Your code opened a READ ONLY transaction
1Check
A READ ONLY transaction refuses INSERT, UPDATE, DELETE, MERGE, all CREATE, ALTER and DROP, COMMENT, GRANT, REVOKE and TRUNCATE. Look for BEGIN READ ONLY, SET TRANSACTION READ ONLY, or a driver or ORM option such as a read-only flag.
grep -rn -i "read only\|readonly\|read_only" src/2Fix
Remove the read-only mode from that transaction, or start it as READ WRITE. In SQL, set the mode before the first query of the transaction.
BEGIN TRANSACTION READ WRITE;
CREATE TABLE audit_log (id bigint);
COMMIT;3Verify
The block commits without the error.
SHOW transaction_read_only;Confirmed by postgresql.org
How do I stop it coming back?
- Use separate connection strings for the primary and for replicas, and name them so nobody mixes them up.
- Run migrations only against the primary.
- Keep read-only transaction options in code limited to code paths that really only read.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix