Sequence Replication and Safe Failover
PostgreSQL 19 logical replication can copy sequence values with ALL SEQUENCES and REFRESH SEQUENCES. When values move, the duplicate key it prevents, and the cutover order.
TLDR: Through PostgreSQL 18, logical replication copied rows but not sequence values, so a promoted subscriber handed out IDs that the old primary had already used. PostgreSQL 19 adds ALL SEQUENCES to publications and ALTER SUBSCRIPTION ... REFRESH SEQUENCES. Values are copied at specific moments, not continuously: at subscription creation, and when you run the refresh. A cutover runbook must run the refresh inside the write freeze and check the result. The same release adds FOR ALL TABLES EXCEPT (TABLE ...).
An identity column advances its sequence on the publisher with every insert. Through 18, the subscriber's copy of that sequence stayed at its start value. Nothing reads the subscriber's sequences during normal replication, so nobody notices until the subscriber is promoted, in a failover or at the end of a logical-replication upgrade, and the application starts inserting there. The first new rows then collide with IDs the old primary already gave out.
PostgreSQL 19 adds sequence synchronization to logical replication (release notes, replicating sequences).
Prerequisites
- Two PostgreSQL 19 servers, a publisher and a subscriber. The publisher must run 19 for sequence synchronization. From an 18 publisher, you still copy sequence values yourself, as the upgrade checklist post shows.
- The publisher does not need
wal_level = logicalany more: creating the subscription's slot raises the level on its own, as the previous part showed. On our publisher,effective_wal_levelreadlogicalright after the subscription was created.
With Docker, put two containers on one network:
docker network create pg19net
docker run -d --name pg19pub --network pg19net \
-e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=lab postgres:19
docker run -d --name pg19sub --network pg19net \
-e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=lab postgres:19
until docker exec pg19sub pg_isready -h localhost -q; do sleep 1; done
Set Up a Publication with Sequences
On the publisher:
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric NOT NULL
);
CREATE TABLE audit_log (id int PRIMARY KEY, note text);
INSERT INTO invoices (amount) SELECT 100 FROM generate_series(1, 2000);
CREATE PUBLICATION billing
FOR ALL TABLES EXCEPT (TABLE audit_log), ALL SEQUENCES;
SELECT tablename FROM pg_publication_tables WHERE pubname = 'billing' ORDER BY 1;
SELECT * FROM pg_publication_sequences WHERE pubname = 'billing';
tablename
-----------
invoices
pubname | schemaname | sequencename
---------+------------+-----------------
billing | public | invoices_id_seq
The "all" forms go together and cannot be mixed with a list of single tables. CREATE PUBLICATION mixed FOR ALL TABLES, TABLE invoices; fails with syntax error at or near "TABLE". FOR ALL TABLES and ALL SEQUENCES need superuser.
On the subscriber, create the same table and subscribe:
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric NOT NULL
);
CREATE SUBSCRIPTION billing_sub
CONNECTION 'host=pg19pub dbname=lab user=postgres password=postgres'
PUBLICATION billing;
Wait until every relation has state r (ready), then compare:
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel ORDER BY 1;
SELECT count(*), max(id) FROM invoices;
SELECT last_value, is_called FROM invoices_id_seq;
srrelid | srsubstate
-----------------+------------
invoices_id_seq | r
invoices | r
count | max
-------+------
2000 | 2000
last_value | is_called
------------+-----------
2000 | t
The sequence is listed next to the table, and its value came over with the initial copy. On 18 the subscriber's sequence would still be at 1.
Reproduce the Classic Incident
Insert 500 more rows on the publisher:
INSERT INTO invoices (amount) SELECT 100 FROM generate_series(1, 500);
On the subscriber, the rows arrive, but the sequence does not move:
count | max
-------+------
2500 | 2500
last_value | is_called
------------+-----------
2000 | t
Now act like the application after a cutover and insert on the subscriber:
INSERT INTO invoices (amount) VALUES (999) RETURNING id;
ERROR: duplicate key value violates unique constraint "invoices_pkey"
DETAIL: Key (id)=(2001) already exists.
IDs 2001 to 2500 were used on the old primary and replicated as rows, and the subscriber's sequence hands them out again. With gaps, deletes or explicit IDs in real data, the collision may come later than the first insert, but it comes.
When Values Move
Per the documentation, sequence values are copied at three points:
- At
CREATE SUBSCRIPTIONwithcopy_dataon (the default), which is why the first comparison matched. - At
ALTER SUBSCRIPTION ... REFRESH PUBLICATION, for sequences newly added to the publication. - At an explicit refresh of the sequences the subscription already has:
ALTER SUBSCRIPTION billing_sub REFRESH SEQUENCES;
The refresh starts a synchronization worker and returns. On 19.0, right after the command, pg_subscription_rel showed the sequence in state i (being synchronized) with the old value, and less than half a second later in state r with 2500. A runbook should wait for that, then confirm the value, instead of trusting the command's return:
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srrelid = 'invoices_id_seq'::regclass;
SELECT last_value, is_called FROM invoices_id_seq; -- on both sides: they must match
For an identity column that counts up by one, as here, this query also checks that the next value is past the table's highest ID:
SELECT (SELECT max(id) FROM invoices) AS max_id,
s.last_value, s.is_called,
CASE WHEN s.is_called THEN s.last_value + 1 ELSE s.last_value END AS next_value
FROM invoices_id_seq s;
max_id | last_value | is_called | next_value
--------+------------+-----------+------------
2500 | 2500 | t | 2501
The same insert that failed now returns id = 2501.
Between refreshes, the subscriber's sequences fall behind again as soon as the publisher inserts. That gives two rules:
- In a planned cutover, run
REFRESH SEQUENCESright before you promote, inside the write freeze, wait for stater, and compare the values on both sides. - In an unplanned failover, the publisher may be gone and no refresh can help. The fallback is the same as before 19: advance each sequence past the highest replicated ID with
setval().
The documentation also warns that equal sequence LSNs do not prove equal values. PostgreSQL writes a WAL record for a sequence only once per 32 values (it logs ahead), so advances inside that range leave the LSN the same. Check values against the table, not LSNs against each other.
The Cutover Order
- Stop application writes on the publisher and let open transactions finish.
- Check that the subscriber has applied everything. A sequence refresh says nothing about rows. Compare the publisher's position with the subscriber's origin:
SELECT pg_current_wal_lsn(); -- on the publisher
SELECT external_id, remote_lsn FROM pg_replication_origin_status; -- on the subscriber
On 19.0 both read 0/01C48258 once the subscriber had caught up. Also count the rows of the tables that matter most.
- Run
ALTER SUBSCRIPTION ... REFRESH SEQUENCESon the subscriber. - Wait until each critical sequence is in state
rinpg_subscription_rel, and check that its value matches the publisher's. - Only then point application writes at the subscriber.
Practice it with realistic timing. A drill that promotes a subscriber right after its initial copy passes, because the copy synchronized the sequences. The failure needs publisher inserts after the copy and an application insert after the cutover. In our run that was 500 inserts, then one insert on the subscriber.
Setup Rules We Hit
- The subscriber needs every published sequence. With
ALL SEQUENCES, adding a sequence on the publisher that the subscriber does not have makes the next refresh fail. On 19.0, afterCREATE SEQUENCE orphan_seqon the publisher,ALTER SUBSCRIPTION billing_sub REFRESH PUBLICATIONfailed withERROR: relation "public.orphan_seq" does not exist. Your schema tooling must create sequences on the subscriber along with tables. The synchronization worker also checks that the definitions match. - Create the subscriber's table before rows arrive.
ALL TABLESpublishes a new table as soon as it exists on the publisher. When we inserted into a newreceiptstable there before creating it on the subscriber, the apply worker loggedlogical replication target relation "public.receipts" does not existand retried until the table existed. - A subscription to the same cluster hangs while it creates its slot, because the slot creation waits for the transaction that runs
CREATE SUBSCRIPTION. TheCREATE SUBSCRIPTIONdocumentation gives the workaround: create the slot first withpg_create_logical_replication_slot()and subscribe withcreate_slot = false. This only matters when both databases are on one server, as in CI. - A sequence sync error names the sequence. If the worker fails, for example because the subscription owner cannot
SET ROLEto the sequence owner, it exits and retries like the apply worker, and the log'sCONTEXTline names the sequence and the subscription.
FOR ALL TABLES EXCEPT
Before 19, a publication could list tables (FOR TABLE), whole schemas (FOR TABLES IN SCHEMA, since 15), or every table (FOR ALL TABLES). 19 adds exclusions to the last form, as in the publication above. New tables join it with no ALTER PUBLICATION:
CREATE TABLE receipts (id int PRIMARY KEY);
SELECT tablename FROM pg_publication_tables WHERE pubname = 'billing' ORDER BY 1;
tablename
-----------
invoices
receipts
DDL still does not replicate. Create the table on the subscriber, then pick up the new member:
CREATE TABLE receipts (id int PRIMARY KEY);
ALTER SUBSCRIPTION billing_sub REFRESH PUBLICATION;
After that, the row we had inserted on the publisher arrived. \dRp+ billing in psql lists the exclusions under Except tables:.
One fix before 19.0 matters for FOR ALL TABLES publications on partitioned tables. If ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY was interrupted, the partition stayed "detach pending", and decoding a change to it crashed the WAL sender in a loop. In 19 the change that added EXCEPT made every FOR ALL TABLES publication reach this path. It is fixed in 19.0. On a release candidate, run DETACH PARTITION ... FINALIZE as soon as a concurrent detach is interrupted.
What to Do When You Upgrade
- If you use logical replication between 19 servers, add
ALL SEQUENCESto the publication (or recreate it) and make sure the subscriber has every sequence. - Add
REFRESH SEQUENCESand the check query to your cutover runbook, inside the write freeze. - Keep the
setval()step for unplanned failovers, and for the upgrade from 18 itself, because an 18 publisher cannot publish sequences. - Replace long
FOR TABLElists withFOR ALL TABLES EXCEPT (TABLE ...)where that is what you meant.
The last part covers the other direction of replication lag: read-your-writes on physical standbys with WAIT FOR LSN.
Tags
Found an issue?