Debian Codegraph @

src/test/recovery/t/035_standby_logical_decoding.pl

raw
1 # Copyright (c) 2023-2025, PostgreSQL Global Development Group
2
3 # logical decoding on standby : test logical decoding,
4 # recovery conflict and standby promotion.
5
6 use strict;
7 use warnings FATAL => 'all';
8
9 use PostgreSQL::Test::Cluster;
10 use PostgreSQL::Test::Utils;
11 use Time::HiRes qw(usleep);
12 use Test::More;
13
14 if ($ENV{enable_injection_points} ne 'yes')
15 {
16 plan skip_all => 'Injection points not supported by this build';
17 }
18
19 my ($stdout, $stderr, $cascading_stdout, $cascading_stderr, $handle);
20
21 my $node_primary = PostgreSQL::Test::Cluster->new('primary');
22 my $node_standby = PostgreSQL::Test::Cluster->new('standby');
23 my $node_cascading_standby =
24 PostgreSQL::Test::Cluster->new('cascading_standby');
25 my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
26 my $default_timeout = $PostgreSQL::Test::Utils::timeout_default;
27 my $res;
28
29 # Name for the physical slot on primary
30 my $primary_slotname = 'primary_physical';
31 my $standby_physical_slotname = 'standby_physical';
32
33 # Fetch xmin columns from slot's pg_replication_slots row, after waiting for
34 # given boolean condition to be true to ensure we've reached a quiescent state.
35 sub wait_for_xmins
36 {
37 my ($node, $slotname, $check_expr) = @_;
38
39 $node->poll_query_until(
40 'postgres', qq[
41 SELECT $check_expr
42 FROM pg_catalog.pg_replication_slots
43 WHERE slot_name = '$slotname';
44 ]) or die "Timed out waiting for slot xmins to advance";
45 }
46
47 # Create the required logical slots on standby.
48 sub create_logical_slots
49 {
50 my ($node, $slot_prefix) = @_;
51
52 my $active_slot = $slot_prefix . 'activeslot';
53 my $inactive_slot = $slot_prefix . 'inactiveslot';
54 $node->create_logical_slot_on_standby($node_primary, qq($inactive_slot),
55 'testdb');
56 $node->create_logical_slot_on_standby($node_primary, qq($active_slot),
57 'testdb');
58 }
59
60 # Drop the logical slots on standby.
61 sub drop_logical_slots
62 {
63 my ($slot_prefix) = @_;
64 my $active_slot = $slot_prefix . 'activeslot';
65 my $inactive_slot = $slot_prefix . 'inactiveslot';
66
67 $node_standby->psql('postgres',
68 qq[SELECT pg_drop_replication_slot('$inactive_slot')]);
69 $node_standby->psql('postgres',
70 qq[SELECT pg_drop_replication_slot('$active_slot')]);
71 }
72
73 # Acquire one of the standby logical slots created by create_logical_slots().
74 # In case wait is true we are waiting for an active pid on the 'activeslot' slot.
75 # If wait is not true it means we are testing a known failure scenario.
76 sub make_slot_active
77 {
78 my ($node, $slot_prefix, $wait, $to_stdout, $to_stderr) = @_;
79 my $slot_user_handle;
80
81 my $active_slot = $slot_prefix . 'activeslot';
82 $slot_user_handle = IPC::Run::start(
83 [
84 'pg_recvlogical',
85 '--dbname' => $node->connstr('testdb'),
86 '--slot' => $active_slot,
87 '--option' => 'include-xids=0',
88 '--option' => 'skip-empty-xacts=1',
89 '--file' => '-',
90 '--no-loop',
91 '--start',
92 ],
93 '>' => $to_stdout,
94 '2>' => $to_stderr,
95 IPC::Run::timeout($default_timeout));
96
97 if ($wait)
98 {
99 # make sure activeslot is in use
100 $node->poll_query_until('testdb',
101 qq[SELECT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = '$active_slot' AND active_pid IS NOT NULL)]
102 ) or die "slot never became active";
103 }
104 return $slot_user_handle;
105 }
106
107 # Check pg_recvlogical stderr
108 sub check_pg_recvlogical_stderr
109 {
110 my ($slot_user_handle, $check_stderr) = @_;
111 my $return;
112
113 # our client should've terminated in response to the walsender error
114 $slot_user_handle->finish;
115 $return = $?;
116 cmp_ok($return, "!=", 0, "pg_recvlogical exited non-zero");
117 if ($return)
118 {
119 like($stderr, qr/$check_stderr/, 'slot has been invalidated');
120 }
121
122 return 0;
123 }
124
125 # Check if all the slots on standby are dropped. These include the 'activeslot'
126 # that was acquired by make_slot_active(), and the non-active 'inactiveslot'.
127 sub check_slots_dropped
128 {
129 my ($slot_prefix, $slot_user_handle) = @_;
130
131 is($node_standby->slot($slot_prefix . 'inactiveslot')->{'slot_type'},
132 '', 'inactiveslot on standby dropped');
133 is($node_standby->slot($slot_prefix . 'activeslot')->{'slot_type'},
134 '', 'activeslot on standby dropped');
135
136 check_pg_recvlogical_stderr($slot_user_handle, "conflict with recovery");
137 }
138
139 # Change hot_standby_feedback and check xmin and catalog_xmin values.
140 sub change_hot_standby_feedback_and_wait_for_xmins
141 {
142 my ($hsf, $invalidated) = @_;
143
144 $node_standby->append_conf(
145 'postgresql.conf', qq[
146 hot_standby_feedback = $hsf
147 ]);
148
149 $node_standby->reload;
150
151 if ($hsf && $invalidated)
152 {
153 # With hot_standby_feedback on, xmin should advance,
154 # but catalog_xmin should still remain NULL since there is no logical slot.
155 wait_for_xmins($node_primary, $primary_slotname,
156 "xmin IS NOT NULL AND catalog_xmin IS NULL");
157 }
158 elsif ($hsf)
159 {
160 # With hot_standby_feedback on, xmin and catalog_xmin should advance.
161 wait_for_xmins($node_primary, $primary_slotname,
162 "xmin IS NOT NULL AND catalog_xmin IS NOT NULL");
163 }
164 else
165 {
166 # Both should be NULL since hs_feedback is off
167 wait_for_xmins($node_primary, $primary_slotname,
168 "xmin IS NULL AND catalog_xmin IS NULL");
169
170 }
171 }
172
173 # Check reason for conflict in pg_replication_slots.
174 sub check_slots_conflict_reason
175 {
176 my ($slot_prefix, $reason) = @_;
177
178 my $active_slot = $slot_prefix . 'activeslot';
179 my $inactive_slot = $slot_prefix . 'inactiveslot';
180
181 $res = $node_standby->safe_psql(
182 'postgres', qq(
183 select invalidation_reason from pg_replication_slots where slot_name = '$active_slot' and conflicting;)
184 );
185
186 is($res, "$reason", "$active_slot reason for conflict is $reason");
187
188 $res = $node_standby->safe_psql(
189 'postgres', qq(
190 select invalidation_reason from pg_replication_slots where slot_name = '$inactive_slot' and conflicting;)
191 );
192
193 is($res, "$reason", "$inactive_slot reason for conflict is $reason");
194 }
195
196 # Drop the slots, re-create them, change hot_standby_feedback,
197 # check xmin and catalog_xmin values, make slot active and reset stat.
198 sub reactive_slots_change_hfs_and_wait_for_xmins
199 {
200 my ($previous_slot_prefix, $slot_prefix, $hsf, $invalidated) = @_;
201
202 # drop the logical slots
203 drop_logical_slots($previous_slot_prefix);
204
205 # create the logical slots
206 create_logical_slots($node_standby, $slot_prefix);
207
208 change_hot_standby_feedback_and_wait_for_xmins($hsf, $invalidated);
209
210 $handle =
211 make_slot_active($node_standby, $slot_prefix, 1, \$stdout, \$stderr);
212
213 # reset stat: easier to check for confl_active_logicalslot in pg_stat_database_conflicts
214 $node_standby->psql('testdb', q[select pg_stat_reset();]);
215 }
216
217 # Check invalidation in the logfile and in pg_stat_database_conflicts
218 sub check_for_invalidation
219 {
220 my ($slot_prefix, $log_start, $test_name) = @_;
221
222 my $active_slot = $slot_prefix . 'activeslot';
223 my $inactive_slot = $slot_prefix . 'inactiveslot';
224
225 # message should be issued
226 ok( $node_standby->log_contains(
227 "invalidating obsolete replication slot \"$inactive_slot\"",
228 $log_start),
229 "inactiveslot slot invalidation is logged $test_name");
230
231 ok( $node_standby->log_contains(
232 "invalidating obsolete replication slot \"$active_slot\"",
233 $log_start),
234 "activeslot slot invalidation is logged $test_name");
235
236 # Verify that pg_stat_database_conflicts.confl_active_logicalslot has been updated
237 ok( $node_standby->poll_query_until(
238 'postgres',
239 "select (confl_active_logicalslot = 1) from pg_stat_database_conflicts where datname = 'testdb'",
240 't'),
241 'confl_active_logicalslot updated'
242 ) or die "Timed out waiting confl_active_logicalslot to be updated";
243 }
244
245 # Launch $sql query, wait for a new snapshot that has a newer horizon and
246 # launch a VACUUM. $vac_option is the set of options to be passed to the
247 # VACUUM command, $sql the sql to launch before triggering the vacuum and
248 # $to_vac the relation to vacuum.
249 #
250 # Note that the injection_point avoids seeing a xl_running_xacts that could
251 # advance an active replication slot's catalog_xmin. Advancing the active
252 # replication slot's catalog_xmin would break some tests that expect the
253 # active slot to conflict with the catalog xmin horizon.
254 sub wait_until_vacuum_can_remove
255 {
256 my ($vac_option, $sql, $to_vac) = @_;
257
258 # Note that from this point the checkpointer and bgwriter will skip writing
259 # xl_running_xacts record.
260 $node_primary->safe_psql('testdb',
261 "SELECT injection_points_attach('skip-log-running-xacts', 'error');");
262
263 # Get the current xid horizon,
264 my $xid_horizon = $node_primary->safe_psql('testdb',
265 qq[select pg_snapshot_xmin(pg_current_snapshot());]);
266
267 # Launch our sql.
268 $node_primary->safe_psql('testdb', qq[$sql]);
269
270 # Wait until we get a newer horizon.
271 $node_primary->poll_query_until('testdb',
272 "SELECT (select pg_snapshot_xmin(pg_current_snapshot())::text::int - $xid_horizon) > 0"
273 ) or die "new snapshot does not have a newer horizon";
274
275 # Launch the vacuum command and insert into flush_wal (see CREATE
276 # TABLE flush_wal for the reason).
277 $node_primary->safe_psql(
278 'testdb', qq[VACUUM $vac_option verbose $to_vac;
279 INSERT INTO flush_wal DEFAULT VALUES;]);
280
281 $node_primary->wait_for_replay_catchup($node_standby);
282
283 # Resume generating the xl_running_xacts record
284 $node_primary->safe_psql('testdb',
285 "SELECT injection_points_detach('skip-log-running-xacts');");
286 }
287
288 ########################
289 # Initialize primary node
290 ########################
291
292 $node_primary->init(allows_streaming => 1, has_archiving => 1);
293 $node_primary->append_conf(
294 'postgresql.conf', q{
295 wal_level = 'logical'
296 max_replication_slots = 4
297 max_wal_senders = 4
298 autovacuum = off
299 });
300 $node_primary->dump_info;
301 $node_primary->start;
302
303 # Check if the extension injection_points is available, as it may be
304 # possible that this script is run with installcheck, where the module
305 # would not be installed by default.
306 if (!$node_primary->check_extension('injection_points'))
307 {
308 plan skip_all => 'Extension injection_points not installed';
309 }
310
311 $node_primary->psql('postgres', q[CREATE DATABASE testdb]);
312
313 $node_primary->safe_psql('testdb',
314 qq[SELECT * FROM pg_create_physical_replication_slot('$primary_slotname');]
315 );
316
317 # Check conflicting is NULL for physical slot
318 $res = $node_primary->safe_psql(
319 'postgres', qq[
320 SELECT conflicting is null FROM pg_replication_slots where slot_name = '$primary_slotname';]
321 );
322
323 is($res, 't', "Physical slot reports conflicting as NULL");
324
325 my $backup_name = 'b1';
326 $node_primary->backup($backup_name);
327
328 # Some tests need to wait for VACUUM to be replayed. But vacuum does not flush
329 # WAL. An insert into flush_wal outside transaction does guarantee a flush.
330 $node_primary->psql('testdb', q[CREATE TABLE flush_wal();]);
331
332 #######################
333 # Initialize standby node
334 #######################
335
336 $node_standby->init_from_backup(
337 $node_primary, $backup_name,
338 has_streaming => 1,
339 has_restoring => 1);
340 $node_standby->append_conf(
341 'postgresql.conf',
342 qq[primary_slot_name = '$primary_slotname'
343 max_replication_slots = 5]);
344 $node_standby->start;
345 $node_primary->wait_for_replay_catchup($node_standby);
346
347 #######################
348 # Initialize subscriber node
349 #######################
350 $node_subscriber->init;
351 $node_subscriber->start;
352
353 my %psql_subscriber = (
354 'subscriber_stdin' => '',
355 'subscriber_stdout' => '',
356 'subscriber_stderr' => '');
357 $psql_subscriber{run} = IPC::Run::start(
358 [
359 'psql', '--no-psqlrc', '--no-align',
360 '--file' => '-',
361 '--dbname' => $node_subscriber->connstr('postgres')
362 ],
363 '<' => \$psql_subscriber{subscriber_stdin},
364 '>' => \$psql_subscriber{subscriber_stdout},
365 '2>' => \$psql_subscriber{subscriber_stderr},
366 IPC::Run::timeout($default_timeout));
367
368 ##################################################
369 # Test that the standby requires hot_standby to be
370 # enabled for pre-existing logical slots.
371 ##################################################
372
373 # create the logical slots
374 $node_standby->create_logical_slot_on_standby($node_primary, 'restart_test');
375 $node_standby->stop;
376 $node_standby->append_conf('postgresql.conf', qq[hot_standby = off]);
377
378 # Use run_log instead of $node_standby->start because this test expects
379 # that the server ends with an error during startup.
380 run_log(
381 [
382 'pg_ctl',
383 '--pgdata' => $node_standby->data_dir,
384 '--log' => $node_standby->logfile,
385 'start',
386 ]);
387
388 # wait for postgres to terminate
389 foreach my $i (0 .. 10 * $PostgreSQL::Test::Utils::timeout_default)
390 {
391 last if !-f $node_standby->data_dir . '/postmaster.pid';
392 usleep(100_000);
393 }
394
395 # Confirm that the server startup fails with an expected error
396 my $logfile = slurp_file($node_standby->logfile());
397 ok( $logfile =~
398 qr/FATAL: .* logical replication slot ".*" exists on the standby, but "hot_standby" = "off"/,
399 "the standby ends with an error during startup because hot_standby was disabled"
400 );
401 $node_standby->adjust_conf('postgresql.conf', 'hot_standby', 'on');
402 $node_standby->start;
403 $node_standby->safe_psql('postgres',
404 qq[SELECT pg_drop_replication_slot('restart_test')]);
405
406 ##################################################
407 # Test that logical decoding on the standby
408 # behaves correctly.
409 ##################################################
410
411 # create the logical slots
412 create_logical_slots($node_standby, 'behaves_ok_');
413
414 $node_primary->safe_psql('testdb',
415 qq[CREATE TABLE decoding_test(x integer, y text);]);
416 $node_primary->safe_psql('testdb',
417 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(1,10) s;]
418 );
419
420 $node_primary->wait_for_replay_catchup($node_standby);
421
422 my $result = $node_standby->safe_psql('testdb',
423 qq[SELECT pg_logical_slot_get_changes('behaves_ok_activeslot', NULL, NULL);]
424 );
425
426 # test if basic decoding works
427 is(scalar(my @foobar = split /^/m, $result),
428 14, 'Decoding produced 14 rows (2 BEGIN/COMMIT and 10 rows)');
429
430 # Insert some rows and verify that we get the same results from pg_recvlogical
431 # and the SQL interface.
432 $node_primary->safe_psql('testdb',
433 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;]
434 );
435
436 my $expected = q{BEGIN
437 table public.decoding_test: INSERT: x[integer]:1 y[text]:'1'
438 table public.decoding_test: INSERT: x[integer]:2 y[text]:'2'
439 table public.decoding_test: INSERT: x[integer]:3 y[text]:'3'
440 table public.decoding_test: INSERT: x[integer]:4 y[text]:'4'
441 COMMIT};
442
443 $node_primary->wait_for_replay_catchup($node_standby);
444
445 my $stdout_sql = $node_standby->safe_psql('testdb',
446 qq[SELECT data FROM pg_logical_slot_peek_changes('behaves_ok_activeslot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');]
447 );
448
449 is($stdout_sql, $expected, 'got expected output from SQL decoding session');
450
451 my $endpos = $node_standby->safe_psql('testdb',
452 "SELECT lsn FROM pg_logical_slot_peek_changes('behaves_ok_activeslot', NULL, NULL) ORDER BY lsn DESC LIMIT 1;"
453 );
454
455 # Insert some rows after $endpos, which we won't read.
456 $node_primary->safe_psql('testdb',
457 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(5,50) s;]
458 );
459
460 $node_primary->wait_for_replay_catchup($node_standby);
461
462 my $stdout_recv = $node_standby->pg_recvlogical_upto(
463 'testdb', 'behaves_ok_activeslot', $endpos, $default_timeout,
464 'include-xids' => '0',
465 'skip-empty-xacts' => '1');
466 chomp($stdout_recv);
467 is($stdout_recv, $expected,
468 'got same expected output from pg_recvlogical decoding session');
469
470 $node_standby->poll_query_until('testdb',
471 "SELECT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = 'behaves_ok_activeslot' AND active_pid IS NULL)"
472 ) or die "slot never became inactive";
473
474 $stdout_recv = $node_standby->pg_recvlogical_upto(
475 'testdb', 'behaves_ok_activeslot', $endpos, $default_timeout,
476 'include-xids' => '0',
477 'skip-empty-xacts' => '1');
478 chomp($stdout_recv);
479 is($stdout_recv, '', 'pg_recvlogical acknowledged changes');
480
481 $node_primary->safe_psql('postgres', 'CREATE DATABASE otherdb');
482
483 # Wait for catchup to ensure that the new database is visible to other sessions
484 # on the standby.
485 $node_primary->wait_for_replay_catchup($node_standby);
486
487 ($result, $stdout, $stderr) = $node_standby->psql('otherdb',
488 "SELECT lsn FROM pg_logical_slot_peek_changes('behaves_ok_activeslot', NULL, NULL) ORDER BY lsn DESC LIMIT 1;"
489 );
490 ok( $stderr =~
491 m/replication slot "behaves_ok_activeslot" was not created in this database/,
492 "replaying logical slot from another database fails");
493
494 ##################################################
495 # Test that we can subscribe on the standby with the publication
496 # created on the primary.
497 ##################################################
498
499 # Create a table on the primary
500 $node_primary->safe_psql('postgres',
501 "CREATE TABLE tab_rep (a int primary key)");
502
503 # Create a table (same structure) on the subscriber node
504 $node_subscriber->safe_psql('postgres',
505 "CREATE TABLE tab_rep (a int primary key)");
506
507 # Create a publication on the primary
508 $node_primary->safe_psql('postgres',
509 "CREATE PUBLICATION tap_pub for table tab_rep");
510
511 $node_primary->wait_for_replay_catchup($node_standby);
512
513 # Subscribe on the standby
514 my $standby_connstr = $node_standby->connstr . ' dbname=postgres';
515
516 # Not using safe_psql() here as it would wait for activity on the primary
517 # and we wouldn't be able to launch pg_log_standby_snapshot() on the primary
518 # while waiting.
519 # psql_subscriber() allows to not wait synchronously.
520 $psql_subscriber{subscriber_stdin} .= qq[CREATE SUBSCRIPTION tap_sub
521 CONNECTION '$standby_connstr'
522 PUBLICATION tap_pub
523 WITH (copy_data = off);];
524 $psql_subscriber{subscriber_stdin} .= "\n";
525
526 $psql_subscriber{run}->pump_nb();
527
528 # Log the standby snapshot to speed up the subscription creation
529 $node_primary->log_standby_snapshot($node_standby, 'tap_sub');
530
531 # Explicitly shut down psql instance gracefully - to avoid hangs
532 # or worse on windows
533 $psql_subscriber{subscriber_stdin} .= "\\q\n";
534 $psql_subscriber{run}->finish;
535
536 $node_subscriber->wait_for_subscription_sync($node_standby, 'tap_sub');
537
538 # Insert some rows on the primary
539 $node_primary->safe_psql('postgres',
540 qq[INSERT INTO tab_rep select generate_series(1,10);]);
541
542 $node_primary->wait_for_replay_catchup($node_standby);
543 $node_standby->wait_for_catchup('tap_sub');
544
545 # Check that the subscriber can see the rows inserted in the primary
546 $result =
547 $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_rep");
548 is($result, qq(10), 'check replicated inserts after subscription on standby');
549
550 # We do not need the subscription and the subscriber anymore
551 $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub");
552 $node_subscriber->stop;
553
554 # Create the injection_points extension
555 $node_primary->safe_psql('testdb', 'CREATE EXTENSION injection_points;');
556
557 ##################################################
558 # Recovery conflict: Invalidate conflicting slots, including in-use slots
559 # Scenario 1: hot_standby_feedback off and vacuum FULL
560 #
561 # In passing, ensure that replication slot stats are not removed when the
562 # active slot is invalidated, and check that an error occurs when
563 # attempting to alter the invalid slot.
564 ##################################################
565
566 # One way to produce recovery conflict is to create/drop a relation and
567 # launch a vacuum full on pg_class with hot_standby_feedback turned off on
568 # the standby.
569 reactive_slots_change_hfs_and_wait_for_xmins('behaves_ok_', 'vacuum_full_',
570 0, 1);
571
572 # Ensure that replication slot stats are not empty before triggering the
573 # conflict.
574 $node_primary->safe_psql('testdb',
575 qq[INSERT INTO decoding_test(x,y) SELECT 100,'100';]);
576
577 $node_standby->poll_query_until('testdb',
578 qq[SELECT total_txns > 0 FROM pg_stat_replication_slots WHERE slot_name = 'vacuum_full_activeslot']
579 ) or die "replication slot stats of vacuum_full_activeslot not updated";
580
581 # This should trigger the conflict
582 wait_until_vacuum_can_remove(
583 'full', 'CREATE TABLE conflict_test(x integer, y text);
584 DROP TABLE conflict_test;', 'pg_class');
585
586 # Check invalidation in the logfile and in pg_stat_database_conflicts
587 check_for_invalidation('vacuum_full_', 1, 'with vacuum FULL on pg_class');
588
589 # Verify reason for conflict is 'rows_removed' in pg_replication_slots
590 check_slots_conflict_reason('vacuum_full_', 'rows_removed');
591
592 # Attempting to alter an invalidated slot should result in an error
593 ($result, $stdout, $stderr) = $node_standby->psql(
594 'postgres',
595 qq[ALTER_REPLICATION_SLOT vacuum_full_inactiveslot (failover);],
596 replication => 'database');
597 ok( $stderr =~
598 /ERROR: can no longer access replication slot "vacuum_full_inactiveslot"/
599 && $stderr =~
600 /DETAIL: This replication slot has been invalidated due to "rows_removed"./,
601 "invalidated slot cannot be altered");
602
603 # Ensure that replication slot stats are not removed after invalidation.
604 is( $node_standby->safe_psql(
605 'testdb',
606 qq[SELECT total_txns > 0 FROM pg_stat_replication_slots WHERE slot_name = 'vacuum_full_activeslot']
607 ),
608 't',
609 'replication slot stats not removed after invalidation');
610
611 $handle =
612 make_slot_active($node_standby, 'vacuum_full_', 0, \$stdout, \$stderr);
613
614 # We are not able to read from the slot as it has been invalidated
615 check_pg_recvlogical_stderr($handle,
616 "can no longer access replication slot \"vacuum_full_activeslot\"");
617
618 # Attempt to copy an invalidated logical replication slot
619 ($result, $stdout, $stderr) = $node_standby->psql(
620 'postgres',
621 qq[select pg_copy_logical_replication_slot('vacuum_full_inactiveslot', 'vacuum_full_inactiveslot_copy');],
622 replication => 'database');
623 ok( $stderr =~
624 /ERROR: cannot copy invalidated replication slot "vacuum_full_inactiveslot"/,
625 "invalidated slot cannot be copied");
626
627 # Set hot_standby_feedback to on
628 change_hot_standby_feedback_and_wait_for_xmins(1, 1);
629
630 ##################################################
631 # Verify that invalidated logical slots stay invalidated across a restart.
632 ##################################################
633 $node_standby->restart;
634
635 # Verify reason for conflict is retained across a restart.
636 check_slots_conflict_reason('vacuum_full_', 'rows_removed');
637
638 ##################################################
639 # Verify that invalidated logical slots do not lead to retaining WAL.
640 ##################################################
641
642 # Get the restart_lsn from an invalidated slot
643 my $restart_lsn = $node_standby->safe_psql(
644 'postgres',
645 "SELECT restart_lsn FROM pg_replication_slots
646 WHERE slot_name = 'vacuum_full_activeslot' AND conflicting;"
647 );
648
649 chomp($restart_lsn);
650
651 # As pg_walfile_name() can not be executed on the standby,
652 # get the WAL file name associated to this lsn from the primary
653 my $walfile_name = $node_primary->safe_psql('postgres',
654 "SELECT pg_walfile_name('$restart_lsn')");
655
656 chomp($walfile_name);
657
658 # Generate some activity and switch WAL file on the primary
659 $node_primary->advance_wal(1);
660 $node_primary->safe_psql('postgres', "checkpoint;");
661
662 # Wait for the standby to catch up
663 $node_primary->wait_for_replay_catchup($node_standby);
664
665 # Request a checkpoint on the standby to trigger the WAL file(s) removal
666 $node_standby->safe_psql('postgres', 'checkpoint;');
667
668 # Verify that the WAL file has not been retained on the standby
669 my $standby_walfile = $node_standby->data_dir . '/pg_wal/' . $walfile_name;
670 ok(!-f "$standby_walfile",
671 "invalidated logical slots do not lead to retaining WAL");
672
673 ##################################################
674 # Recovery conflict: Invalidate conflicting slots, including in-use slots
675 # Scenario 2: conflict due to row removal with hot_standby_feedback off.
676 ##################################################
677
678 # get the position to search from in the standby logfile
679 my $logstart = -s $node_standby->logfile;
680
681 # One way to produce recovery conflict is to create/drop a relation and
682 # launch a vacuum on pg_class with hot_standby_feedback turned off on the
683 # standby.
684 reactive_slots_change_hfs_and_wait_for_xmins('vacuum_full_', 'row_removal_',
685 0, 1);
686
687 # This should trigger the conflict
688 wait_until_vacuum_can_remove(
689 '', 'CREATE TABLE conflict_test(x integer, y text);
690 DROP TABLE conflict_test;', 'pg_class');
691
692 # Check invalidation in the logfile and in pg_stat_database_conflicts
693 check_for_invalidation('row_removal_', $logstart, 'with vacuum on pg_class');
694
695 # Verify reason for conflict is 'rows_removed' in pg_replication_slots
696 check_slots_conflict_reason('row_removal_', 'rows_removed');
697
698 $handle =
699 make_slot_active($node_standby, 'row_removal_', 0, \$stdout, \$stderr);
700
701 # We are not able to read from the slot as it has been invalidated
702 check_pg_recvlogical_stderr($handle,
703 "can no longer access replication slot \"row_removal_activeslot\"");
704
705 ##################################################
706 # Recovery conflict: Same as Scenario 2 but on a shared catalog table
707 # Scenario 3: conflict due to row removal with hot_standby_feedback off.
708 ##################################################
709
710 # get the position to search from in the standby logfile
711 $logstart = -s $node_standby->logfile;
712
713 # One way to produce recovery conflict on a shared catalog table is to
714 # create/drop a role and launch a vacuum on pg_authid with
715 # hot_standby_feedback turned off on the standby.
716 reactive_slots_change_hfs_and_wait_for_xmins('row_removal_',
717 'shared_row_removal_', 0, 1);
718
719 # Trigger the conflict
720 wait_until_vacuum_can_remove(
721 '', 'CREATE ROLE create_trash;
722 DROP ROLE create_trash;', 'pg_authid');
723
724 # Check invalidation in the logfile and in pg_stat_database_conflicts
725 check_for_invalidation('shared_row_removal_', $logstart,
726 'with vacuum on pg_authid');
727
728 # Verify reason for conflict is 'rows_removed' in pg_replication_slots
729 check_slots_conflict_reason('shared_row_removal_', 'rows_removed');
730
731 $handle = make_slot_active($node_standby, 'shared_row_removal_', 0, \$stdout,
732 \$stderr);
733
734 # We are not able to read from the slot as it has been invalidated
735 check_pg_recvlogical_stderr($handle,
736 "can no longer access replication slot \"shared_row_removal_activeslot\""
737 );
738
739 ##################################################
740 # Recovery conflict: Same as Scenario 2 but on a non catalog table
741 # Scenario 4: No conflict expected.
742 ##################################################
743
744 # get the position to search from in the standby logfile
745 $logstart = -s $node_standby->logfile;
746
747 reactive_slots_change_hfs_and_wait_for_xmins('shared_row_removal_',
748 'no_conflict_', 0, 1);
749
750 # This should not trigger a conflict
751 wait_until_vacuum_can_remove(
752 '', 'CREATE TABLE conflict_test(x integer, y text);
753 INSERT INTO conflict_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;
754 UPDATE conflict_test set x=1, y=1;', 'conflict_test');
755
756 # message should not be issued
757 ok( !$node_standby->log_contains(
758 "invalidating obsolete replication slot \"no_conflict_inactiveslot\"", $logstart),
759 'inactiveslot slot invalidation is not logged with vacuum on conflict_test'
760 );
761
762 ok( !$node_standby->log_contains(
763 "invalidating obsolete replication slot \"no_conflict_activeslot\"", $logstart),
764 'activeslot slot invalidation is not logged with vacuum on conflict_test'
765 );
766
767 # Verify that pg_stat_database_conflicts.confl_active_logicalslot has not been updated
768 ok( $node_standby->poll_query_until(
769 'postgres',
770 "select (confl_active_logicalslot = 0) from pg_stat_database_conflicts where datname = 'testdb'",
771 't'),
772 'confl_active_logicalslot not updated'
773 ) or die "Timed out waiting confl_active_logicalslot to be updated";
774
775 # Verify slots are reported as non conflicting in pg_replication_slots
776 is( $node_standby->safe_psql(
777 'postgres',
778 q[select bool_or(conflicting) from
779 (select conflicting from pg_replication_slots
780 where slot_type = 'logical')]),
781 'f',
782 'Logical slots are reported as non conflicting');
783
784 # Turn hot_standby_feedback back on
785 change_hot_standby_feedback_and_wait_for_xmins(1, 0);
786
787 # Restart the standby node to ensure no slots are still active
788 $node_standby->restart;
789
790 ##################################################
791 # Recovery conflict: Invalidate conflicting slots, including in-use slots
792 # Scenario 5: conflict due to on-access pruning.
793 ##################################################
794
795 # get the position to search from in the standby logfile
796 $logstart = -s $node_standby->logfile;
797
798 # One way to produce recovery conflict is to trigger an on-access pruning
799 # on a relation marked as user_catalog_table.
800 reactive_slots_change_hfs_and_wait_for_xmins('no_conflict_', 'pruning_', 0,
801 0);
802
803 # Injection point avoids seeing a xl_running_xacts. This is required because if
804 # it is generated between the last two updates, then the catalog_xmin of the
805 # active slot could be updated, and hence, the conflict won't occur. See
806 # comments atop wait_until_vacuum_can_remove.
807 $node_primary->safe_psql('testdb',
808 "SELECT injection_points_attach('skip-log-running-xacts', 'error');");
809
810 # This should trigger the conflict
811 $node_primary->safe_psql('testdb',
812 qq[CREATE TABLE prun(id integer, s char(2000)) WITH (fillfactor = 75, user_catalog_table = true);]
813 );
814 $node_primary->safe_psql('testdb', qq[INSERT INTO prun VALUES (1, 'A');]);
815 $node_primary->safe_psql('testdb', qq[UPDATE prun SET s = 'B';]);
816 $node_primary->safe_psql('testdb', qq[UPDATE prun SET s = 'C';]);
817 $node_primary->safe_psql('testdb', qq[UPDATE prun SET s = 'D';]);
818 $node_primary->safe_psql('testdb', qq[UPDATE prun SET s = 'E';]);
819
820 $node_primary->wait_for_replay_catchup($node_standby);
821
822 # Resume generating the xl_running_xacts record
823 $node_primary->safe_psql('testdb',
824 "SELECT injection_points_detach('skip-log-running-xacts');");
825
826 # Check invalidation in the logfile and in pg_stat_database_conflicts
827 check_for_invalidation('pruning_', $logstart, 'with on-access pruning');
828
829 # Verify reason for conflict is 'rows_removed' in pg_replication_slots
830 check_slots_conflict_reason('pruning_', 'rows_removed');
831
832 $handle = make_slot_active($node_standby, 'pruning_', 0, \$stdout, \$stderr);
833
834 # We are not able to read from the slot as it has been invalidated
835 check_pg_recvlogical_stderr($handle,
836 "can no longer access replication slot \"pruning_activeslot\"");
837
838 # Turn hot_standby_feedback back on
839 change_hot_standby_feedback_and_wait_for_xmins(1, 1);
840
841 ##################################################
842 # Recovery conflict: Invalidate conflicting slots, including in-use slots
843 # Scenario 6: incorrect wal_level on primary.
844 ##################################################
845
846 # get the position to search from in the standby logfile
847 $logstart = -s $node_standby->logfile;
848
849 # drop the logical slots
850 drop_logical_slots('pruning_');
851
852 # create the logical slots
853 create_logical_slots($node_standby, 'wal_level_');
854
855 $handle =
856 make_slot_active($node_standby, 'wal_level_', 1, \$stdout, \$stderr);
857
858 # reset stat: easier to check for confl_active_logicalslot in pg_stat_database_conflicts
859 $node_standby->psql('testdb', q[select pg_stat_reset();]);
860
861 # Make primary wal_level replica. This will trigger slot conflict.
862 $node_primary->append_conf(
863 'postgresql.conf', q[
864 wal_level = 'replica'
865 ]);
866 $node_primary->restart;
867
868 $node_primary->wait_for_replay_catchup($node_standby);
869
870 # Check invalidation in the logfile and in pg_stat_database_conflicts
871 check_for_invalidation('wal_level_', $logstart, 'due to wal_level');
872
873 # Verify reason for conflict is 'wal_level_insufficient' in pg_replication_slots
874 check_slots_conflict_reason('wal_level_', 'wal_level_insufficient');
875
876 $handle =
877 make_slot_active($node_standby, 'wal_level_', 0, \$stdout, \$stderr);
878 # We are not able to read from the slot as it requires wal_level >= logical on the primary server
879 check_pg_recvlogical_stderr($handle,
880 "logical decoding on standby requires \"wal_level\" >= \"logical\" on the primary"
881 );
882
883 # Restore primary wal_level
884 $node_primary->append_conf(
885 'postgresql.conf', q[
886 wal_level = 'logical'
887 ]);
888 $node_primary->restart;
889 $node_primary->wait_for_replay_catchup($node_standby);
890
891 $handle =
892 make_slot_active($node_standby, 'wal_level_', 0, \$stdout, \$stderr);
893 # as the slot has been invalidated we should not be able to read
894 check_pg_recvlogical_stderr($handle,
895 "can no longer access replication slot \"wal_level_activeslot\"");
896
897 ##################################################
898 # DROP DATABASE should drop its slots, including active slots.
899 ##################################################
900
901 # drop the logical slots
902 drop_logical_slots('wal_level_');
903
904 # create the logical slots
905 create_logical_slots($node_standby, 'drop_db_');
906
907 $handle = make_slot_active($node_standby, 'drop_db_', 1, \$stdout, \$stderr);
908
909 # Create a slot on a database that would not be dropped. This slot should not
910 # get dropped.
911 $node_standby->create_logical_slot_on_standby($node_primary, 'otherslot',
912 'postgres');
913
914 # dropdb on the primary to verify slots are dropped on standby
915 $node_primary->safe_psql('postgres', q[DROP DATABASE testdb]);
916
917 $node_primary->wait_for_replay_catchup($node_standby);
918
919 is( $node_standby->safe_psql(
920 'postgres',
921 q[SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname = 'testdb')]),
922 'f',
923 'database dropped on standby');
924
925 check_slots_dropped('drop_db', $handle);
926
927 is($node_standby->slot('otherslot')->{'slot_type'},
928 'logical', 'otherslot on standby not dropped');
929
930 # Cleanup : manually drop the slot that was not dropped.
931 $node_standby->psql('postgres',
932 q[SELECT pg_drop_replication_slot('otherslot')]);
933
934 ##################################################
935 # Test standby promotion and logical decoding behavior
936 # after the standby gets promoted.
937 ##################################################
938
939 $node_standby->reload;
940
941 $node_primary->psql('postgres', q[CREATE DATABASE testdb]);
942 $node_primary->safe_psql('testdb',
943 qq[CREATE TABLE decoding_test(x integer, y text);]);
944
945 # Wait for the standby to catchup before initializing the cascading standby
946 $node_primary->wait_for_replay_catchup($node_standby);
947
948 # Create a physical replication slot on the standby.
949 # Keep this step after the "Verify that invalidated logical slots do not lead
950 # to retaining WAL" test (as the physical slot on the standby could prevent the
951 # WAL file removal).
952 $node_standby->safe_psql('testdb',
953 qq[SELECT * FROM pg_create_physical_replication_slot('$standby_physical_slotname');]
954 );
955
956 # Initialize cascading standby node
957 $node_standby->backup($backup_name);
958 $node_cascading_standby->init_from_backup(
959 $node_standby, $backup_name,
960 has_streaming => 1,
961 has_restoring => 1);
962 $node_cascading_standby->append_conf(
963 'postgresql.conf',
964 qq[primary_slot_name = '$standby_physical_slotname'
965 hot_standby_feedback = on]);
966 $node_cascading_standby->start;
967
968 # create the logical slots
969 create_logical_slots($node_standby, 'promotion_');
970
971 # Wait for the cascading standby to catchup before creating the slots
972 $node_standby->wait_for_replay_catchup($node_cascading_standby,
973 $node_primary);
974
975 # create the logical slots on the cascading standby too
976 create_logical_slots($node_cascading_standby, 'promotion_');
977
978 # Make slots actives
979 $handle =
980 make_slot_active($node_standby, 'promotion_', 1, \$stdout, \$stderr);
981 my $cascading_handle =
982 make_slot_active($node_cascading_standby, 'promotion_', 1,
983 \$cascading_stdout, \$cascading_stderr);
984
985 # Insert some rows before the promotion
986 $node_primary->safe_psql('testdb',
987 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;]
988 );
989
990 # Wait for both standbys to catchup
991 $node_primary->wait_for_replay_catchup($node_standby);
992 $node_standby->wait_for_replay_catchup($node_cascading_standby,
993 $node_primary);
994
995 # promote
996 $node_standby->promote;
997
998 # insert some rows on promoted standby
999 $node_standby->safe_psql('testdb',
1000 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(5,7) s;]
1001 );
1002
1003 # Wait for the cascading standby to catchup
1004 $node_standby->wait_for_replay_catchup($node_cascading_standby);
1005
1006 $expected = q{BEGIN
1007 table public.decoding_test: INSERT: x[integer]:1 y[text]:'1'
1008 table public.decoding_test: INSERT: x[integer]:2 y[text]:'2'
1009 table public.decoding_test: INSERT: x[integer]:3 y[text]:'3'
1010 table public.decoding_test: INSERT: x[integer]:4 y[text]:'4'
1011 COMMIT
1012 BEGIN
1013 table public.decoding_test: INSERT: x[integer]:5 y[text]:'5'
1014 table public.decoding_test: INSERT: x[integer]:6 y[text]:'6'
1015 table public.decoding_test: INSERT: x[integer]:7 y[text]:'7'
1016 COMMIT};
1017
1018 # check that we are decoding pre and post promotion inserted rows
1019 $stdout_sql = $node_standby->safe_psql('testdb',
1020 qq[SELECT data FROM pg_logical_slot_peek_changes('promotion_inactiveslot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');]
1021 );
1022
1023 is($stdout_sql, $expected,
1024 'got expected output from SQL decoding session on promoted standby');
1025
1026 # check that we are decoding pre and post promotion inserted rows
1027 # with pg_recvlogical that has started before the promotion
1028 my $pump_timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
1029
1030 ok(pump_until($handle, $pump_timeout, \$stdout, qr/^.*COMMIT.*COMMIT$/s),
1031 'got 2 COMMIT from pg_recvlogical output');
1032
1033 chomp($stdout);
1034 is($stdout, $expected,
1035 'got same expected output from pg_recvlogical decoding session');
1036
1037 # check that we are decoding pre and post promotion inserted rows on the cascading standby
1038 $stdout_sql = $node_cascading_standby->safe_psql('testdb',
1039 qq[SELECT data FROM pg_logical_slot_peek_changes('promotion_inactiveslot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');]
1040 );
1041
1042 is($stdout_sql, $expected,
1043 'got expected output from SQL decoding session on cascading standby');
1044
1045 # check that we are decoding pre and post promotion inserted rows
1046 # with pg_recvlogical that has started before the promotion on the cascading standby
1047 ok( pump_until(
1048 $cascading_handle, $pump_timeout,
1049 \$cascading_stdout, qr/^.*COMMIT.*COMMIT$/s),
1050 'got 2 COMMIT from pg_recvlogical output');
1051
1052 chomp($cascading_stdout);
1053 is($cascading_stdout, $expected,
1054 'got same expected output from pg_recvlogical decoding session on cascading standby'
1055 );
1056
1057 ##################################################
1058 # Test that logical decoding on standby correctly handles a timeline
1059 # change during promotion. This relies on an injection point that
1060 # waits between the moment the segments of the old timeline are removed
1061 # and the moment RecoveryInProgress() would set, catching that a WAL
1062 # sender is still able to decode changes across a promotion.
1063 ##################################################
1064
1065 # Create a logical slot on the cascading standby for this test.
1066 $node_cascading_standby->create_logical_slot_on_standby($node_standby,
1067 'race_slot', 'testdb');
1068 $node_cascading_standby->create_logical_slot_on_standby($node_standby,
1069 'race_slot_sql', 'testdb');
1070
1071 $node_standby->safe_psql('testdb',
1072 qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(10,13) s;]
1073 );
1074 $node_standby->wait_for_replay_catchup($node_cascading_standby);
1075
1076 $expected = q{BEGIN
1077 table public.decoding_test: INSERT: x[integer]:10 y[text]:'10'
1078 table public.decoding_test: INSERT: x[integer]:11 y[text]:'11'
1079 table public.decoding_test: INSERT: x[integer]:12 y[text]:'12'
1080 table public.decoding_test: INSERT: x[integer]:13 y[text]:'13'
1081 COMMIT};
1082
1083 $node_standby->safe_psql('testdb', 'CREATE EXTENSION injection_points;');
1084 $node_standby->wait_for_replay_catchup($node_cascading_standby);
1085
1086 # Open a background psql session BEFORE promotion for the SQL decoding
1087 # test.
1088 my $decode_session = $node_cascading_standby->background_psql('testdb');
1089
1090 # Attach injection point to pause startup after WAL segment cleanup
1091 # but before RecoveryInProgress() flips to false.
1092 $node_cascading_standby->safe_psql('testdb',
1093 "SELECT injection_points_attach('promotion-after-wal-segment-cleanup', 'wait');"
1094 );
1095
1096 # Promote, wait for the removal of the segments on the old timeline.
1097 $node_cascading_standby->safe_psql('testdb', "SELECT pg_promote(false)");
1098 $node_cascading_standby->wait_for_event('startup',
1099 'promotion-after-wal-segment-cleanup');
1100
1101 # Start pg_recvlogical.
1102 my ($stdout2, $stderr2);
1103 my $handle2 = IPC::Run::start(
1104 [
1105 'pg_recvlogical',
1106 '--dbname' => $node_cascading_standby->connstr('testdb'),
1107 '--slot' => 'race_slot',
1108 '--option' => 'include-xids=0',
1109 '--option' => 'skip-empty-xacts=1',
1110 '--file' => '-',
1111 '--no-loop',
1112 '--start',
1113 ],
1114 '>' => \$stdout2,
1115 '2>' => \$stderr2,
1116 IPC::Run::timeout($default_timeout));
1117
1118 # Verify that pg_recvlogical successfully decodes the data while startup
1119 # is still paused in the injection point.
1120 $pump_timeout = IPC::Run::timer($default_timeout);
1121 ok( pump_until($handle2, $pump_timeout, \$stdout2, qr/COMMIT/s),
1122 'pg_recvlogical works during promotion timeline switch');
1123 chomp($stdout2);
1124 is($stdout2, $expected,
1125 'got expected output from pg_recvlogical during promotion timeline switch'
1126 );
1127
1128 # Verify SQL decoding.
1129 my $sql_out = $decode_session->query_safe(
1130 "SELECT data FROM pg_logical_slot_peek_changes('race_slot_sql', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1')"
1131 );
1132 is($sql_out, $expected,
1133 'pg_logical_slot_peek_changes works during promotion timeline switch');
1134
1135 # Resume promotion.
1136 $node_cascading_standby->safe_psql('testdb',
1137 "SELECT injection_points_wakeup('promotion-after-wal-segment-cleanup');");
1138
1139 done_testing();