Cannot execute INSERT in a read-only transaction: what put your database in read-only mode, and how to get writes back

Your first move when Postgres answers cannot execute INSERT in a read-only transaction is 2 queries through your app’s exact connection string, before any change: SELECT pg_is_in_recovery() and SHOW default_transaction_read_only. The first says whether you reached a replica or a database in recovery. The second says whether a setting, or a provider reacting to a full disk, switched writes off.

Cannot execute INSERT in a read-only transaction: what to do right now

A read-only transaction error from Postgres is handled in 6 steps: scope which writes fail, run the two queries through the app’s own connection, check the provider’s dashboard, save the evidence, pause writes with a clear notice, and avoid the three moves that make it worse.

1. 01 Scope it. Is every write failing, or only some: one service, one background job, only the SQL editor? Write down which writes fail and the time of the first error in the logs.
2. 02 If the log shows cannot execute INSERT in a read-only transaction, run the two queries through the same host, port and user the app uses. pg_is_in_recovery() true means a replica or a database still in recovery. default_transaction_read_only on means a setting or the provider turned writes off. False and off together point at the pooler or the code. Behind a transaction pooler, also run them once on a direct connection on port 5432, where the Supabase pooler guide names either query as the check that the database itself is not in read-only mode. Then run SHOW default_transaction_read_only through the pooler a few times: an answer that changes between runs fits the explanation Supabase gives, that the error only occurs on a contaminated backend connection while other connections in the same pool "may still be in the default read-write state". Record each answer next to the host it came from.
3. 03 Open the provider dashboard. Look for disk or database size near its limit, a read-only notice, an over-quota or usage notice, and any failover or maintenance event in the last day. On Supabase, database size is on the Database Reports page and disk size on the Database Settings page. Note what each page shows and when you looked.
4. 04 Save the evidence: the error line with its timestamp, the host name from the connection string (never the password), and the two query results. Keep them in one incident note.
5. 05 Keep users from losing work. A plain "saving is paused" notice on write paths beats a spinner, and background jobs that write should pause rather than burn their retries. List the jobs you paused, so the same ones restart later.
6. 06 Do not make it worse. Never try to force read-write on a standby: a hot standby refuses SET TRANSACTION READ WRITE, and the attempt only adds a second error, cannot set transaction read-write mode during recovery. Do not delete rows in a hurry to free space. Do not restore a backup over production. Write down anything already tried, including by teammates.

The error refuses new writes; it does not remove rows already stored, so a restore solves nothing here, and whether to restore into a copy or over production is a decision for a different incident, one where data is actually gone. The first query is one of PostgreSQL’s recovery information functions, where pg_is_in_recovery() “Returns true if recovery is still in progress”.

On Supabase, direct connections “are on IPv6, or on IPv4 if the project has the IPv4 add-on”, which is a paid add-on. From an IPv4-only network without it, your machine cannot open that connection, so run the two queries in the dashboard’s SQL Editor instead. The editor may connect differently from your app, which is why the second step insists on the app’s own host, port and user wherever you can reach them.

These checks sit among the database checks in the data consistency checklist for SaaS.

Why this happens

Postgres raises SQLSTATE 25006, read_only_sql_transaction, when a writing statement runs in a read-only transaction. Five things make it read-only: a replica or a database in recovery; a provider’s read-only mode after a full disk, a size limit or an exceeded quota; the read-only default setting; a contaminated pooler connection; or code or a SQL tool that opened it read-only.

The code and its condition name are listed in PostgreSQL’s error codes appendix under class 25, invalid transaction state. The causes below are described from each vendor’s documentation, not from a test I ran. The table puts them in the order I’d check them on a managed host; no source gives how often each one happens.

Cannot execute UPDATE in a read-only transaction, DELETE, CREATE TABLE: one error, many verbs

The verb in the message is whichever statement ran first. Postgres fills the refused command’s name into the message, so the same five causes can print cannot execute DELETE in a read-only transaction, or name CREATE TABLE or CREATE EXTENSION. Under SET TRANSACTION, the docs list what a read-only transaction disallows: INSERT, UPDATE, DELETE, MERGE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. A hot standby also refuses sequence updates, nextval() and setval().

Which verb you see tells you which code path reached the database first, nothing more. Reads keep working, which is why the app looks half alive: pages load and every save fails.

A replica, a reader endpoint, or a failover that moved the primary

PostgreSQL’s hot standby chapter defines hot standby as the ability to connect and run read-only queries while the server is in archive recovery or standby mode, and no session setting can lift that on a standby (the table’s first row).

One route there is a connection string that names a read endpoint. AWS’s advice for Aurora’s reader endpoint is conditional: “For clusters where high availability is important”, use the cluster endpoint for read/write or general-purpose connections and the reader endpoint for read-only connections. Neon’s read replicas run on a read-only compute instance. Supabase’s read replicas are read-only databases, each with its own database endpoint, which you pick with the Source dropdown on the Connect panel. Fly Postgres, in its unmanaged form, lets you add read-only replicas in other regions; Fly’s docs now say that unmanaged version is no longer maintained.

