The copy#
The orders table moves from one PostgreSQL 16.15 cluster to another. The new cluster subscribes to a publication on the old one:
-- old database
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- new database, with the same table definition
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=old-db port=5432 dbname=shop user=replicator'
PUBLICATION orders_pub;
The initial copy and five later inserts arrived: both databases had 1,005 rows with a highest id of 1005. The orders_id_seq sequences, read as last_value and is_called, did not match:
| Database | Sequence value | Used yet |
|---|---|---|
| old | 1005 | t |
| new | 1 | f |
The logical replication restrictions list this: sequence data is not replicated. The rows carry their id values, so the table looks complete, and nothing has used the new database's sequence yet.
The cutover failure#
We disabled the subscription, as a cutover does once writes have stopped on the old database, and let the application insert on the new one:
ALTER SUBSCRIPTION orders_sub DISABLE;
INSERT INTO orders (status) VALUES ('paid') RETURNING id;
ERROR: duplicate key value violates unique constraint "orders_pkey"
DETAIL: Key (id)=(1) already exists.
Each retry advances the sequence by one, so the application fails on every insert until the sequence passes 1005. With a million rows, that is a million failed inserts. If the sequence feeds a column without a unique constraint, the inserts succeed and reuse IDs instead.
Set the sequences in the freeze#
-- on the new database, after writes to the old one have stopped and replication has caught up
SELECT setval('orders_id_seq', (SELECT max(id) FROM orders) + 10000);
-- 11005
INSERT INTO orders (status) VALUES ('paid') RETURNING id;
-- 11006
The margin covers IDs the old database handed out to transactions that had not committed when you read the maximum. Without a margin, setting the sequence to max(id) while writes still reach the old database leaves a window for exactly the collision above. List every sequence the moved tables use, including those behind identity columns:
SELECT sequencename, last_value FROM pg_sequences;
Run it on both databases and compare before the switch.
The order of the cutover#
- Stop writes to the old database, and make sure they stopped; freeze writes before a cutover, including connections already open shows how that check can mislead.
- Wait until the subscription has applied everything: the publisher's slot shows no WAL behind
confirmed_flush_lsn. - Set every sequence on the new database above the old value with a margin.
- Compare row counts or checksums, as in compare two PostgreSQL tables in chunks with a checksum.
- Point the application at the new database and disable the subscription.
The rollback limit is step 5: writes on the new database do not flow back unless you set up replication in the other direction before allowing them.