PostgreSQL 19's sequence replication only catches up when you tell it to

A logical replication subscriber in PostgreSQL has always replicated your rows and ignored your sequences. Promote it to primary after a failover, and the first few inserts try to reuse IDs that already exist on disk, because the subscriber's sequences never left their starting position. This is one of the standard reasons teams avoid logical replication for major-version upgrades or disaster recovery and reach for physical replication instead, even though physical replication can't filter which tables come across. PostgreSQL 19 is meant to fix the sequence half of that trade-off. I spent an afternoon with the release candidate trying to break it, and it is both a real fix and a narrower one than the release notes make it sound.

This is PostgreSQL 19 RC1, not a shipped release. General availability is currently planned for 19 November 2026, two weeks after I ran this, so anything below could still change before GA.

What's new

PostgreSQL 19 lets a publication include ALL SEQUENCES, and a subscriber created against it picks up the publisher's sequence values as part of the initial sync. You can also ask for a sequence refresh on demand with ALTER SUBSCRIPTION ... REFRESH SEQUENCES, and a new function, pg_get_sequence_data(), lets you inspect what a subscriber currently thinks a sequence's value is.

None of that exists in PostgreSQL 18. I confirmed that directly before testing anything on 19:

postgres18=# create publication pub18seqtest for all tables, all sequences; ERROR: syntax error at or near "," LINE 1: create publication pub18seqtest for all tables, all sequence... ^ 

The setup

I ran two containers on the same Docker network, pub19 and sub19, both postgres:19rc1, with wal_level=logical on the publisher. A table with an identity column and 1,000 seeded rows:

create table orders (id bigint generated always as identity primary key, note text); insert into orders (note) select 'row '||g from generate_series(1,1000) g; create publication pub19pub for all tables, all sequences; 

On the subscriber, the table has to exist already, same as it always has for logical replication:

create table orders (id bigint generated always as identity primary key, note text); create subscription sub19sub connection 'host=pub19 port=5432 user=test password=test dbname=test' publication pub19pub; 

A few seconds later:

Same 1,000 rows either way. The sequence is where the two versions diverge, and it's exactly the number that matters: on 19, the subscriber's sequence landed on the publisher's actual position; on 18, it never moved from its default starting value.

What that gets you, and what it doesn't

I treated the subscriber as if it had just been promoted and inserted a row directly into it, with the subscription disabled first, same as a real failover runbook would do.

On the 18.6 subscriber, with its sequence still parked at 1:

test=# insert into orders (note) values ('new after promotion') returning id; ERROR: duplicate key value violates unique constraint "orders_pkey" DETAIL: Key (id)=(1) already exists. 

Immediate collision, as expected. On a fresh 19 RC1 subscriber, right after the initial sync, the same insert returned id=1001 and succeeded. That's the headline, and it's genuine: for a subscriber promoted shortly after CREATE SUBSCRIPTION, the sequence problem is gone.

But table rows keep replicating continuously after that point, and I wanted to know whether the sequence value does too. I inserted 500 more rows on the publisher without touching the subscription at all:

pub19=# insert into orders (note) select 'row2 '||g from generate_series(1,500) g; pub19=# select last_value from orders_id_seq; last_value ------------ 1500 

The subscriber's table caught up fully, count(*) and max(id) both landing on 1,500. Its sequence did not:

sub19=# select last_value from orders_id_seq; last_value ------------ 1000 

Still 1,000, five hundred rows behind the data it already holds. Running the refresh explicitly fixed it in well under a second:

sub19=# alter subscription sub19sub refresh sequences; ALTER SUBSCRIPTION Time: 118.432 ms sub19=# select last_value from orders_id_seq; last_value ------------ 1500 

So sequence sync in PostgreSQL 19 is a snapshot taken at specific moments — CREATE SUBSCRIPTION, REFRESH PUBLICATION, or REFRESH SEQUENCES — not a continuous stream like the row data. That distinction isn't just academic. I pushed 200 more rows through the publisher (total 1,700, fully replicated to the subscriber as before), then did a proper promotion: disabled the subscription, dropped it, and inserted locally, without refreshing sequences first because that last refresh had already happened before this new batch landed:

sub19=# drop subscription sub19sub; NOTICE: dropped replication slot "sub19sub" on publisher DROP SUBSCRIPTION sub19=# insert into orders (note) values ('promoted without refresh') returning id; ERROR: duplicate key value violates unique constraint "orders_pkey" DETAIL: Key (id)=(1501) already exists. 

That is the exact failure the feature exists to prevent, reproduced on PostgreSQL 19 RC1, by skipping one step that the documentation mentions but doesn't put front and centre: refresh sequences has to be the last thing you do before you promote, not something you did at some earlier point in the subscription's life.

What it refuses

I wanted to see what happens when new sequences show up after the subscription already exists, since that's the common case — someone adds a table with a new identity column weeks into running this setup. I created 300 extra sequences on the publisher, then asked the subscriber to pick them up:

sub19=# alter subscription sub19sub2 refresh publication; ERROR: relation "public.seq_256" does not exist 