An ORM’s replication config can also send a write to the read pool; the fix section shows how Prisma and Sequelize split the two. A failover gets you there too: another node is promoted, and a hard-coded instance address or a stale DNS answer still points at the old primary. Aurora’s writer and reader endpoints, unlike its instance endpoints, automatically change which DB instance they connect to if one becomes unavailable, which is the reason to connect through them. Errors that come and go behind a load balancer can mean some connections land on a replica; that is my reading, not documented behavior.

The provider switched the database to read-only: full disk or a plan size limit

Supabase’s read-only mode docs name this error outright: in read-only mode, “clients will encounter errors such as cannot execute INSERT in a read-only transaction” (checked 2026-09-28). The triggers depend on the plan. Under “Free Plan behavior”, a Free Plan project enters read-only mode when its database size exceeds 500 MB, and the page notes that this is the size of the Postgres data, not the 1 GB disk. Under “Paid plan behavior”, a project enters read-only mode if it reaches 95% disk utilization and has exhausted its modification quota, which on that plan is four modifications within a rolling 24-hour window. The same section’s example is a large import that needs several disk expansions: “for example, uploading more than 1.5x the current size of your database storage will put your database into read-only mode.” Supabase says regular read-write operation is “automatically re-enabled once usage is below 95% of the disk size”.

My list of places to look first when a small app’s disk fills: an events or logs table nobody prunes, large JSON blobs, files stored in rows, and the write-ahead log during a bulk import, which Supabase names as one of the primary components of disk usage.

On Supabase there is a third way in. Under Supabase’s Fair Use restrictions, service restrictions may apply to an organization that continually exceeds the Free Plan quota, or the Pro Plan quota with the spend cap enabled, and the billing FAQ says those restrictions could mean switching databases to read-only mode (checked 2026-09-28). Pausing projects is a separate item on that list, and a paused project is a different incident, resumed from the dashboard with Resume project.

A provider’s own tooling can leave the switch on, too. PagerTree’s own postmortem says Fly.io’s migrate-to-v2 command “first puts the database in a read-only state” and, when the timeout occurs, “fails to remember to put the database back in a writable state”; their error tracker showed the INSERT form of this error, and the app functionally failed for approximately 45 minutes on July 30, 2023. A session-level SET default_transaction_read_only TO off looked like the fix, but they “would later learn this only set the option for the current connection”; alter database ... set default_transaction_read_only=off was the authoritative fix, and they now run a write test every minute in their monitoring. In my reading, the lesson is that the error names the transaction, not the cause: here the cause was a default set on the database (the read-only default row in the table), and a SET in one session changed only that session.

How to fix it: the fix for each of the five causes, and the canary write

The fix for a read-only transaction error follows its cause: point writes at the primary endpoint, clear the provider’s read-only mode the way its docs describe, reset the read-only default, remove session-level SET from pooled clients, or fix the call site. Then prove it with a canary write through the app’s own connection.

For a replica or a failover, the table row is the fix; the separately named variables are what keep a later deploy from mixing the two hosts up again.

For provider read-only mode, follow the host’s own docs. On a Supabase Free Plan project, the options the page lists under “Free Plan behavior” are to upgrade to the Pro Plan, to disable the Spend Cap if you want a Pro instance to auto-scale beyond the 8 GB disk size limit, or to disable read-only mode and reduce the database size. On a paid plan, the page’s advice for a planned large import is to increase the disk size manually on the Database Settings page; disk modifications are limited to four within a rolling 24-hour window, and once that limit is reached no further adjustment can be made until the window permits it. Disabling read-only mode happens in the SQL Editor, in this order: set session characteristics as transaction read write;, delete data, then, in Supabase’s words, “consider running a vacuum”, then set default_transaction_read_only = 'off';. Free space deliberately: find the largest tables with pg_total_relation_size(), then archive or prune the one that grows without bound, never a core table in a hurry. Supabase warns that deleting data may not immediately reduce the reported disk usage. Confirm from a new connection, not the SQL Editor session that ran the override, since that session’s settings belong to it alone.

An over-quota restriction lifts through the quota, not the database: once the quota refills at the start of the next billing cycle, or at once by upgrading to Pro from the Free Plan or by disabling the spend cap on a Pro Plan that has it enabled. When egress is the quota that ran over, finding what burns it is covered under the Supabase egress limit.

For the read-only default, find where it lives. pg_db_role_setting holds per-role and per-database settings: setdatabase is zero if a setting is not database-specific, and setrole is zero if it is not role-specific. If that catalog shows nothing, remember the default can also be set in the configuration file. After the reset, check from a new connection, because a database default takes effect when a new session is started. Before closing the incident, find out who set default_transaction_read_only and why.

