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