| 1 |
# Copyright (c) 2026, PostgreSQL Global Development Group |
| 2 |
|
| 3 |
# Test that a cascading standby can reconnect to its upstream standby after |
| 4 |
# advancing past the upstream's WAL flush position via archive recovery. |
| 5 |
# |
| 6 |
# Setup: praline -> samurai -> stubble |
| 7 |
# stubble has both streaming (from samurai) and restore_command |
| 8 |
# (from praline's archive). |
| 9 |
# |
| 10 |
# When samurai's walreceiver is stopped and stubble falls back to |
| 11 |
# archive recovery, stubble may advance its recovery position past |
| 12 |
# samurai's replay position. Previously, stubble's walreceiver |
| 13 |
# would fail with "requested starting point is ahead of the WAL flush |
| 14 |
# position" when reconnecting to samurai. |
| 15 |
|
| 16 |
use strict; |
| 17 |
use warnings FATAL => 'all'; |
| 18 |
|
| 19 |
use PostgreSQL::Test::Cluster; |
| 20 |
use PostgreSQL::Test::Utils; |
| 21 |
use Test::More; |
| 22 |
|
| 23 |
# Initialize praline with archiving |
| 24 |
my $praline = PostgreSQL::Test::Cluster->new('praline'); |
| 25 |
$praline->init(allows_streaming => 1, has_archiving => 1); |
| 26 |
$praline->append_conf( |
| 27 |
'postgresql.conf', qq( |
| 28 |
wal_keep_size = 128MB |
| 29 |
checkpoint_timeout = 1h |
| 30 |
)); |
| 31 |
$praline->start; |
| 32 |
|
| 33 |
# Take backup and create samurai (streaming from praline, no archive) |
| 34 |
my $backup_name = 'my_backup'; |
| 35 |
$praline->backup($backup_name); |
| 36 |
|
| 37 |
my $samurai = PostgreSQL::Test::Cluster->new('samurai'); |
| 38 |
$samurai->init_from_backup($praline, $backup_name, has_streaming => 1); |
| 39 |
$samurai->start; |
| 40 |
|
| 41 |
# Wait for samurai to start streaming |
| 42 |
$praline->wait_for_catchup($samurai); |
| 43 |
|
| 44 |
# Take backup from samurai and create stubble |
| 45 |
# stubble streams from samurai AND restores from praline's archive |
| 46 |
$samurai->backup($backup_name); |
| 47 |
|
| 48 |
my $stubble = PostgreSQL::Test::Cluster->new('stubble'); |
| 49 |
$stubble->init_from_backup($samurai, $backup_name, has_streaming => 1); |
| 50 |
$stubble->enable_restoring($praline); |
| 51 |
$stubble->start; |
| 52 |
|
| 53 |
# Generate initial data and wait for full cascade replication |
| 54 |
$praline->safe_psql('postgres', |
| 55 |
"CREATE TABLE test_tab AS SELECT generate_series(1, 1000) AS id"); |
| 56 |
$praline->wait_for_replay_catchup($samurai); |
| 57 |
$samurai->wait_for_replay_catchup($stubble, $praline); |
| 58 |
|
| 59 |
my $result = $stubble->safe_psql('postgres', "SELECT count(*) FROM test_tab"); |
| 60 |
is($result, '1000', 'initial data replicated to stubble'); |
| 61 |
|
| 62 |
# Disconnect samurai from praline by clearing primary_conninfo. |
| 63 |
# This stops samurai's walreceiver, so samurai can no longer receive |
| 64 |
# new WAL. Its GetStandbyFlushRecPtr() will return only replayPtr. |
| 65 |
$samurai->append_conf('postgresql.conf', "primary_conninfo = ''"); |
| 66 |
$samurai->reload; |
| 67 |
|
| 68 |
# Wait for samurai's walreceiver to stop |
| 69 |
$samurai->poll_query_until('postgres', |
| 70 |
"SELECT NOT EXISTS (SELECT 1 FROM pg_stat_wal_receiver)") |
| 71 |
or die "Timed out waiting for samurai walreceiver to stop"; |
| 72 |
|
| 73 |
# Stop stubble cleanly. We'll restart it after generating new WAL |
| 74 |
# so it enters the recovery state machine fresh and tries archive first. |
| 75 |
$stubble->stop; |
| 76 |
|
| 77 |
# Force a checkpoint now so that no background checkpoint can generate |
| 78 |
# extra WAL during the INSERT below and push it across a segment boundary. |
| 79 |
# Combined with checkpoint_timeout = 1h this ensures the new WAL fits |
| 80 |
# within a single segment, keeping the gap within wal_segment_size. |
| 81 |
$praline->safe_psql('postgres', "CHECKPOINT"); |
| 82 |
|
| 83 |
# Generate more WAL on praline |
| 84 |
$praline->safe_psql('postgres', |
| 85 |
"INSERT INTO test_tab SELECT generate_series(1001, 2000)"); |
| 86 |
|
| 87 |
# Force WAL switch and wait for archiving to complete, so that |
| 88 |
# stubble can find the new WAL in the archive when it starts. |
| 89 |
my $walfile = $praline->safe_psql('postgres', |
| 90 |
"SELECT pg_walfile_name(pg_current_wal_lsn())"); |
| 91 |
$praline->safe_psql('postgres', "SELECT pg_switch_wal()"); |
| 92 |
$praline->poll_query_until('postgres', |
| 93 |
"SELECT '$walfile' <= last_archived_wal FROM pg_stat_archiver") |
| 94 |
or die "Timed out waiting for WAL archiving"; |
| 95 |
|
| 96 |
# Rotate stubble's log so we can check just the new log output |
| 97 |
$stubble->rotate_logfile; |
| 98 |
my $stubble_log_offset = -s $stubble->logfile; |
| 99 |
|
| 100 |
# Start stubble. It will: |
| 101 |
# 1. Read new WAL from praline's archive (XLOG_FROM_ARCHIVE) |
| 102 |
# 2. Advance RecPtr past samurai's replay position |
| 103 |
# 3. Try streaming from samurai (XLOG_FROM_STREAM) |
| 104 |
# 4. detect that upstream is behind via |
| 105 |
# IDENTIFY_SYSTEM and wait instead of failing |
| 106 |
$stubble->start; |
| 107 |
|
| 108 |
# Wait for stubble to replay the new data from archive |
| 109 |
$stubble->poll_query_until('postgres', |
| 110 |
"SELECT count(*) >= 2000 FROM test_tab") |
| 111 |
or die "Timed out waiting for stubble to replay archived WAL"; |
| 112 |
|
| 113 |
$result = $stubble->safe_psql('postgres', "SELECT count(*) FROM test_tab"); |
| 114 |
is($result, '2000', 'stubble replayed new data from archive'); |
| 115 |
|
| 116 |
# Wait for walreceiver to hit the upstream-catchup wait event, proving we |
| 117 |
# exercised the START_REPLICATION-ahead-of-upstream path. |
| 118 |
$stubble->wait_for_event('walreceiver', 'WalReceiverUpstreamCatchup'); |
| 119 |
|
| 120 |
# Verify no errors occurred in stubble. |
| 121 |
my $stubble_loglines = |
| 122 |
PostgreSQL::Test::Utils::slurp_file($stubble->logfile, $stubble_log_offset); |
| 123 |
ok( $stubble_loglines !~ m/ERROR/, 'no errors in stubble log'); |
| 124 |
|
| 125 |
# Now restore samurai's streaming from praline so it can catch up |
| 126 |
$samurai->enable_streaming($praline); |
| 127 |
$samurai->reload; |
| 128 |
|
| 129 |
# Wait for samurai to catch up with praline |
| 130 |
$praline->wait_for_replay_catchup($samurai); |
| 131 |
|
| 132 |
# stubble's walreceiver should eventually connect to samurai and |
| 133 |
# resume streaming (once samurai has caught up past stubble's position) |
| 134 |
$samurai->poll_query_until('postgres', |
| 135 |
"SELECT EXISTS (SELECT 1 FROM pg_stat_replication)") |
| 136 |
or die "Timed out waiting for stubble to reconnect to samurai"; |
| 137 |
|
| 138 |
# Verify end-to-end cascade streaming works with new data |
| 139 |
$praline->safe_psql('postgres', |
| 140 |
"INSERT INTO test_tab SELECT generate_series(2001, 3000)"); |
| 141 |
$praline->wait_for_replay_catchup($samurai); |
| 142 |
$samurai->wait_for_replay_catchup($stubble, $praline); |
| 143 |
|
| 144 |
$result = $stubble->safe_psql('postgres', "SELECT count(*) FROM test_tab"); |
| 145 |
is($result, '3000', |
| 146 |
'cascade streaming resumes normally after upstream catches up'); |
| 147 |
|
| 148 |
done_testing(); |