Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL refuses writes: cannot execute CREATE TABLE in a read-only transaction

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

Short 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.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW in_hot_standby returns onYou are connected to a standby (replica)Fix
    2. 2SHOW in_hot_standby returns off, but SHOW default_transaction_read_only returns ondefault_transaction_read_only is switched onFix
    3. 3Both checks above say off, yet the error appears only inside one transaction blockYour code opened a READ ONLY transactionFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.