Zero of the 300 got created. This isn't a per-object skip; it's all-or-nothing, and unlike tables being matched by name, sequences apparently need to already exist as objects on the subscriber before the subscription will acknowledge them at all. The documented workaround is to create matching sequences yourself first:

docker exec pub19 psql -U test -d test -t -A -c \ "select string_agg('create sequence if not exists '||relname||';', E'\n') from pg_class where relkind='S' and relname like 'seq_%';" > seqs.sql docker cp seqs.sql sub19:/tmp/seqs.sql docker exec sub19 psql -U test -d test -f /tmp/seqs.sql 

With the 300 sequences pre-created, both refresh publication and refresh sequences succeeded, each in under 200ms, and pg_sequence on the subscriber went from 1 row to 301. The workaround works, but it means "add a sequence" is a two-step manual job on both ends, the same shape of chore logical replication has always had for new tables.

What it costs

Nothing I could measure. Refreshing one sequence and refreshing 301 both finished in under 200 milliseconds. There's a new column, sync_seq_error_count, on pg_stat_subscription_stats, meant for watching this in production. I checked it existed and read it successfully, but in the time I had I couldn't engineer a scenario that actually incremented it rather than failing the whole refresh outright the way the missing-relation case above did, so I can't tell you what triggers it specifically.

There's also a new function for inspecting a subscriber's view of a sequence without touching nextval(), which matters because reading a sequence normally advances it:

sub19=# select * from pg_get_sequence_data('orders_id_seq'::regclass); last_value | is_called | page_lsn ------------+-----------+------------ 1000 | t | 0/01BBEE00 

According to the documentation, page_lsn is the LSN of the most recent WAL record that touched this sequence on the subscriber itself, which is a way to tell a value that was just refreshed from one that's been sitting since the initial sync, without having to remember when you last ran the refresh command by hand. I didn't build a monitoring query around it; I only confirmed the function runs and returns a value that moved when I expected it to.

What I got wrong on the way

My first pass at the promotion test skipped the step where you disable the subscription before writing to the subscriber. I inserted directly into orders on sub19 while sub19sub was still active, then separately inserted 500 more rows on the publisher expecting a clean before/after comparison. Instead, the subscriber's row count froze at 1,001 and stopped moving. docker logs sub19 showed why, repeating every five seconds:

ERROR: conflict detected on relation "public.orders": conflict=insert_exists DETAIL: Could not apply remote change: remote row (1001, row2 1). Key already exists in unique index "orders_pkey", modified in transaction 672: key (id)=(1001), local row (1001, new after promotion). 

What happened: I'd written id=1001 by hand on the subscriber, then the publisher's own insert batch tried to replicate a row with that exact same id into the same table a few seconds later. Postgres won't silently overwrite an existing primary key during apply, so the worker died on it, restarted, and died on the identical row again, five seconds on the dot, several times over before I went and looked at the logs. Nothing wrong with the new sequence feature here — I'd just left sub19sub subscribed while poking at the subscriber directly, which is not what promoting a replica actually looks like. Once I noticed that in the logs I dropped both containers and started the comparison over.

Run it yourself

This needs Docker.

docker network create pgtest docker run -d --name pub19 --network pgtest -e POSTGRES_PASSWORD=test -e POSTGRES_USER=test -e POSTGRES_DB=test \ postgres:19rc1 -c wal_level=logical -c max_replication_slots=10 -c max_wal_senders=10 docker run -d --name sub19 --network pgtest -e POSTGRES_PASSWORD=test -e POSTGRES_USER=test -e POSTGRES_DB=test \ postgres:19rc1 -c wal_level=logical sleep 8 docker exec pub19 psql -U test -d test -c \ "create table orders (id bigint generated always as identity primary key, note text); insert into orders (note) select 'row '||g from generate_series(1,1000) g; create publication pub19pub for all tables, all sequences;" docker exec sub19 psql -U test -d test -c \ "create table orders (id bigint generated always as identity primary key, note text);" docker exec sub19 psql -U test -d test -c \ "create subscription sub19sub connection 'host=pub19 port=5432 user=test password=test dbname=test' publication pub19pub;" sleep 5 docker exec sub19 psql -U test -d test -c "select last_value from orders_id_seq;" docker exec pub19 psql -U test -d test -c \ "insert into orders (note) select 'row2 '||g from generate_series(1,500) g;" sleep 5 docker exec sub19 psql -U test -d test -c "select last_value from orders_id_seq;" # still 1000 docker exec sub19 psql -U test -d test -c "alter subscription sub19sub refresh sequences;" docker exec sub19 psql -U test -d test -c "select last_value from orders_id_seq;" # now 1500 

What to do with this

If you're planning a failover runbook on logical replication once PostgreSQL 19 ships, add ALTER SUBSCRIPTION ... REFRESH SEQUENCES as the literal last command before you drop the subscription and start writing, not something you ran when you first set the thing up. The value it syncs is a point-in-time copy, and in my test it sat stale for as long as I let it, through hundreds of rows of otherwise-current replicated data. If you add sequences to an existing ALL SEQUENCES publication later, create the matching sequence objects on every subscriber first, or the refresh will refuse all of them rather than the one that's missing. And test this against the actual RC, not the beta notes, because it's RC1 behaviour two weeks from a GA date that the project has already moved twice this year.