PostgreSQL’s “cannot execute CREATE TABLE in a read-only transaction” error is now tripping migrations for many teams, leaving schemas half-applied and migration history tables locked. The failure usually appears when a write operation lands on a replica instead of the primary, and it stalls any tool that relies on schema changes—Flyway, Liquibase, Django ORM, and similar.

Why the error shows up

PostgreSQL disables all writes, schema alterations, and sequence updates when a transaction is marked read-only. The most common ways a migration ends up in that state are:

  • Connection poolers (e.g., PgBouncer) that inadvertently send the migration connection to a read replica.
  • Roles that have the default_transaction_read_only parameter set to on by default.
  • Cloud endpoints that expose separate reader and writer URLs; using the reader URL (as is common with AWS RDS or Aurora) routes writes to a replica.

When any of these conditions applies, the migration process can create tables, add columns, or update sequences only to hit a wall, leaving the database in a partially migrated state.

Immediate fix inside the transaction

If you already hit the error, you can override the read-only flag for the current transaction without affecting the rest of the session—crucial when a connection pool reuses the same session for other work.

BEGIN;
SET LOCAL default_transaction_read_only = off;
SET TRANSACTION READ WRITE;
CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
COMMIT;

SET LOCAL changes the setting only for the duration of the transaction. Using plain SET would persist the change for the whole session, which can break other operations that legitimately need a read-only default.

Preventive measures

1. Verify you’re on the primary node

Add a quick check before any migration runs:

SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA' ELSE 'PRIMARY' END;

If the result is REPLICA, abort the migration. The function pg_is_in_recovery() returns true on a standby server, guaranteeing you’re not trying to write to a read-only copy.

2. Use dedicated migration roles

Create a role whose default transaction mode is write-enabled, and grant it only the privileges it needs:

  • CREATE on the target database.
  • CONNECT to allow the migration tool to open a session.

Avoid giving this role the default_transaction_read_only = on attribute that is sometimes set for general-purpose users.

3. Point infrastructure at the writer endpoint

In IaC scripts (Terraform, CloudFormation, etc.), configure migration runners to use the cluster’s writer endpoint, not the reader endpoint. The writer endpoint resolves to the primary node, while the reader endpoint resolves to a replica that will reject writes.

4. Add a CI/CD gate

Insert a shell step in your pipeline (GitHub Actions, GitLab CI, etc.) that runs the pg_is_in_recovery() query. If it returns true, exit the job with a non-zero status to stop the deployment early.

if psql $DATABASE_URL -c "SELECT pg_is_in_recovery()" | grep -q t; then
  echo "Connected to replica – aborting migration"
  exit 1
fi

5. Tune migration-tool retries

Tools like Flyway often retry connections automatically. For Flyway 9+ set flyway.connectRetries=0. This prevents the tool from repeatedly hitting a replica, which can otherwise increase replication lag and waste resources.

What to watch next

  • Replication lag metrics: A growing lag may indicate that a migration inadvertently targeted a replica, causing write attempts to be queued.
  • Connection-pooler routing rules: Ensure that pooler configurations explicitly route migration traffic to the primary host.
  • Role defaults after upgrades: Database upgrades sometimes reset role parameters; re-audit default_transaction_read_only after major version changes.

The bottom line: a read-only transaction error is rarely a PostgreSQL bug; it’s a symptom of traffic being sent to the wrong node or a role being misconfigured. By checking node role, using dedicated migration accounts, and hardening your CI/CD pipeline, you can keep migrations running smoothly and avoid half-applied schemas that cripple downstream development.