search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

What is the prefered way of dealing with sequeneces replication #379

Open rhollins opened 2 years ago

rhollins commented 2 years ago

Hi,

I'm trying to find the best way to deal with sequence replication:

Currently, I'm adding all sequences this way when we setup replication: SELECT pglogical.replication_set_add_all_sequences(set_name := ssome_name, schema_names := ARRAY['schema_name'], synchronize_data := true)

Now the question is as we add new tables and sequences to source db should we just rerun this above command again on daily schedule for example considering the automated synchronization doesn't always work like highlighted in this issue https://github.com/2ndQuadrant/pglogical/issues/163? This would be in order to ensure that source and target both have same sequences.

Also another question should we need to failover to the destination database I assume we need to run: select pglogical.synchronize_sequence( seqoid ) from pglogical.sequence_state; only once to ensure the sequence values are the same.

lpossamai commented 1 year ago

EnterpriseDB mention the sequences are not replicated in real-time here.

And yes, @rhollins , just before cutting off the replication to failover, make sure to run synchronize_sequence.