Fix "cannot execute in a read-only transaction" in Django

Problem statement

Reads work, but every write fails:

django.db.utils.InternalError: cannot execute INSERT in a read-only transaction
psycopg.errors.ReadOnlySqlTransaction: cannot execute UPDATE in a read-only transaction

Logins fail (the last_login update is a write), forms cannot be saved, and sessions stored in the database stop working. PostgreSQL is refusing writes on this connection because the server is a read-only standby, or the session or database is configured as read-only. In Django deployments, that usually means the application is talking to the wrong server: a replica, a reader endpoint, or a former primary after a failover. This guide finds out which and fixes the connection.

1. From Django's own connection, run SELECT pg_is_in_recovery(), current_setting('transaction_read_only'), inet_server_addr();.
2. pg_is_in_recovery = true: you are connected to a standby. Point default at the primary or writer endpoint.
3. After a failover, restart app processes (or rely on CONN_HEALTH_CHECKS and a short CONN_MAX_AGE) so connections to the old primary are replaced.
4. With several hosts in the connection string, add target_session_attrs=read-write.
5. If you use a database router, make sure db_for_write always returns the primary alias.
6. If the server is not in recovery, look for default_transaction_read_only on the database or role, or a provider putting the database into read-only mode (for example when storage is full).

Step-by-step solution

1. Ask the server Django is connected to

python manage.py shell -c "
from django.db import connections
for alias in connections:
    with connections[alias].cursor() as c:
        c.execute(\"SELECT pg_is_in_recovery(), current_setting('transaction_read_only'), inet_server_addr(), current_database()\")
        print(alias, c.fetchone())
"

Run it in the same environment as the failing process (web or worker). The answer tells you which branch to follow:

2. Connected to a replica: fix the endpoint

Managed services give separate endpoints for writing and reading:

• Amazon Aurora: the cluster (writer) endpoint versus the reader endpoint (-ro- in the name). Using the reader endpoint for default makes all writes fail.
• RDS read replicas, Cloud SQL replicas, Azure read replicas: each replica has its own host name; default must use the primary's.
• Self-managed: a load balancer or DNS name that round-robins across primary and replicas sends some connections to standbys, producing intermittent write failures.

Set default to the writer endpoint and keep replicas in separate aliases:

DATABASES = {
    "default": {**BASE_DB, "HOST": os.environ["DB_WRITER_HOST"]},
    "replica": {**BASE_DB, "HOST": os.environ["DB_READER_HOST"]},
}

3. After a failover: replace stale connections

When a managed database fails over, the writer endpoint's DNS moves to the new primary, and the old primary may come back as a standby. Connections opened before the failover (persistent connections with CONN_MAX_AGE, pooler connections, long-running workers) can still point at the old server. Writes then fail until those connections are replaced.

DATABASES["default"].update({
    "CONN_MAX_AGE": 60,
    "CONN_HEALTH_CHECKS": True,
})

CONN_HEALTH_CHECKS detects dead connections, but a connection to a server that is alive as a standby is healthy from its point of view. After a failover, restart web and worker processes, or restart PgBouncer so it reconnects. Keep CONN_MAX_AGE moderate so stale connections age out on their own. Also check that the application's DNS resolution is not cached for longer than the endpoint's TTL.

4. Multiple hosts: target_session_attrs

With self-managed replication, libpq can try several hosts and pick the writable one:

DATABASES["default"]["HOST"] = "db1.internal,db2.internal"
DATABASES["default"]["PORT"] = "5432,5432"
DATABASES["default"]["OPTIONS"] = {"target_session_attrs": "read-write"}

libpq connects to each host in turn and keeps the first one that accepts writes. This works with psycopg because it passes the parameters to libpq. After a failover, new connections find the new primary; existing ones still need replacing as in step 3.

5. Check database routers

With read replicas, a router decides which alias each query uses:

class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        return "replica"

    def db_for_write(self, model, **hints):
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        return True

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "default"

Errors appear when db_for_write returns a replica for some models, when code writes to an object loaded with .using("replica") (saves go to the instance's database unless routed otherwise), or when explicit .using("replica") appears on update(), delete(), or create(). Search the code for using("replica") on write paths.

6. The server is primary but read-only

SELECT current_setting('default_transaction_read_only');
SELECT datname, datconfig FROM pg_db_role_setting JOIN pg_database ON setdatabase = oid;
SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL;

Someone may have set ALTER DATABASE myapp SET default_transaction_read_only = on (or on the role) for maintenance and not reverted it. Revert with ALTER DATABASE myapp RESET default_transaction_read_only; and reconnect.

Some managed services switch a server to read-only automatically when storage is nearly full, to protect it. Check the provider's console for storage alerts and increase storage or free space; the server returns to read-write afterwards.

Verification check:

python manage.py shell -c "
from django.db import connection
with connection.cursor() as c:
    c.execute('SELECT pg_is_in_recovery()'); print('in recovery:', c.fetchone()[0])
"
python manage.py shell -c "from django.contrib.sessions.models import Session; print(Session.objects.count())"

Then perform a write through the site (log in, save a form).

Explanation

PostgreSQL standbys replay changes from the primary and accept only read queries; any write fails with a read-only transaction error. Primaries can also be forced into read-only mode by configuration. Django has no notion of which server is writable: it sends writes to the alias the router chooses, over whatever connection that alias holds. So the fix is always to make the default (writer) alias resolve to, and stay connected to, the current primary.

Edge cases / notes

Replication lag after fixing writes

Once writes go to the primary and reads to a replica, users may not see their own changes immediately. Route reads that must be fresh (right after a write) to the primary, for example for the rest of the request.

PgBouncer

PgBouncer does not know which server is primary. Point its database entry at the writer endpoint and reconnect (RECONNECT in the admin console, or a restart) after failovers.

When to automate this

Add a start-up or health check that verifies the default connection is writable (pg_is_in_recovery() is false) and fails loudly otherwise. Include a restart of app processes and poolers in your failover runbook.

Next steps

• Set up routing correctly with Route Django Reads to PostgreSQL Read Replicas.
• Use provider endpoints correctly with Connect Django to Managed PostgreSQL Securely.
• For other connection failures, see Fix Database Connection Errors in Django Production.
• Tune connection lifetime with Configure Persistent Database Connections in Django.

FAQ

What does "cannot execute INSERT in a read-only transaction" mean?

The connection is to a server or session that does not allow writes, usually a replica or standby, or a database configured as read-only.

Why did writes fail right after a database failover?

Existing connections still point to the old primary, which is now a standby. Restart app processes and poolers, and keep CONN_MAX_AGE moderate.

How do I make Django connect to the writable server automatically?

List all hosts and set target_session_attrs=read-write in OPTIONS, or use your provider's writer endpoint.

Can a database router cause read-only errors?

Yes, if db_for_write returns a replica or code explicitly writes with .using("replica").