ERROR

How to Fix PostgreSQL 'Cannot Execute DDL in a Read-Only Transaction' (2025)

Quick Fix Summary

TL;DR

Connect to a writable primary node and ensure the session or database is not in read-only mode.

PostgreSQL is preventing Data Definition Language (DDL) commands like CREATE, ALTER, or DROP because the current transaction or database connection is explicitly set to read-only. This is a protective mechanism, often seen in replication setups or controlled environments.

Diagnosis & Causes

Connected to a read-only hot standby replica.

Session variable `default_transaction_read_only` is set to ON.

Database property `is_read_only` is set to true.

User lacks CONNECT or CREATE privilege on the database.

Transaction block began with `START TRANSACTION READ ONLY`.

Recovery Steps

Step 1: Verify Connection Target and Database State

First, confirm you are connected to a writable primary server and check the read-only status of your session and database.

sql

SELECT pg_is_in_recovery(); -- Returns 'f' for primary/writable
SHOW default_transaction_read_only; -- Should return 'off'
SELECT datname, datallowconn, datistemplate FROM pg_database WHERE datname = current_database(); -- Ensure datallowconn is true

Step 2: Switch to a Writable Primary Connection

If connected to a replica (`pg_is_in_recovery()` returns 't'), you must connect to the primary node. Update your application's connection string or use a tool like `psql` to connect directly to the primary host.

bash

# Example: Connect via psql to the primary node
psql -h <primary_host_ip> -U <username> -d <database_name>

Step 3: Disable Read-Only Mode for the Session

If the session is read-only but the database is writable, explicitly set the transaction state to read/write. This must be done outside of any transaction block.

sql

SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE;
-- Or for the current transaction (if not already started):
BEGIN READ WRITE;

Step 4: Check and Modify User/Database Permissions

Ensure the connected user has the necessary privileges (like CREATEDB, CREATE) on the database to execute DDL.

sql

-- Check user privileges
\du <username>
-- Grant CONNECT and CREATE if needed (run as superuser)
GRANT CONNECT, CREATE ON DATABASE <dbname> TO <username>;

Step 5: Alter Database Read-Only Property (Superuser Required)

If the database itself was set to read-only (e.g., `ALTER DATABASE mydb SET default_transaction_read_only = on;`), a superuser must reset it.

sql

ALTER DATABASE <database_name> RESET default_transaction_read_only;
-- Then reconnect to that database for changes to take effect.

Step 6: Configure Connection Pooler/Router (Advanced)

In cloud/managed services (RDS, Cloud SQL) or with poolers like PgBouncer, ensure DDL connections are routed to the primary. Use different ports or service names for writer endpoints.

yaml

# Example: RDS PostgreSQL might use a different endpoint for writer
# Application config for DDL tasks:
host: mydb.cluster-xyz.<region>.rds.amazonaws.com (Writer Endpoint)

Architect's Pro Tip

"In streaming replication, perform DDL on the primary only. Use `pg_is_in_recovery()` in your deployment scripts to abort DDL execution automatically on replicas."

Frequently Asked Questions

Can I make a hot standby replica writable to run DDL?

No. A hot standby's primary purpose is read scalability and high availability. Making it writable would break replication. All DDL must originate on the primary server.

Why did my session suddenly become read-only?

Common reasons include: connection pooler routing you to a replica, a failover event you haven't reconnected from, or a database/role ALTER command that changed the default setting.

Does `SET TRANSACTION READ WRITE` work inside a transaction?

No. The transaction access mode (READ ONLY/READ WRITE) cannot be changed after `BEGIN`. You must commit/rollback and start a new transaction with `BEGIN READ WRITE`.