---
title: "Set sequences before cutting over to a logically replicated database | Proofline"
description: "Logical replication copies table rows to the new database, not the sequences that generate their IDs."
url: https://proofline.sh/learn/postgres-logical-replication-sequences-cutover/
---

[Learning center](/learn/)

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

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

## 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](https://www.postgresql.org/docs/16/logical-replication-restrictions.html) 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](/learn/postgres-read-only-cutover-existing-sessions/) 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](/learn/postgres-chunked-table-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](/free-code-review/).

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

## Related reading

-   ### [Freeze writes before a cutover, including connections already open](/learn/postgres-read-only-cutover-existing-sessions/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [A column added during logical replication stops the subscriber](/learn/postgres-logical-replication-ddl-subscriber/)
    
    Schema conflicts The Proofline team November 22, 2026 2 min read
