Replicate Sequences with Logical Replication
Chat2DB TeamLogical replication is the standard way to upgrade PostgreSQL with almost no downtime: build a new server on the new major version, replicate the tables to it, then switch the application over. For years there has been one step everyone forgets until the first insert after cutover fails: sequences. Logical replication copied table rows but not sequence state, so the new server's identity columns still started at 1. The first INSERT after the switch then hit duplicate key value violates unique constraint.
PostgreSQL 19 fixes this. Publications can now include sequences, and subscribers can synchronize their values. This article walks through a complete setup between two PostgreSQL 19 RC2 servers. It shows what is synchronized and when (the answer is less automatic than you might hope), the errors you can meet, and the setval script you still need for older versions.
What Changed in PostgreSQL 19
CREATE PUBLICATION ... FOR ALL SEQUENCESpublishes every sequence in the database. It can be combined withFOR ALL TABLES.- When a subscription is created or refreshed, sequence values are copied from the publisher.
ALTER SUBSCRIPTION ... REFRESH SEQUENCEScopies the current values again on demand.pg_stat_subscription_statsgets async_seq_error_countcolumn, and the oldsync_error_countis renamed tosync_table_error_count.
One thing did not change: sequence increments are still not streamed continuously. More on that below, because it decides how you plan a cutover.
Setup
On the publisher (the old server), a database with a table using an identity column and a standalone sequence:
CREATE TABLE invoices (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
amount numeric
);
CREATE SEQUENCE ticket_no START 1000;
INSERT INTO invoices (amount) SELECT g FROM generate_series(1, 50) g;
SELECT nextval('ticket_no') FROM generate_series(1, 7); -- 1000 .. 1006
CREATE PUBLICATION pub_all FOR ALL TABLES, ALL SEQUENCES;Check what is published:
SELECT pubname, puballtables, puballsequences FROM pg_publication;
SELECT * FROM pg_publication_sequences; pubname | puballtables | puballsequences
---------+--------------+-----------------
pub_all | t | t
pubname | schemaname | sequencename
---------+------------+-----------------
pub_all | public | invoices_id_seq
pub_all | public | ticket_noThe identity column's internal sequence invoices_id_seq is included automatically. In psql, \dRp+ pub_all now has an "All sequences" column.
There is no way to publish a single sequence. CREATE PUBLICATION p FOR SEQUENCE ticket_no failed in RC2 with invalid publication object list. It is all sequences or none, per publication.
The Subscriber Needs the Schema
As with tables, logical replication does not create objects. On the subscriber, create the same table and sequence, then subscribe:
CREATE TABLE invoices (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
amount numeric
);
CREATE SEQUENCE ticket_no START 1000;
CREATE SUBSCRIPTION sub_all
CONNECTION 'host=old-db port=5432 user=replicator dbname=shop'
PUBLICATION pub_all;NOTICE: created replication slot "sub_all" on publisher
CREATE SUBSCRIPTIONIn practice you would copy the schema with pg_dump --schema-only, which creates the identity sequences with the tables.
A tip from our first attempt: we first created the publication in a database that held other lab tables, and CREATE SUBSCRIPTION failed with relation "public.big" does not exist. FOR ALL TABLES really means all. Every table must exist on the subscriber, or you need FOR TABLE lists. PostgreSQL 19 also adds FOR ALL TABLES EXCEPT (TABLE ...) for this situation.
Logical Decoding Without a Restart
Our publisher ran with wal_level = replica, the default. Before version 19 that would have required a restart to switch to logical. In RC2, SHOW effective_wal_level changed from replica to logical by itself as soon as the subscription created its replication slot. No restart was needed.
What Gets Synchronized at Subscribe Time
A few seconds after CREATE SUBSCRIPTION, on the subscriber:
SELECT count(*) FROM invoices; -- 50
SELECT last_value, is_called FROM invoices_id_seq; -- 50 | t
SELECT last_value, is_called FROM ticket_no; -- 1006 | t
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel ORDER BY 1; srrelid | srsubstate
-----------------+------------
invoices_id_seq | r
invoices | r
ticket_no | rBoth sequences were copied along with the table data, and they appear in pg_subscription_rel with state r (ready), just like tables. The subscriber's log shows a dedicated worker:
LOG: logical replication sequence synchronization worker for subscription "sub_all" has started
LOG: logical replication sequence synchronization worker for subscription "sub_all" has finishedSequences Are Not Streamed
Now the publisher keeps working:
-- publisher
INSERT INTO invoices (amount) SELECT g FROM generate_series(1, 25) g;
SELECT nextval('ticket_no') FROM generate_series(1, 3); -- 1007 .. 1009On the subscriber, the new rows arrive within a second, but the sequences do not move:
SELECT count(*) FROM invoices; -- 75
SELECT last_value FROM invoices_id_seq; -- 50
SELECT last_value FROM ticket_no; -- 1006The 25 new rows have ids 51 to 75, but the subscriber's sequence still says 50. If you switched the application over now, the next insert would try id 51 and fail. Sequence values are copied when the subscription synchronizes, not with every nextval().
REFRESH SEQUENCES Before Cutover
That is what the new command is for:
ALTER SUBSCRIPTION sub_all REFRESH SEQUENCES;SELECT last_value, is_called FROM invoices_id_seq; -- 75 | t
SELECT last_value, is_called FROM ticket_no; -- 1009 | t
INSERT INTO invoices (amount) VALUES (1) RETURNING id; -- 76The insert on the subscriber got id 76, the correct next value. So the cutover sequence for an upgrade becomes:
- Stop writes to the old primary (put the application in maintenance mode or revoke write access).
- Wait until the subscriber has caught up on table changes (compare
pg_current_wal_lsn()on the publisher with the subscription'slatest_end_lsninpg_stat_subscription). - Run
ALTER SUBSCRIPTION ... REFRESH SEQUENCESon the subscriber and check the values. - Point the application at the new server.
Step 3 replaces the setval script that every logical upgrade runbook used to contain. Our major version upgrade guide covers the rest of the process.
Verify Before You Switch
Do not trust the cutover to a single command. Run a quick check on the subscriber after REFRESH SEQUENCES. For each important table, the sequence must be at or above the highest id already in the table:
SELECT (SELECT max(id) FROM invoices) AS max_id,
(SELECT last_value FROM invoices_id_seq) AS seq_last; max_id | seq_last
--------+----------
76 | 76To see which sequence belongs to which column, join pg_sequences with pg_depend. Identity sequences have dependency type i and SERIAL sequences type a:
SELECT s.schemaname, s.sequencename, s.last_value,
d.refobjid::regclass AS owned_by_table, a.attname AS column_name
FROM pg_sequences s
JOIN pg_class c ON c.relname = s.sequencename
JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = s.schemaname
LEFT JOIN pg_depend d ON d.objid = c.oid
AND d.classid = 'pg_class'::regclass AND d.deptype IN ('a', 'i')
LEFT JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
ORDER BY 1, 2;On our subscriber it listed invoices_id_seq as owned by invoices.id, and the standalone sequences with no owner. Run the same query on the publisher and compare the last_value column. After writes have stopped and the refresh has run, the two lists should match exactly. Standalone sequences such as ticket_no are easy to forget in a manual check, because no max(id) comparison covers them.
New Sequences Need REFRESH PUBLICATION
A sequence created on the publisher after the subscription exists is not picked up automatically, just like a new table. Create it on both sides, then run ALTER SUBSCRIPTION ... REFRESH PUBLICATION. If you forget to create it on the subscriber, the refresh fails:
ERROR: relation "public.only_on_pub" does not existAfter we created it on the subscriber and refreshed again, only_on_pub showed up in pg_subscription_rel with state r.
Mismatched Sequence Definitions
The sequence synchronization checks that the definitions match. We created a sequence with INCREMENT 5 on the publisher and INCREMENT 1 on the subscriber. After REFRESH PUBLICATION it stayed in state i (initialize), and the subscriber log repeated:
WARNING: mismatched or renamed sequence on subscriber ("public.mism")
ERROR: logical replication sequence synchronization failed for subscription "sub_all"
LOG: background worker "logical replication sequencesync worker" (PID 98894) exited with exit code 1The worker retried every few seconds, and sync_seq_error_count in pg_stat_subscription_stats climbed to 6 while we looked. The fix is to make the definitions match:
ALTER SEQUENCE mism INCREMENT 5;On the next retry the worker finished and the sequence moved to r. Watch for this after schema changes that alter sequence options (INCREMENT, MINVALUE, MAXVALUE, CYCLE, data type) on only one side.
A useful monitoring query on the subscriber:
SELECT subname, apply_error_count, sync_table_error_count, sync_seq_error_count
FROM pg_stat_subscription_stats;
SELECT srrelid::regclass, srsubstate
FROM pg_subscription_rel
WHERE srsubstate <> 'r';Anything not in state r deserves a look at the subscriber log.
Before PostgreSQL 19: the setval Script
If either side is older than 19, you still need to copy sequence values yourself at cutover. Generate the commands on the publisher from pg_sequences:
SELECT format('SELECT setval(%L, %s, true);',
format('%I.%I', schemaname, sequencename), last_value)
FROM pg_sequences
WHERE last_value IS NOT NULL;SELECT setval('public.invoices_id_seq', 75, true);
SELECT setval('public.ticket_no', 1009, true);Run the output on the subscriber after writes have stopped. last_value is NULL for sequences that were never used, and those can be skipped. Our sequences guide explains setval's third argument. Some teams add a safety margin (last_value + 1000) in case a write slipped through. With an identity column, that costs nothing but a gap.
Summary of Commands
| Task | Command |
|---|---|
| Publish all sequences | CREATE PUBLICATION p FOR ALL SEQUENCES (or FOR ALL TABLES, ALL SEQUENCES) |
| See published sequences | SELECT * FROM pg_publication_sequences |
| Copy current values now | ALTER SUBSCRIPTION s REFRESH SEQUENCES |
| Pick up new sequences | ALTER SUBSCRIPTION s REFRESH PUBLICATION |
| Find problems | pg_stat_subscription_stats.sync_seq_error_count, pg_subscription_rel |
| Older versions | setval() script from pg_sequences |
During a cutover rehearsal it helps to compare last_value on both servers side by side. Chat2DB (opens in a new tab) can open the publisher and subscriber in two tabs, so you can run the same check query against both before you switch.
Summary
PostgreSQL 19 lets logical replication carry sequences: publish them with FOR ALL SEQUENCES, and the subscriber copies their values when the subscription is created or refreshed. Values are not streamed with every nextval(), so the subscriber falls behind as the publisher keeps inserting. Run ALTER SUBSCRIPTION ... REFRESH SEQUENCES after writes stop and before cutover. The subscriber must already have the sequences, with matching definitions, or the sequence sync worker logs mismatched or renamed sequence and retries. For servers older than 19, the setval script from pg_sequences is still the way to go.
