| 1 |
|
| 2 |
# Copyright (c) 2021-2025, PostgreSQL Global Development Group |
| 3 |
|
| 4 |
# Demonstrate that logical can follow timeline switches. |
| 5 |
# |
| 6 |
# Logical replication slots can follow timeline switches but it's |
| 7 |
# normally not possible to have a logical slot on a replica where |
| 8 |
# promotion and a timeline switch can occur. The only ways |
| 9 |
# we can create that circumstance are: |
| 10 |
# |
| 11 |
# * By doing a filesystem-level copy of the DB, since pg_basebackup |
| 12 |
# excludes pg_replslot but we can copy it directly; or |
| 13 |
# |
| 14 |
# * by creating a slot directly at the C level on the replica and |
| 15 |
# advancing it as we go using the low level APIs. It can't be done |
| 16 |
# from SQL since logical decoding isn't allowed on replicas. |
| 17 |
# |
| 18 |
# This module uses the first approach to show that timeline following |
| 19 |
# on a logical slot works. |
| 20 |
# |
| 21 |
# (For convenience, it also tests some recovery-related operations |
| 22 |
# on logical slots). |
| 23 |
# |
| 24 |
use strict; |
| 25 |
use warnings FATAL => 'all'; |
| 26 |
|
| 27 |
use PostgreSQL::Test::Cluster; |
| 28 |
use PostgreSQL::Test::Utils; |
| 29 |
use Test::More; |
| 30 |
use File::Copy; |
| 31 |
use Scalar::Util qw(blessed); |
| 32 |
|
| 33 |
my ($stdout, $stderr, $ret); |
| 34 |
|
| 35 |
# Initialize primary node |
| 36 |
my $node_primary = PostgreSQL::Test::Cluster->new('primary'); |
| 37 |
$node_primary->init(allows_streaming => 1, has_archiving => 1); |
| 38 |
$node_primary->append_conf( |
| 39 |
'postgresql.conf', q[ |
| 40 |
wal_level = 'logical' |
| 41 |
max_replication_slots = 3 |
| 42 |
max_wal_senders = 2 |
| 43 |
log_min_messages = 'debug2' |
| 44 |
hot_standby_feedback = on |
| 45 |
wal_receiver_status_interval = 1 |
| 46 |
]); |
| 47 |
$node_primary->dump_info; |
| 48 |
$node_primary->start; |
| 49 |
|
| 50 |
note "testing logical timeline following with a filesystem-level copy"; |
| 51 |
|
| 52 |
$node_primary->safe_psql('postgres', |
| 53 |
"SELECT pg_create_logical_replication_slot('before_basebackup', 'test_decoding');" |
| 54 |
); |
| 55 |
$node_primary->safe_psql('postgres', "CREATE TABLE decoding(blah text);"); |
| 56 |
$node_primary->safe_psql('postgres', |
| 57 |
"INSERT INTO decoding(blah) VALUES ('beforebb');"); |
| 58 |
|
| 59 |
# We also want to verify that DROP DATABASE on a standby with a logical |
| 60 |
# slot works. This isn't strictly related to timeline following, but |
| 61 |
# the only way to get a logical slot on a standby right now is to use |
| 62 |
# the same physical copy trick, so: |
| 63 |
$node_primary->safe_psql('postgres', 'CREATE DATABASE dropme;'); |
| 64 |
$node_primary->safe_psql('dropme', |
| 65 |
"SELECT pg_create_logical_replication_slot('dropme_slot', 'test_decoding');" |
| 66 |
); |
| 67 |
|
| 68 |
$node_primary->safe_psql('postgres', 'CHECKPOINT;'); |
| 69 |
|
| 70 |
my $backup_name = 'b1'; |
| 71 |
$node_primary->stop(); |
| 72 |
$node_primary->backup_fs_cold($backup_name); |
| 73 |
$node_primary->start(); |
| 74 |
|
| 75 |
$node_primary->safe_psql('postgres', |
| 76 |
q[SELECT pg_create_physical_replication_slot('phys_slot');]); |
| 77 |
|
| 78 |
my $node_replica = PostgreSQL::Test::Cluster->new('replica'); |
| 79 |
$node_replica->init_from_backup( |
| 80 |
$node_primary, $backup_name, |
| 81 |
has_streaming => 1, |
| 82 |
has_restoring => 1); |
| 83 |
$node_replica->append_conf('postgresql.conf', |
| 84 |
q[primary_slot_name = 'phys_slot']); |
| 85 |
|
| 86 |
$node_replica->start; |
| 87 |
|
| 88 |
# If we drop 'dropme' on the primary, the standby should drop the |
| 89 |
# db and associated slot. |
| 90 |
is($node_primary->psql('postgres', 'DROP DATABASE dropme'), |
| 91 |
0, 'dropped DB with logical slot OK on primary'); |
| 92 |
$node_primary->wait_for_catchup($node_replica); |
| 93 |
is( $node_replica->safe_psql( |
| 94 |
'postgres', q[SELECT 1 FROM pg_database WHERE datname = 'dropme']), |
| 95 |
'', |
| 96 |
'dropped DB dropme on standby'); |
| 97 |
is($node_replica->slot('dropme_slot')->{'plugin'}, |
| 98 |
'', 'logical slot was actually dropped on standby'); |
| 99 |
|
| 100 |
# Back to testing failover... |
| 101 |
$node_primary->safe_psql('postgres', |
| 102 |
"SELECT pg_create_logical_replication_slot('after_basebackup', 'test_decoding');" |
| 103 |
); |
| 104 |
$node_primary->safe_psql('postgres', |
| 105 |
"INSERT INTO decoding(blah) VALUES ('afterbb');"); |
| 106 |
$node_primary->safe_psql('postgres', 'CHECKPOINT;'); |
| 107 |
|
| 108 |
# Verify that only the before base_backup slot is on the replica |
| 109 |
$stdout = $node_replica->safe_psql('postgres', |
| 110 |
'SELECT slot_name FROM pg_replication_slots ORDER BY slot_name'); |
| 111 |
is($stdout, 'before_basebackup', |
| 112 |
'Expected to find only slot before_basebackup on replica'); |
| 113 |
|
| 114 |
# Examine the physical slot the replica uses to stream changes |
| 115 |
# from the primary to make sure its hot_standby_feedback |
| 116 |
# has locked in a catalog_xmin on the physical slot, and that |
| 117 |
# any xmin is >= the catalog_xmin |
| 118 |
$node_primary->poll_query_until( |
| 119 |
'postgres', q[ |
| 120 |
SELECT catalog_xmin IS NOT NULL |
| 121 |
FROM pg_replication_slots |
| 122 |
WHERE slot_name = 'phys_slot' |
| 123 |
]) or die "slot's catalog_xmin never became set"; |
| 124 |
|
| 125 |
my $phys_slot = $node_primary->slot('phys_slot'); |
| 126 |
isnt($phys_slot->{'xmin'}, '', 'xmin assigned on physical slot of primary'); |
| 127 |
isnt($phys_slot->{'catalog_xmin'}, |
| 128 |
'', 'catalog_xmin assigned on physical slot of primary'); |
| 129 |
|
| 130 |
# Ignore wrap-around here, we're on a new cluster: |
| 131 |
cmp_ok( |
| 132 |
$phys_slot->{'xmin'}, '>=', |
| 133 |
$phys_slot->{'catalog_xmin'}, |
| 134 |
'xmin on physical slot must not be lower than catalog_xmin'); |
| 135 |
|
| 136 |
$node_primary->safe_psql('postgres', 'CHECKPOINT'); |
| 137 |
$node_primary->wait_for_catchup($node_replica, 'write'); |
| 138 |
|
| 139 |
# Boom, crash |
| 140 |
$node_primary->stop('immediate'); |
| 141 |
|
| 142 |
$node_replica->promote; |
| 143 |
|
| 144 |
$node_replica->safe_psql('postgres', |
| 145 |
"INSERT INTO decoding(blah) VALUES ('after failover');"); |
| 146 |
|
| 147 |
# Shouldn't be able to read from slot created after base backup |
| 148 |
($ret, $stdout, $stderr) = $node_replica->psql('postgres', |
| 149 |
"SELECT data FROM pg_logical_slot_peek_changes('after_basebackup', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');" |
| 150 |
); |
| 151 |
is($ret, 3, 'replaying from after_basebackup slot fails'); |
| 152 |
like( |
| 153 |
$stderr, |
| 154 |
qr/replication slot "after_basebackup" does not exist/, |
| 155 |
'after_basebackup slot missing'); |
| 156 |
|
| 157 |
# Should be able to read from slot created before base backup |
| 158 |
($ret, $stdout, $stderr) = $node_replica->psql( |
| 159 |
'postgres', |
| 160 |
"SELECT data FROM pg_logical_slot_peek_changes('before_basebackup', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');", |
| 161 |
timeout => $PostgreSQL::Test::Utils::timeout_default); |
| 162 |
is($ret, 0, 'replay from slot before_basebackup succeeds'); |
| 163 |
|
| 164 |
my $final_expected_output_bb = q(BEGIN |
| 165 |
table public.decoding: INSERT: blah[text]:'beforebb' |
| 166 |
COMMIT |
| 167 |
BEGIN |
| 168 |
table public.decoding: INSERT: blah[text]:'afterbb' |
| 169 |
COMMIT |
| 170 |
BEGIN |
| 171 |
table public.decoding: INSERT: blah[text]:'after failover' |
| 172 |
COMMIT); |
| 173 |
is($stdout, $final_expected_output_bb, |
| 174 |
'decoded expected data from slot before_basebackup'); |
| 175 |
is($stderr, '', 'replay from slot before_basebackup produces no stderr'); |
| 176 |
|
| 177 |
# So far we've peeked the slots, so when we fetch the same info over |
| 178 |
# pg_recvlogical we should get complete results. First, find out the commit lsn |
| 179 |
# of the last transaction. There's no max(pg_lsn), so: |
| 180 |
|
| 181 |
my $endpos = $node_replica->safe_psql('postgres', |
| 182 |
"SELECT lsn FROM pg_logical_slot_peek_changes('before_basebackup', NULL, NULL) ORDER BY lsn DESC LIMIT 1;" |
| 183 |
); |
| 184 |
|
| 185 |
# now use the walsender protocol to peek the slot changes and make sure we see |
| 186 |
# the same results. |
| 187 |
|
| 188 |
$stdout = $node_replica->pg_recvlogical_upto( |
| 189 |
'postgres', 'before_basebackup', |
| 190 |
$endpos, $PostgreSQL::Test::Utils::timeout_default, |
| 191 |
'include-xids' => '0', |
| 192 |
'skip-empty-xacts' => '1'); |
| 193 |
|
| 194 |
# walsender likes to add a newline |
| 195 |
chomp($stdout); |
| 196 |
is($stdout, $final_expected_output_bb, |
| 197 |
'got same output from walsender via pg_recvlogical on before_basebackup'); |
| 198 |
|
| 199 |
$node_replica->teardown_node(); |
| 200 |
|
| 201 |
done_testing(); |