Synchronize sequence values to the subscriber in logical replication

Command

# Generate the setval script on the publisher and apply it on the subscriber (at cutover, after writes are stopped):
psql -h <publisher_host> -d <db> -XAtc "SELECT format('SELECT setval(%L, %s, %s);', quote_ident(schemaname) || '.' || quote_ident(sequencename), COALESCE(last_value, start_value), (last_value IS NOT NULL)::text) FROM pg_sequences;" | psql -h <subscriber_host> -d <db> -X

What it does

Because logical replication does not carry sequence (including identity) values, this reads the last value of every sequence on the publisher and generates and directly applies setval commands that set the same value on the subscriber.

When to use it

At the cutover of an upgrade/migration via logical replication, right before pointing the application to the subscriber.

Caution

If run while the publisher is still taking writes, the values are stale immediately and duplicate key errors follow after cutover; run it after stopping writes or add a safety margin. DDL is not replicated either: apply schema changes (ALTER TABLE, new indexes) to both sides separately, subscriber first. pg_sequences returns NULL last_value for sequences you cannot read; run the command as the owner or a superuser.