I did some experiments over synchronous replications and
got that cascade replication can`t be synchronous. AndÂ
pg_wal_replay_wait() allows us to read your writes
consistency on cascade replication.
Beyond that, I added more tests on multi-standby replication
and cascade replications.
--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
diff --git a/src/test/recovery/t/043_wal_replay_wait.pl b/src/test/recovery/t/043_wal_replay_wait.pl
index bbd64aa67b..867c3dd94a 100644
--- a/src/test/recovery/t/043_wal_replay_wait.pl
+++ b/src/test/recovery/t/043_wal_replay_wait.pl
@@ -19,17 +19,17 @@ my $backup_name = 'my_backup';
$node_primary->backup($backup_name);
# Create a streaming standby with a 1 second delay from the backup
-my $node_standby = PostgreSQL::Test::Cluster->new('standby');
+my $node_standby1 = PostgreSQL::Test::Cluster->new('standby');
my $delay = 1;
-$node_standby->init_from_backup($node_primary, $backup_name,
+$node_standby1->init_from_backup($node_primary, $backup_name,
has_streaming => 1);
-$node_standby->append_conf(
+$node_standby1->append_conf(
'postgresql.conf', qq[
recovery_min_apply_delay = '${delay}s'
]);
-$node_standby->start;
-
+$node_standby1->start;
+# I
# Make sure that pg_wal_replay_wait() works: add new content to
# primary and memorize primary's insert LSN, then wait for that LSN to be
# replayed on standby.
@@ -37,7 +37,7 @@ $node_primary->safe_psql('postgres',
"INSERT INTO wait_test VALUES (generate_series(11, 20))");
my $lsn1 =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
-my $output = $node_standby->safe_psql(
+my $output = $node_standby1->safe_psql(
'postgres', qq[
CALL pg_wal_replay_wait('${lsn1}', 1000000);
SELECT pg_lsn_cmp(pg_last_wal_replay_lsn(), '${lsn1}'::pg_lsn);
@@ -48,12 +48,13 @@ my $output = $node_standby->safe_psql(
ok($output >= 0,
"standby reached the same LSN as primary after pg_wal_replay_wait()");
+# II
# Check that new data is visible after calling pg_wal_replay_wait()
$node_primary->safe_psql('postgres',
"INSERT INTO wait_test VALUES (generate_series(21, 30))");
my $lsn2 =
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
-$output = $node_standby->safe_psql(
+$output = $node_standby1->safe_psql(
'postgres', qq[
CALL pg_wal_replay_wait('${lsn2}');
SELECT count(*) FROM wait_test;
@@ -62,6 +63,82 @@ $output = $node_standby->safe_psql(
# Make sure the current LSN on standby and is the same as primary's LSN
ok($output eq 30, "standby reached the same LSN as primary");
+# III
+# Check two standby waiting LSN
+# Create a streaming second standby with a 1 second delay from the backup
+my $node_standby2 = PostgreSQL::Test::Cluster->new('standby2');
+$node_standby2->init_from_backup($node_primary, $backup_name,
+ has_streaming => 1);
+$node_standby2->append_conf(
+ 'postgresql.conf', qq[
+ recovery_min_apply_delay = '${delay}s'
+]);
+$node_standby2->start;
+
+# Check that new data is visible after calling pg_wal_replay_wait()
+$node_primary->safe_psql('postgres',
+ "INSERT INTO wait_test VALUES (generate_series(31, 40))");
+$lsn2 =
+ $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
+
+$output = $node_standby1->safe_psql(
+ 'postgres', qq[
+ CALL pg_wal_replay_wait('${lsn2}');
+ SELECT count(*) FROM wait_test;
+]);
+my $output2 = $node_standby1->safe_psql(
+ 'postgres', qq[
+ CALL pg_wal_replay_wait('${lsn2}');
+ SELECT count(*) FROM wait_test;
+]);
+
+# Make sure the current LSN on standby and standby2 are the same as
+# primary's LSN
+ok($output eq 40, "standby1 reached the same LSN as primary");
+ok($output2 eq 40, "standby2 reached the same LSN as primary");
+
+# IV
+# Create a cascading standby waiting LSN
+$backup_name = 'cas_backup';
+$node_standby1->backup($backup_name);
+
+my $cascading_standby = PostgreSQL::Test::Cluster->new('cascading_standby');
+$cascading_standby->init_from_backup($node_standby1, $backup_name,
+ has_streaming => 1,
+ has_restoring => 1);
+
+my $cascading_connstr = $node_standby1->connstr;
+$cascading_standby->append_conf(
+ 'postgresql.conf', qq(
+ hot_standby_feedback = on
+ recovery_min_apply_delay = '${delay}s'
+));
+
+$cascading_standby->start;
+
+# Check that new data is visible after calling pg_wal_replay_wait()
+$node_primary->safe_psql('postgres',
+ "INSERT INTO wait_test VALUES (generate_series(41, 50))");
+$lsn2 =
+ $node_primary->safe_psql('postgres', "SELECT pg_current_wal_insert_lsn()");
+
+$output = $node_standby1->safe_psql(
+ 'postgres', qq[
+ CALL pg_wal_replay_wait('${lsn2}');
+ SELECT count(*) FROM wait_test;
+]);
+$output2 = $cascading_standby->safe_psql(
+ 'postgres', qq[
+ CALL pg_wal_replay_wait('${lsn2}');
+ SELECT count(*) FROM wait_test;
+]);
+
+# Make sure the current LSN on standby and standby2 are the same as
+# primary's LSN
+ok($output eq 50, "standby1 reached the same LSN as primary");
+ok($output2 eq 50, "cascading_standby reached the same LSN as primary");
+
+# V
# Check that waiting for unreachable LSN triggers the timeout. The
# unreachable LSN must be well in advance. So WAL records issued by
# the concurrent autovacuum could not affect that.
@@ -69,29 +146,31 @@ my $lsn3 =
$node_primary->safe_psql('postgres',
"SELECT pg_current_wal_insert_lsn() + 10000000000");
my $stderr;
-$node_standby->safe_psql('postgres',
+$node_standby1->safe_psql('postgres',
"CALL pg_wal_replay_wait('${lsn2}', 10);");
-$node_standby->psql(
+$node_standby1->psql(
'postgres',
"CALL pg_wal_replay_wait('${lsn3}', 1000);",
stderr => \$stderr);
ok( $stderr =~ /timed out while waiting for target LSN/,
"get timeout on waiting for unreachable LSN");
+# VI
# Check that the standby promotion terminates the wait on LSN. Start
# waiting for unreachable LSN then promote. Check the log for the relevant
# error message.
-my $psql_session = $node_standby->background_psql('postgres');
+my $psql_session = $node_standby1->background_psql('postgres');
$psql_session->query_until(
qr/start/, qq[
\\echo start
CALL pg_wal_replay_wait('${lsn3}');
]);
-my $log_offset = -s $node_standby->logfile;
-$node_standby->promote;
-$node_standby->wait_for_log('recovery is not in progress', $log_offset);
+my $log_offset = -s $node_standby1->logfile;
+$node_standby1->promote;
+$node_standby1->wait_for_log('recovery is not in progress', $log_offset);
-$node_standby->stop;
+$node_standby1->stop;
+$node_standby2->stop;
$node_primary->stop;
done_testing();
