Re: Synchronizing slots from primary to standby

On Thu, Mar 28, 2024 at 10:08 AM Zhijie Hou (Fujitsu)
<houzj(dot)fnst(at)fujitsu(dot)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());"
./psql -d postgres -p 5432 -c "INSERT INTO dummy1 VALUES(999);"

./psql -d postgres -p 5433 -c "SELECT pg_promote();"
./psql -d postgres -p 5433 -c "SELECT pg_is_in_recovery();"

./psql -d postgres -p 5433 -c "select * from
pg_logical_slot_get_changes('lrep_sync_slot', NULL, NULL);"

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com