Re: Synchronizing slots from primary to standby - Mailing list pgsql-hackers
From | shveta malik |
---|---|
Subject | Re: Synchronizing slots from primary to standby |
Date | |
Msg-id | CAJpy0uD23XXNUgUaJtJatVC5LHha22TsRkaKJBDiGRRcuGACpQ@mail.gmail.com Whole thread Raw |
In response to | Re: Synchronizing slots from primary to standby (Amit Kapila <amit.kapila16@gmail.com>) |
List | pgsql-hackers |
On Mon, Apr 1, 2024 at 10:40 AM Amit Kapila <amit.kapila16@gmail.com> wrote: > > On Mon, Apr 1, 2024 at 10:01 AM Bharath Rupireddy > <bharath.rupireddyforpostgres@gmail.com> wrote: > > > > On Thu, Mar 28, 2024 at 10:08 AM Zhijie Hou (Fujitsu) > > <houzj.fnst@fujitsu.com> wrote: > > > > > > [2] The steps to reproduce the data miss issue on a primary->standby setup: > > > > I'm trying to reproduce the problem with [1], but I can see the > > changes after the standby is promoted. Am I missing anything here? > > > > ubuntu:~/postgres/pg17/bin$ ./psql -d postgres -p 5433 -c "select * > > from pg_logical_slot_get_changes('lrep_sync_slot', NULL, NULL);" > > lsn | xid | data > > -----------+-----+--------------------------------------------- > > 0/30000B0 | 738 | BEGIN 738 > > 0/3017FC8 | 738 | COMMIT 738 > > 0/3017FF8 | 739 | BEGIN 739 > > 0/3019A38 | 739 | COMMIT 739 > > 0/3019A38 | 740 | BEGIN 740 > > 0/3019A38 | 740 | table public.dummy1: INSERT: a[integer]:999 > > 0/3019AA8 | 740 | COMMIT 740 > > (7 rows) > > > > [1] > > -#define LOG_SNAPSHOT_INTERVAL_MS 15000 > > +#define LOG_SNAPSHOT_INTERVAL_MS 1500000 > > > > ./initdb -D db17 > > echo "archive_mode = on > > archive_command='cp %p /home/ubuntu/postgres/pg17/bin/archived_wal/%f' > > wal_level='logical' > > autovacuum = off > > checkpoint_timeout='1h'" | tee -a db17/postgresql.conf > > > > ./pg_ctl -D db17 -l logfile17 start > > > > rm -rf sbdata logfilesbdata > > ./pg_basebackup -D sbdata > > > > ./psql -d postgres -p 5432 -c "SELECT > > pg_create_logical_replication_slot('lrep_sync_slot', 'test_decoding', > > false, false, true);" > > ./psql -d postgres -p 5432 -c "SELECT > > pg_create_physical_replication_slot('phy_repl_slot', true, false);" > > > > echo "port=5433 > > primary_conninfo='host=localhost port=5432 dbname=postgres user=ubuntu' > > primary_slot_name='phy_repl_slot' > > restore_command='cp /home/ubuntu/postgres/pg17/bin/archived_wal/%f %p' > > hot_standby_feedback=on > > sync_replication_slots=on" | tee -a sbdata/postgresql.conf > > > > touch sbdata/standby.signal > > > > ./pg_ctl -D sbdata -l logfilesbdata start > > ./psql -d postgres -p 5433 -c "SELECT pg_is_in_recovery();" > > > > ./psql -d postgres > > > > SESSION1, TXN1 > > BEGIN; > > create table dummy1(a int); > > > > SESSION2, TXN2 > > SELECT pg_log_standby_snapshot(); > > > > SESSION1, TXN1 > > COMMIT; > > > > SESSION1, TXN1 > > BEGIN; > > create table dummy2(a int); > > > > SESSION2, TXN2 > > SELECT pg_log_standby_snapshot(); > > > > SESSION1, TXN1 > > COMMIT; > > > > ./psql -d postgres -p 5432 -c "SELECT > > pg_replication_slot_advance('lrep_sync_slot', pg_current_wal_lsn());" > > > > After this step and before the next, did you ensure that the slot sync > has synced the latest confirmed_flush/restart LSNs? You can query: > "select slot_name,restart_lsn, confirmed_flush_lsn from > pg_replication_slots;" to ensure the same on both the primary and > standby. +1. To ensure last sync, one can run this manually on standby just before promotion : SELECT pg_sync_replication_slots(); thanks Shveta
pgsql-hackers by date: