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 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 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.