Learning center

Set sequences before cutting over to a logically replicated database

Logical replication copies table rows to the new database, not the sequences that generate their IDs. After the cutover, the first insert on the new database asked its sequence for an ID, got 1, and failed with a duplicate key. Set every sequence on the new database above the old one's value, with a margin, in the same freeze that stops writes to the old database.

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:

DatabaseSequence valueUsed yet
old1005t
new1f

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#

  1. 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.
  2. Wait until the subscription has applied everything: the publisher's slot shows no WAL behind confirmed_flush_lsn.
  3. Set every sequence on the new database above the old value with a margin.
  4. Compare row counts or checksums, as in compare two PostgreSQL tables in chunks with a checksum.
  5. 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.

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.

Start free with GitHub

Related reading