How to Fix PostgreSQL 'cannot execute CREATE TABLE in a read-only transaction' Error

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 5–15 mins

TL;DR

• What broke: A CREATE TABLE (or any DDL) fired inside a transaction marked READ ONLY — either explicitly via SET TRANSACTION READ ONLY, a session-level GUC (default_transaction_read_only = on), or the connection was routed to a hot standby replica that physically cannot accept writes.
• How to fix it: Identify the read-only boundary (session, transaction, or server role), remove or bypass it, and ensure DDL runs on the primary with an explicit READ WRITE transaction or outside a constrained transaction block.
• Shortcut: Use our Client-Side Sandbox above to paste your migration script — it will auto-detect the read-only context and refactor the transaction wrapper for you.

The Incident

ERROR:  cannot execute CREATE TABLE in a read-only transaction
CONTEXT:  SQL function "migrate_schema" during inlining

Postgres enforces a hard wall: no DDL, no DML writes, no sequence increments are permitted inside a read-only transaction. The moment the executor hits CREATE TABLE, it throws and rolls back the entire transaction block. If this fires mid-migration (Flyway, Liquibase, Alembic, Django), your schema is left in a partial state — the migration tool may mark the version as failed, requiring manual repair of the migration history table before the next deploy can proceed.

The Attack Vector / Blast Radius

This is not a security exploit in the traditional sense — but the blast radius in a production pipeline is severe:

1. Replica misrouting (most common prod incident): A connection pooler (PgBouncer, RDS Proxy, HAProxy) routes the migration runner to a read replica instead of the primary. Every DDL fails. The migration tool may retry in a loop, hammering the replica and spiking its replication lag.

2. default_transaction_read_only = on at the role or database level: A DBA hardened a reporting role or an entire database cluster with ALTER ROLE reporter SET default_transaction_read_only = on. If your migration runner inherits this role or connects to that DB, every transaction opens read-only silently. No warning. Just the error at DDL time.

3. Explicit SET TRANSACTION READ ONLY in a wrapper: A shared DB utility function or ORM hook sets SET TRANSACTION READ ONLY for "safe" read operations and the DDL accidentally executes inside that context.

4. RDS/Aurora Multi-AZ failover window: During failover, the old primary becomes a replica. Connections that survive the failover are now on a read-only node. Migrations queued during this window fail with exactly this error.

Cascading risk: Liquibase/Flyway lock the DATABASECHANGELOGLOCK table at migration start. If the transaction fails mid-flight, the lock row may remain set to TRUE, blocking all future migration runs until manually cleared.

How to Fix It

Basic Fix — Verify and Override Transaction Mode

- -- Implicit read-only context inherited from role or pooler
- BEGIN;
- CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
- COMMIT;

+ -- Explicitly assert READ WRITE before DDL
+ BEGIN;
+ SET LOCAL default_transaction_read_only = off;  -- override session GUC for this txn
+ SET TRANSACTION READ WRITE;                      -- belt-and-suspenders
+ CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
+ COMMIT;

Note: SET LOCAL only survives the current transaction block. This is intentional — do not use SET (session-level) in a shared connection pool.

Verify You Are on the Primary

- -- Blindly running migration against pool VIP
- psql -h db-pool.internal -U migrator -d appdb -f migrate.sql

+ -- Confirm primary before running DDL
+ psql -h db-pool.internal -U migrator -d appdb \
+   -c "SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA - ABORT' ELSE 'PRIMARY - OK' END AS node_role;"
+ # Only proceed if output is 'PRIMARY - OK'
+ psql -h db-pool.internal -U migrator -d appdb -f migrate.sql

Enterprise Best Practice — Role Hygiene + Connection Routing

- -- Migration runner uses shared 'app_user' role with read-only default
- ALTER ROLE app_user SET default_transaction_read_only = on;

+ -- Dedicated migration role, never read-only, explicitly scoped
+ CREATE ROLE migrator NOINHERIT LOGIN PASSWORD '...';
+ GRANT CREATE, CONNECT ON DATABASE appdb TO migrator;
+ -- DO NOT set default_transaction_read_only on this role
+ -- In PgBouncer: route migrator to primary_dsn ONLY
+ ; pgbouncer.ini
+ [databases]
+ appdb_migrate = host=primary.db.internal port=5432 dbname=appdb auth_user=migrator
+ appdb         = host=pool.db.internal   port=5432 dbname=appdb

In RDS/Aurora, always resolve the cluster writer endpoint (cluster.cluster-xxxx.rds.amazonaws.com) — never the reader endpoint — for migration runners. Enforce this in your Terraform:

- db_host = aws_rds_cluster.main.reader_endpoint
+ db_host = aws_rds_cluster.main.endpoint  # writer/primary endpoint

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

Prevention in CI/CD

1. Pre-migration node role check (shell gate):

#!/bin/bash
ROLE=$(psql "$DATABASE_URL" -tAc "SELECT pg_is_in_recovery();")
if [ "$ROLE" = "t" ]; then
  echo "FATAL: Connected to a replica. Aborting migration."
  exit 1
fi

Drop this as a pre-step in your GitHub Actions / GitLab CI job before Flyway or Alembic runs.

2. Checkov / kics policy for Terraform RDS:

Ensure your Terraform never wires a migration runner to a reader endpoint. Write a custom Checkov check or use OPA:

# OPA: deny migration jobs targeting reader endpoints
deny[msg] {
  input.resource.type == "kubernetes_deployment"
  input.resource.spec.template.spec.containers[_].env[e]
  e.name == "DATABASE_URL"
  contains(e.value, "reader")
  msg := "Migration workload must not target a reader/replica endpoint."
}

3. Liquibase / Flyway — fail fast on read-only:

Flyway 9+: set flyway.connectRetries=0 and flyway.validateOnMigrate=true. Do not let it retry silently into a replica loop.

4. Audit role defaults in your IaC:

# Detect any role with read-only default in your DB
psql -c "SELECT rolname, rolconfig FROM pg_roles WHERE 'default_transaction_read_only=on' = ANY(rolconfig);"

Run this as a nightly CI job and alert if your migration role appears in the output.