1 # Demonstrate that logical can follow timeline switches.
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:
8 # * By doing a filesystem-level copy of the DB, since pg_basebackup
9 # excludes pg_replslot but we can copy it directly; or
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.
15 # This module uses the first approach to show that timeline following
16 # on a logical slot works.
18 # (For convenience, it also tests some recovery-related operations
26 use Test::More tests => 13;
30 use Scalar::Util qw(blessed);
32 my ($stdout, $stderr, $ret);
34 # Initialize master node
35 my $node_master = get_new_node('master');
36 $node_master->init(allows_streaming => 1, has_archiving => 1);
37 $node_master->append_conf('postgresql.conf', q[
39 max_replication_slots = 3
41 log_min_messages = 'debug2'
42 hot_standby_feedback = on
43 wal_receiver_status_interval = 1
45 $node_master->dump_info;
48 diag "Testing logical timeline following with a filesystem-level copy";
50 $node_master->safe_psql('postgres',
51 "SELECT pg_create_logical_replication_slot('before_basebackup', 'test_decoding');"
53 $node_master->safe_psql('postgres', "CREATE TABLE decoding(blah text);");
54 $node_master->safe_psql('postgres',
55 "INSERT INTO decoding(blah) VALUES ('beforebb');");
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');"
66 $node_master->safe_psql('postgres', 'CHECKPOINT;');
68 my $backup_name = 'b1';
69 $node_master->backup_fs_hot($backup_name);
71 $node_master->safe_psql('postgres',
72 q[SELECT pg_create_physical_replication_slot('phys_slot');]);
74 my $node_replica = get_new_node('replica');
75 $node_replica->init_from_backup(
76 $node_master, $backup_name,
79 $node_replica->append_conf(
80 'recovery.conf', q[primary_slot_name = 'phys_slot']);
84 # If we drop 'dropme' on the master, the standby should drop the
85 # db and associated slot.
86 is($node_master->psql('postgres', 'DROP DATABASE dropme'), 0,
87 'dropped DB with logical slot OK on master');
88 $node_master->wait_for_catchup($node_replica, 'replay', $node_master->lsn('insert'));
89 is($node_replica->safe_psql('postgres', q[SELECT 1 FROM pg_database WHERE datname = 'dropme']), '',
90 'dropped DB dropme on standby');
91 is($node_master->slot('dropme_slot')->{'slot_name'}, undef,
92 'logical slot was actually dropped on standby');
94 # Back to testing failover...
95 $node_master->safe_psql('postgres',
96 "SELECT pg_create_logical_replication_slot('after_basebackup', 'test_decoding');"
98 $node_master->safe_psql('postgres',
99 "INSERT INTO decoding(blah) VALUES ('afterbb');");
100 $node_master->safe_psql('postgres', 'CHECKPOINT;');
102 # Verify that only the before base_backup slot is on the replica
103 $stdout = $node_replica->safe_psql('postgres',
104 'SELECT slot_name FROM pg_replication_slots ORDER BY slot_name');
105 is($stdout, 'before_basebackup',
106 'Expected to find only slot before_basebackup on replica');
108 # Examine the physical slot the replica uses to stream changes
109 # from the master to make sure its hot_standby_feedback
110 # has locked in a catalog_xmin on the physical slot, and that
111 # any xmin is < the catalog_xmin
112 $node_master->poll_query_until('postgres', q[
113 SELECT catalog_xmin IS NOT NULL
114 FROM pg_replication_slots
115 WHERE slot_name = 'phys_slot'
117 my $phys_slot = $node_master->slot('phys_slot');
118 isnt($phys_slot->{'xmin'}, '',
119 'xmin assigned on physical slot of master');
120 isnt($phys_slot->{'catalog_xmin'}, '',
121 'catalog_xmin assigned on physical slot of master');
122 # Ignore wrap-around here, we're on a new cluster:
123 cmp_ok($phys_slot->{'xmin'}, '>=', $phys_slot->{'catalog_xmin'},
124 'xmin on physical slot must not be lower than catalog_xmin');
126 $node_master->safe_psql('postgres', 'CHECKPOINT');
129 $node_master->stop('immediate');
131 $node_replica->promote;
132 print "waiting for replica to come up\n";
133 $node_replica->poll_query_until('postgres',
134 "SELECT NOT pg_is_in_recovery();");
136 $node_replica->safe_psql('postgres',
137 "INSERT INTO decoding(blah) VALUES ('after failover');");
139 # Shouldn't be able to read from slot created after base backup
140 ($ret, $stdout, $stderr) = $node_replica->psql('postgres',
141 "SELECT data FROM pg_logical_slot_peek_changes('after_basebackup', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');"
143 is($ret, 3, 'replaying from after_basebackup slot fails');
146 qr/replication slot "after_basebackup" does not exist/,
147 'after_basebackup slot missing');
149 # Should be able to read from slot created before base backup
150 ($ret, $stdout, $stderr) = $node_replica->psql(
152 "SELECT data FROM pg_logical_slot_peek_changes('before_basebackup', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');",
154 is($ret, 0, 'replay from slot before_basebackup succeeds');
156 my $final_expected_output_bb = q(BEGIN
157 table public.decoding: INSERT: blah[text]:'beforebb'
160 table public.decoding: INSERT: blah[text]:'afterbb'
163 table public.decoding: INSERT: blah[text]:'after failover'
165 is($stdout, $final_expected_output_bb, 'decoded expected data from slot before_basebackup');
166 is($stderr, '', 'replay from slot before_basebackup produces no stderr');
168 # So far we've peeked the slots, so when we fetch the same info over
169 # pg_recvlogical we should get complete results. First, find out the commit lsn
170 # of the last transaction. There's no max(pg_lsn), so:
172 my $endpos = $node_replica->safe_psql('postgres', "SELECT location FROM pg_logical_slot_peek_changes('before_basebackup', NULL, NULL) ORDER BY location DESC LIMIT 1;");
174 # now use the walsender protocol to peek the slot changes and make sure we see
177 $stdout = $node_replica->pg_recvlogical_upto('postgres', 'before_basebackup',
178 $endpos, 30, 'include-xids' => '0', 'skip-empty-xacts' => '1');
180 # walsender likes to add a newline
182 is($stdout, $final_expected_output_bb, 'got same output from walsender via pg_recvlogical on before_basebackup');
184 $node_replica->teardown_node();