On Thu, Mar 28, 2024 at 10:08 AM Zhijie Hou (Fujitsu)
<houzj.f...@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());"
./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
