I am attempting to logically replicate a production postgres instance to facilitate a blue/green major version upgrade from 13 -> 18 (don't ask).
First, I start a replication slot on the blue (production) instance to begin WAL retention and take note of the LSN.
CREATE PUBLICATION all_rep FOR ALL TABLES; SELECT * FROM pg_create_logical_replication_slot('all_rep_sub', 'pgoutput'); I take a snapshot of the blue environment, and bring up the green from it immediately, which takes 1-2 hours or so.
I advance the slot's LSN to the subscriber's
SELECT pg_current_wal_lsn(); I subscribe from green to blue:
CREATE SUBSCRIPTION all_rep_sub CONNECTION 'connstring' PUBLICATION all_rep WITH (slot_name = 'all_rep_sub', create_slot = false, copy_data = false); I see two behaviors I don't understand: firstly, despite advancing the LSN on the slot successfully, I see default uuid primary key conflicts on green on busy timeseries tables. I would expect the WAL replay to "pick up where it left of" wrt the data on the green instance. Am I picking the wrong LSN? Is there any way I can reliably see the "last" LSN that the snapshot had consumed before it was taken?
Second and more importantly, the slot never advances, at all. I had suspected that maybe the volume was too high and the single slot simply couldn't keep up, but in that case I would expect to see the slot advancing at least some, just not keeping pace. But I basically see it staying completely frozen.
I have tried to eliminate long running transactions on blue. There are wraparound vacuums but as far as I know they are not long lived.
I have 5 large (billions) timeseries tables that are partitioned by day.
To add to the mystery, if I create 4 distinct slots for the timeseries tables and 1 for "everything else," the replication seems to work fine!
This is managed on aws rds, and the stuck all tables replication is preventing us from using the aws blue/green deployment. I have a suspicion that, since I'm able to "replicate" the issue (lol) manually by creating my own all tables publication, the partitions are maybe not playing well with it, and that aws managed b/g on rds is using that under the hood, which is the show stopper, but I have limited visibility into what is stalling the slot.
EDIT: to add a small amount of context, I am able to see the slot not advance if I advance the LSN past a known point of overlap, in other words, I am guaranteed data loss in that scenario but no overlap. So these are two different issues kind of, but I understand that if the replication slot hits a conflict, it will stop streaming.
pg_current_wal_lsnbookmark you're relying on, doesn't correspond to the actual snapshot head/state, meaning when you advance the LSN, you're actually positioning the subscriber further back than it really is. When it starts reading the stream it thinks it's early, and tries to apply deltas that are already in its base snapshot, inserting duplicates of things it already has.