To synchronize sequences from a publisher to a subscriber, first publish
them using
CREATE PUBLICATION ... FOR ALL SEQUENCES and then
on the subscriber:
use CREATE SUBSCRIPTION
to initially synchronize the published sequences.
use
ALTER SUBSCRIPTION ... REFRESH PUBLICATION
to synchronize only newly added sequences.
use
ALTER SUBSCRIPTION ... REFRESH SEQUENCES
to re-synchronize all sequences currently known to the subscription.
A sequence synchronization worker will be started after executing any of the above subscriber commands, and will exit once the sequences are synchronized.
The ability to launch a sequence synchronization worker is limited by the
max_sync_workers_per_subscription
configuration.
The sequence synchronization worker validates that sequence definitions
match between publisher and subscriber. If mismatches exist, the worker
logs an error identifying them and exits. The apply worker continues
respawning the sequence synchronization worker until synchronization
succeeds. See also
wal_retrieve_retry_interval.
To resolve this, use
ALTER SEQUENCE
to align the subscriber's sequence parameters with those of the publisher.
Subscriber sequence values will become out of sync as the publisher advances them.
To detect this, compare the
pg_subscription_rel.srsublsn
on the subscriber with the page_lsn obtained
from the pg_get_sequence_data
function for the sequence on the publisher. Then run
ALTER SUBSCRIPTION ... REFRESH SEQUENCES to
re-synchronize if necessary.
Each sequence caches a block of values (typically 32) in memory before generating a new WAL record, so its LSN advances only after the entire cached batch has been consumed. As a result, sequence value drift cannot be detected by LSN comparison when sequence increments fall within the same cached block (typically 32 values).
Create some sequences on the publisher.
/* pub # */ CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1; /* pub # */ CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10;
Create the same sequences on the subscriber.
/* sub # */ CREATE SEQUENCE s1 START WITH 10 INCREMENT BY 1; /* sub # */ CREATE SEQUENCE s2 START WITH 100 INCREMENT BY 10;
Advance the sequences on the publisher a few times.
/* pub # */ SELECT nextval('s1');
nextval
---------
10
(1 row)
/* pub # */ SELECT nextval('s1');
nextval
---------
11
(1 row)
/* pub # */ SELECT nextval('s2');
nextval
---------
100
(1 row)
/* pub # */ SELECT nextval('s2');
nextval
---------
110
(1 row)
Check the sequence page LSNs on the publisher.
/* pub # */ SELECT * FROM pg_get_sequence_data('s1');
last_value | is_called | page_lsn
------------+-----------+------------
11 | t | 0/0178F9E0
(1 row)
/* pub # */ SELECT * FROM pg_get_sequence_data('s2');
last_value | is_called | page_lsn
------------+-----------+------------
110 | t | 0/0178FAB0
(1 row)
Create a publication for the sequences.
/* pub # */ CREATE PUBLICATION pub1 FOR ALL SEQUENCES;
Subscribe to the publication.
/* sub # */ CREATE SUBSCRIPTION sub1 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1' /* sub - */ PUBLICATION pub1;
Verify that the initial sequence values are synchronized.
/* sub # */ SELECT last_value, is_called FROM s1;
last_value | is_called
------------+-----------
11 | t
(1 row)
/* sub # */ SELECT last_value, is_called FROM s2;
last_value | is_called
------------+-----------
110 | t
(1 row)
Confirm that the sequence page LSNs on the publisher have been recorded on the subscriber.
/* sub # */ SELECT srrelid::regclass, srsublsn FROM pg_subscription_rel; srrelid | srsublsn ---------+------------ s1 | 0/0178F9E0 s2 | 0/0178FAB0 (2 rows)
Advance the sequences on the publisher 50 more times.
/* pub # */ SELECT nextval('s1') FROM generate_series(1,50);
/* pub # */ SELECT nextval('s2') FROM generate_series(1,50);
Check the sequence page LSNs on the publisher.
/* pub # */ SELECT * FROM pg_get_sequence_data('s1');
last_value | is_called | page_lsn
------------+-----------+------------
61 | t | 0/017CED28
(1 row)
/* pub # */ SELECT * FROM pg_get_sequence_data('s2');
last_value | is_called | page_lsn
------------+-----------+------------
610 | t | 0/017CEDF8
(1 row)
The difference between the sequence page LSNs on the publisher and the
sequence page LSNs on the subscriber indicates that the sequences are out
of sync. Re-synchronize all sequences known to the subscriber using
ALTER SUBSCRIPTION ... REFRESH SEQUENCES.
/* sub # */ ALTER SUBSCRIPTION sub1 REFRESH SEQUENCES;
Recheck the sequences on the subscriber.
/* sub # */ SELECT last_value, is_called FROM s1;
last_value | is_called
------------+-----------
61 | t
(1 row)
/* sub # */ SELECT last_value, is_called FROM s2;
last_value | is_called
------------+-----------
610 | t
(1 row)