---
title: "Freeze writes before a cutover, including connections already open | Proofline"
description: "Setting default_transaction_read_only = on with ALTER DATABASE looks like a write freeze, but it changes the default only for sessions that connect afterwards."
url: https://proofline.sh/learn/postgres-read-only-cutover-existing-sessions/
---

[Learning center](/learn/)

# Freeze writes before a cutover, including connections already open

Setting default\_transaction\_read\_only = on with ALTER DATABASE looks like a write freeze, but it changes the default only for sessions that connect afterwards. In our lab, a pooled connection opened before the change kept inserting, and so did a new session that turned the setting off for itself. Revoke the application role's write privileges instead, which applies to open sessions at once, and check that writes stopped.

Breaking migrations The Proofline team November 22, 2026 3 min read

## The setting that waits for new sessions

Before moving `orders` to another database, the cutover stops writes to the old one. We opened a connection as a pool would, applied the setting, and tried to write from several sessions, on PostgreSQL 16.15:

```
ALTER DATABASE shop SET default_transaction_read_only = on;
```

| Session | Insert |
| --- | --- |
| Opened before the change | inserted |
| Opened after the change | `cannot execute INSERT in a read-only transaction` (SQLSTATE 25006) |
| Opened after, then `SET default_transaction_read_only = off` | inserted |
| Opened after, inside `BEGIN READ WRITE` | inserted |

`ALTER DATABASE ... SET` stores a default that each new session reads when it starts, as the [ALTER DATABASE documentation](https://www.postgresql.org/docs/16/sql-alterdatabase.html) notes. A connection pool that keeps connections for hours keeps writing for hours. The setting was also never meant to stop a client that wants to write: any session can change its own default or open a read-write transaction.

Ending the sessions makes the setting apply. We terminated the three client sessions in the database with `pg_terminate_backend`, and a reconnecting session then failed to insert. That still leaves the second and third rows of the table open to any client that sets the parameter.

## Revoke write privileges

Privileges are checked when each statement runs, so a `REVOKE` applies to connections that are already open:

```
REVOKE INSERT, UPDATE, DELETE ON orders FROM orders_app;
```

From an `orders_app` connection opened before the revoke:

| Statement | Result |
| --- | --- |
| `INSERT INTO orders ...` | `permission denied for table orders` (SQLSTATE 42501) |
| `SELECT count(*) FROM orders` | ok |

`GRANT INSERT, UPDATE, DELETE ON orders TO orders_app` restored writes on the same connection. Revoke from every role that writes the moved tables, including job and service roles, and keep the owner and the replication role for the cutover itself. A role that owns the table, or a superuser, bypasses the revoke.

## Prove the freeze

A freeze is only as good as the check that it worked. Before switching, look for writes on the old database since the freeze began:

```
SELECT pid, usename, application_name, state, xact_start, query
FROM pg_stat_activity
WHERE datname = current_database() AND backend_type = 'client backend'
  AND state <> 'idle' AND pid <> pg_backend_pid();
```

Then compare `pg_current_wal_lsn()` twice, a few seconds apart. With writes frozen it should barely move; checkpoints and autovacuum still write a little, and a steady climb points at a writer you missed. [Tag PostgreSQL connections with the release that opened them](/learn/postgres-which-release-is-connected/) names which code still holds a session.

## The rollback limit

The revoke is the start of the cutover's point of no return. Until the application writes to the new database, a `GRANT` puts everything back. After it, the old database is behind, and going back means replicating the new writes the other way first. The rest of the cutover is in [set sequences before cutting over to a logically replicated database](/learn/postgres-logical-replication-sequences-cutover/).

## One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. [Eligibility and fair-use terms](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Set sequences before cutting over to a logically replicated database](/learn/postgres-logical-replication-sequences-cutover/)
    
    Breaking migrations The Proofline team November 22, 2026 2 min read
    
-   ### [Tag PostgreSQL connections with the release that opened them](/learn/postgres-which-release-is-connected/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