For a contaminated pooler connection, PgBouncer’s feature table marks SET/RESET as “Never” supported under transaction pooling. The mechanism is in Supabase’s guide to connection contamination: in transaction pooling mode, “session state can persist unless explicitly reset”, a session-level setting that a client, script or automated task changes “sticks” to the backend connection, and the next client to use it “inherits that exact state”. Search scripts and reporting tools that share the pooler for SET default_transaction_read_only = on and SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, and replace them with the forms Supabase lists as safe because they only affect the current transaction: BEGIN TRANSACTION READ ONLY, or BEGIN then SET TRANSACTION READ ONLY. Scripts that need a special session state belong on the direct connection on port 5432, under the same IPv6-or-add-on condition as above; for an IPv4-only network without the add-on, Supabase’s connection guide says “use session mode instead”. Pool errors of the other kind, such as too many connections, are in every connection pool exhausted error and its fix, and pooling as a standing control is a separate topic: connection pooling for Postgres.

For code, look for an ORM or framework transaction opened read-only, or a replica client used for a write. With Prisma’s read replicas extension, write operations and $transaction queries run against the primary, and $replica() sends a query to a replica explicitly, so any write inside $replica() is the line to find. That extension is Prisma ORM 7; Prisma ORM 8 lists read replicas as not available and says to create one client per database, so there the line to find is a write sent through the replica’s client. Sequelize’s read replication uses the write pool for each write, so check which host the write entry names.

A SQL tool can hold the connection read-only as well. DBeaver’s connection settings can make a connection read-only, which makes them the first thing to check on a DBeaver read only transaction error. In IntelliJ IDEA, when the database tools report cannot execute UPDATE in a read-only transaction, first check whether the Read-only checkbox on the data source’s Options tab is selected; IntelliJ IDEA’s read-only mode page describes the setting, not this message. A read-only connection setting is not stated in pgAdmin’s docs (checked 2026-09-28), so on a pgAdmin read only transaction error, check the host the server entry uses (the replica row) and the database and role settings (the read-only default row) first. Then fix the call site; grouping related writes properly is a separate question: how to wrap multiple writes in a transaction.

Prove the fix with a canary write. Creating the scratch table is itself a write (CREATE is on the disallowed list above), so create it once, after the first checks come back clean, then run the insert and delete through the app’s connection string:

SELECT pg_is_in_recovery();             -- false on a primary
SHOW default_transaction_read_only;     -- off when writes are allowed by default
SHOW in_hot_standby;                    -- servers from version 14
SELECT setdatabase, setrole, setconfig
  FROM pg_db_role_setting;              -- where a default was set
-- once, after the checks above come back clean:
-- CREATE TABLE canary_write (checked_at timestamptz);
INSERT INTO canary_write VALUES (now());
DELETE FROM canary_write;

Then exercise one real write path end to end, such as a sign-up, and save the output with the time. If the app reaches its database only through a client library, that real write path is your canary. A passing canary shows that this connection accepts writes now. It does not show what else a failover touched, such as jobs that failed while the database was read-only.

How to stop it happening again

Five standing controls, each aimed at a cause above:

1. 01 An alert on disk or database size at about 80 percent of the limit (my working rule, not a provider threshold), tested once by lowering the threshold so the alert fires and someone sees it.
2. 02 The primary and the replica in separately named environment variables, so a write client never picks up the read host.
3. 03 Session-level SET banned from anything that connects through a transaction pooler, and the pool configuration written down.
4. 04 A retention job for the tables that grow without bound, such as events and logs.
5. 05 Seed and migration scripts that state which host they target, so staging work never lands on a replica by accident.

On Supabase, disk metrics are updated daily, so leave room in the alert for a day of growth. The seed-script side, including where that data should go, is its own task: generate realistic fake data for staging. After a provider failover, run the canary before trusting the app.

Common questions about read-only transaction errors

How to make a Postgres database read only?

Run ALTER DATABASE <name> SET default_transaction_read_only = on; it becomes the default for sessions started after the change, and a session can still override it with SET TRANSACTION for its own transaction, so revoking write privileges is the stricter tool, in my reading. Undo it when the maintenance window ends, or you have created the read-only default cause yourself.

How do I abort a transaction in PostgreSQL?

Run ROLLBACK, which rolls back the current transaction and discards all the updates it made; ABORT behaves identically and exists only for historical reasons. If the read-only error came inside a transaction block, end that transaction with ROLLBACK before retrying the write on a fixed connection.

What does the error “Failed to update because the database is read-only” mean and how can I fix it?

It is SQL Server’s error 3906, which Microsoft prints as Failed to update database "%.*ls" because the database is read-only. with the database name in the placeholder. In a database set to READ_ONLY, users can read data but not modify it, and changing the state back needs exclusive access to the database. The same three questions apply as for Postgres: who set it, whether you are on a secondary or replica, and whether the disk is full.

How to fix the transaction log for database is full?

That is SQL Server’s error 9002: if the log fills while the database is online, the database stays online but can only be read, not updated. A common solution, Microsoft says, is to ensure transaction log backups are performed, which ensures the log is truncated; Microsoft’s other common causes include a full disk volume, a fixed maximum log size or autogrow turned off, and replication or availability group synchronization that is unable to complete, and shrinking a log file alone can’t solve the problem (Microsoft’s guide to error 9002). In Postgres, the closest cousin is a full disk, the provider read-only row in the table.