Debian Codegraph @

src/test/recovery/t/040_standby_failover_slots_sync.pl

raw
1
2 # Copyright (c) 2024-2025, PostgreSQL Global Development Group
3
4 use strict;
5 use warnings FATAL => 'all';
6 use PostgreSQL::Test::Cluster;
7 use PostgreSQL::Test::Utils;
8 use Test::More;
9
10 ##################################################
11 # Test that when a subscription with failover enabled is created, it will alter
12 # the failover property of the corresponding slot on the publisher.
13 ##################################################
14
15 # Create publisher
16 my $publisher = PostgreSQL::Test::Cluster->new('publisher');
17 # Make sure pg_hba.conf is set up to allow connections from repl_role.
18 # This is only needed on Windows machines that don't use UNIX sockets.
19 $publisher->init(
20 allows_streaming => 'logical',
21 auth_extra => [ '--create-role' => 'repl_role' ]);
22 # Disable autovacuum to avoid generating xid during stats update as otherwise
23 # the new XID could then be replicated to standby at some random point making
24 # slots at primary lag behind standby during slot sync.
25 $publisher->append_conf(
26 'postgresql.conf', qq{
27 autovacuum = off
28 max_prepared_transactions = 1
29 });
30 $publisher->start;
31
32 $publisher->safe_psql('postgres',
33 "CREATE PUBLICATION regress_mypub FOR ALL TABLES;");
34
35 my $publisher_connstr = $publisher->connstr . ' dbname=postgres';
36
37 # Create a subscriber node, wait for sync to complete
38 my $subscriber1 = PostgreSQL::Test::Cluster->new('subscriber1');
39 $subscriber1->init;
40 $subscriber1->append_conf('postgresql.conf', 'max_prepared_transactions = 1');
41 $subscriber1->start;
42
43 # Capture the time before the logical failover slot is created on the
44 # primary. We later call this publisher as primary anyway.
45 my $slot_creation_time_on_primary = $publisher->safe_psql(
46 'postgres', qq[
47 SELECT current_timestamp;
48 ]);
49
50 # Create a subscription that enables failover.
51 $subscriber1->safe_psql('postgres',
52 "CREATE SUBSCRIPTION regress_mysub1 CONNECTION '$publisher_connstr' PUBLICATION regress_mypub WITH (slot_name = lsub1_slot, copy_data = false, failover = true, enabled = false);"
53 );
54
55 # Confirm that the failover flag on the slot is turned on
56 is( $publisher->safe_psql(
57 'postgres',
58 q{SELECT failover from pg_replication_slots WHERE slot_name = 'lsub1_slot';}
59 ),
60 "t",
61 'logical slot has failover true on the publisher');
62
63 ##################################################
64 # Test that changing the failover property of a subscription updates the
65 # corresponding failover property of the slot.
66 ##################################################
67
68 # Disable failover
69 $subscriber1->safe_psql('postgres',
70 "ALTER SUBSCRIPTION regress_mysub1 SET (failover = false)");
71
72 # Confirm that the failover flag on the slot has now been turned off
73 is( $publisher->safe_psql(
74 'postgres',
75 q{SELECT failover from pg_replication_slots WHERE slot_name = 'lsub1_slot';}
76 ),
77 "f",
78 'logical slot has failover false on the publisher');
79
80 # Enable failover
81 $subscriber1->safe_psql('postgres',
82 "ALTER SUBSCRIPTION regress_mysub1 SET (failover = true)");
83
84 # Confirm that the failover flag on the slot has now been turned on
85 is( $publisher->safe_psql(
86 'postgres',
87 q{SELECT failover from pg_replication_slots WHERE slot_name = 'lsub1_slot';}
88 ),
89 "t",
90 'logical slot has failover true on the publisher');
91
92 ##################################################
93 # Test that the failover option cannot be changed for enabled subscriptions.
94 ##################################################
95
96 # Enable subscription
97 $subscriber1->safe_psql('postgres',
98 "ALTER SUBSCRIPTION regress_mysub1 ENABLE");
99
100 # Disable failover for enabled subscription
101 my ($result, $stdout, $stderr) = $subscriber1->psql('postgres',
102 "ALTER SUBSCRIPTION regress_mysub1 SET (failover = false)");
103 ok( $stderr =~
104 /ERROR: cannot set option "failover" for enabled subscription/,
105 "altering failover is not allowed for enabled subscription");
106
107 ##################################################
108 # Test that pg_sync_replication_slots() cannot be executed on a non-standby server.
109 ##################################################
110
111 ($result, $stdout, $stderr) =
112 $publisher->psql('postgres', "SELECT pg_sync_replication_slots();");
113 ok( $stderr =~
114 /ERROR: replication slots can only be synchronized to a standby server/,
115 "cannot sync slots on a non-standby server");
116
117 ##################################################
118 # Test logical failover slots corresponding to different plugins can be
119 # synced to the standby.
120 #
121 # Configure standby1 to replicate and synchronize logical slots configured
122 # for failover on the primary
123 #
124 # failover slot lsub1_slot | output_plugin: pgoutput
125 # failover slot lsub2_slot | output_plugin: test_decoding
126 # primary ---> |
127 # physical slot sb1_slot --->| ----> standby1 (connected via streaming replication)
128 # | lsub1_slot, lsub2_slot (synced_slot)
129 ##################################################
130
131 my $primary = $publisher;
132 my $backup_name = 'backup';
133 $primary->backup($backup_name);
134
135 # Create a standby
136 my $standby1 = PostgreSQL::Test::Cluster->new('standby1');
137 $standby1->init_from_backup(
138 $primary, $backup_name,
139 has_streaming => 1,
140 has_restoring => 1);
141
142 # Increase the log_min_messages setting to DEBUG2 on both the standby and
143 # primary to debug test failures, if any.
144 my $connstr_1 = $primary->connstr;
145 $standby1->append_conf(
146 'postgresql.conf', qq(
147 hot_standby_feedback = on
148 primary_slot_name = 'sb1_slot'
149 primary_conninfo = '$connstr_1 dbname=postgres'
150 log_min_messages = 'debug2'
151 ));
152
153 $primary->append_conf('postgresql.conf', "log_min_messages = 'debug2'");
154 $primary->reload;
155
156 # Drop the subscription to prevent further advancement of the restart_lsn for
157 # the lsub1_slot.
158 $subscriber1->safe_psql('postgres', "DROP SUBSCRIPTION regress_mysub1;");
159
160 # To ensure that restart_lsn has moved to a recent WAL position, we re-create
161 # the lsub1_slot.
162 $primary->psql('postgres',
163 q{SELECT pg_create_logical_replication_slot('lsub1_slot', 'pgoutput', false, false, true);}
164 );
165
166 $primary->psql('postgres',
167 q{SELECT pg_create_logical_replication_slot('lsub2_slot', 'test_decoding', false, false, true);}
168 );
169
170 $primary->psql('postgres',
171 q{SELECT pg_create_physical_replication_slot('sb1_slot');});
172
173 # Start the standby so that slot syncing can begin
174 $standby1->start;
175
176 # Capture the inactive_since of the slot from the primary. Note that the slot
177 # will be inactive since the corresponding subscription was dropped.
178 my $inactive_since_on_primary =
179 $primary->validate_slot_inactive_since('lsub1_slot',
180 $slot_creation_time_on_primary);
181
182 # Wait for the standby to catch up so that the standby is not lagging behind
183 # the failover slots.
184 $primary->wait_for_replay_catchup($standby1);
185
186 # Synchronize the primary server slots to the standby.
187 $standby1->safe_psql('postgres', "SELECT pg_sync_replication_slots();");
188
189 # Confirm that the logical failover slots are created on the standby and are
190 # flagged as 'synced'
191 is( $standby1->safe_psql(
192 'postgres',
193 q{SELECT count(*) = 2 FROM pg_replication_slots WHERE slot_name IN ('lsub1_slot', 'lsub2_slot') AND synced AND NOT temporary;}
194 ),
195 "t",
196 'logical slots have synced as true on standby');
197
198 # Capture the inactive_since of the synced slot on the standby
199 my $inactive_since_on_standby =
200 $standby1->validate_slot_inactive_since('lsub1_slot',
201 $slot_creation_time_on_primary);
202
203 # Synced slot on the standby must get its own inactive_since
204 is( $standby1->safe_psql(
205 'postgres',
206 "SELECT '$inactive_since_on_primary'::timestamptz < '$inactive_since_on_standby'::timestamptz;"
207 ),
208 "t",
209 'synchronized slot has got its own inactive_since');
210
211 ##################################################
212 # Test that the synchronized slot will be dropped if the corresponding remote
213 # slot on the primary server has been dropped.
214 ##################################################
215
216 $primary->psql('postgres', "SELECT pg_drop_replication_slot('lsub2_slot');");
217
218 $standby1->safe_psql('postgres', "SELECT pg_sync_replication_slots();");
219
220 is( $standby1->safe_psql(
221 'postgres',
222 q{SELECT count(*) = 0 FROM pg_replication_slots WHERE slot_name = 'lsub2_slot';}
223 ),
224 "t",
225 'synchronized slot has been dropped');
226
227 ##################################################
228 # Test that if the synchronized slot is invalidated while the remote slot is
229 # still valid, the slot will be dropped and re-created on the standby by
230 # executing pg_sync_replication_slots() again.
231 ##################################################
232
233 # Configure the max_slot_wal_keep_size so that the synced slot can be
234 # invalidated due to wal removal.
235 $standby1->append_conf('postgresql.conf', 'max_slot_wal_keep_size = 64kB');
236 $standby1->reload;
237
238 # Generate some activity and switch WAL file on the primary
239 $primary->advance_wal(1);
240 $primary->psql('postgres', "CHECKPOINT");
241 $primary->wait_for_replay_catchup($standby1);
242
243 # Request a checkpoint on the standby to trigger the WAL file(s) removal
244 $standby1->safe_psql('postgres', "CHECKPOINT");
245
246 # Check if the synced slot is invalidated
247 is( $standby1->safe_psql(
248 'postgres',
249 q{SELECT invalidation_reason = 'wal_removed' FROM pg_replication_slots WHERE slot_name = 'lsub1_slot';}
250 ),
251 "t",
252 'synchronized slot has been invalidated');
253
254 # Reset max_slot_wal_keep_size to avoid further wal removal
255 $standby1->append_conf('postgresql.conf', 'max_slot_wal_keep_size = -1');
256 $standby1->reload;
257
258 # Capture the time before the logical failover slot is created on the primary.
259 $slot_creation_time_on_primary = $publisher->safe_psql(
260 'postgres', qq[
261 SELECT current_timestamp;
262 ]);
263
264 # To ensure that restart_lsn has moved to a recent WAL position, we re-create
265 # the lsub1_slot.
266 $primary->safe_psql(
267 'postgres', qq[
268 SELECT pg_drop_replication_slot('lsub1_slot');
269 SELECT pg_create_logical_replication_slot('lsub1_slot', 'pgoutput', false, false, true);
270 ]);
271
272 # Capture the inactive_since of the slot from the primary. Note that the slot
273 # will be inactive since the corresponding subscription was dropped.
274 $inactive_since_on_primary =
275 $primary->validate_slot_inactive_since('lsub1_slot',
276 $slot_creation_time_on_primary);
277
278 # Wait for the standby to catch up so that the standby is not lagging behind
279 # the failover slots.
280 $primary->wait_for_replay_catchup($standby1);
281
282 my $log_offset = -s $standby1->logfile;
283
284 # Synchronize the primary server slots to the standby.
285 $standby1->safe_psql('postgres', "SELECT pg_sync_replication_slots();");
286
287 # Confirm that the invalidated slot has been dropped.
288 $standby1->wait_for_log(
289 qr/dropped replication slot "lsub1_slot" of database with OID [0-9]+/,
290 $log_offset);
291
292 # Confirm that the logical slot has been re-created on the standby and is
293 # flagged as 'synced'
294 is( $standby1->safe_psql(
295 'postgres',
296 q{SELECT invalidation_reason IS NULL AND synced AND NOT temporary FROM pg_replication_slots WHERE slot_name = 'lsub1_slot';}
297 ),
298 "t",
299 'logical slot is re-synced');
300
301 # Reset the log_min_messages to the default value.
302 $primary->append_conf('postgresql.conf', "log_min_messages = 'warning'");
303 $primary->reload;
304
305 $standby1->append_conf('postgresql.conf', "log_min_messages = 'warning'");
306 $standby1->reload;
307
308 ##################################################
309 # Test that a synchronized slot can not be decoded, altered or dropped by the
310 # user
311 ##################################################
312
313 # Attempting to perform logical decoding on a synced slot should result in an error
314 ($result, $stdout, $stderr) = $standby1->psql('postgres',
315 "select * from pg_logical_slot_get_changes('lsub1_slot', NULL, NULL);");
316 ok( $stderr =~
317 /ERROR: cannot use replication slot "lsub1_slot" for logical decoding/,
318 "logical decoding is not allowed on synced slot");
319
320 # Attempting to alter a synced slot should result in an error
321 ($result, $stdout, $stderr) = $standby1->psql(
322 'postgres',
323 qq[ALTER_REPLICATION_SLOT lsub1_slot (failover);],
324 replication => 'database');
325 ok($stderr =~ /ERROR: cannot alter replication slot "lsub1_slot"/,
326 "synced slot on standby cannot be altered");
327
328 # Attempting to drop a synced slot should result in an error
329 ($result, $stdout, $stderr) = $standby1->psql('postgres',
330 "SELECT pg_drop_replication_slot('lsub1_slot');");
331 ok($stderr =~ /ERROR: cannot drop replication slot "lsub1_slot"/,
332 "synced slot on standby cannot be dropped");
333
334 ##################################################
335 # Test that we cannot synchronize slots if dbname is not specified in the
336 # primary_conninfo.
337 ##################################################
338
339 $standby1->append_conf('postgresql.conf', "primary_conninfo = '$connstr_1'");
340 $standby1->reload;
341
342 ($result, $stdout, $stderr) =
343 $standby1->psql('postgres', "SELECT pg_sync_replication_slots();");
344 ok( $stderr =~
345 /ERROR: replication slot synchronization requires "dbname" to be specified in "primary_conninfo"/,
346 "cannot sync slots if dbname is not specified in primary_conninfo");
347
348 # Add the dbname back to the primary_conninfo for further tests
349 $standby1->append_conf('postgresql.conf',
350 "primary_conninfo = '$connstr_1 dbname=postgres'");
351 $standby1->reload;
352
353 ##################################################
354 # Test that we cannot synchronize slots to a cascading standby server.
355 ##################################################
356
357 # Create a cascading standby
358 $backup_name = 'backup2';
359 $standby1->backup($backup_name);
360
361 my $cascading_standby = PostgreSQL::Test::Cluster->new('cascading_standby');
362 $cascading_standby->init_from_backup(
363 $standby1, $backup_name,
364 has_streaming => 1,
365 has_restoring => 1);
366
367 my $cascading_connstr = $standby1->connstr;
368 $cascading_standby->append_conf(
369 'postgresql.conf', qq(
370 hot_standby_feedback = on
371 primary_slot_name = 'cascading_sb_slot'
372 primary_conninfo = '$cascading_connstr dbname=postgres'
373 ));
374
375 $standby1->psql('postgres',
376 q{SELECT pg_create_physical_replication_slot('cascading_sb_slot');});
377
378 $cascading_standby->start;
379
380 ($result, $stdout, $stderr) =
381 $cascading_standby->psql('postgres', "SELECT pg_sync_replication_slots();");
382 ok( $stderr =~
383 /ERROR: cannot synchronize replication slots from a standby server/,
384 "cannot sync slots to a cascading standby server");
385
386 $cascading_standby->stop;
387
388 ##################################################
389 # Create a failover slot and advance the restart_lsn to a position where a
390 # running transaction exists. This setup is for testing that the synced slots
391 # can achieve the consistent snapshot state starting from the restart_lsn
392 # after promotion without losing any data that otherwise would have been
393 # received from the primary.
394 ##################################################
395
396 $primary->safe_psql('postgres',
397 "SELECT pg_create_logical_replication_slot('snap_test_slot', 'test_decoding', false, false, true);"
398 );
399
400 # Wait for the standby to catch up so that the standby is not lagging behind
401 # the failover slots.
402 $primary->wait_for_replay_catchup($standby1);
403
404 $standby1->safe_psql('postgres', "SELECT pg_sync_replication_slots();");
405
406 # Two xl_running_xacts logs are generated here. When decoding the first log, it
407 # only serializes the snapshot, without advancing the restart_lsn to the latest
408 # position. This is because if a transaction is running, the restart_lsn can
409 # only move to a position before that transaction. Hence, the second
410 # xl_running_xacts log is needed, the decoding for which allows the restart_lsn
411 # to advance to the last serialized snapshot's position (the first log).
412 $primary->safe_psql(
413 'postgres', qq(
414 BEGIN;
415 SELECT txid_current();
416 SELECT pg_log_standby_snapshot();
417 COMMIT;
418 BEGIN;
419 SELECT txid_current();
420 SELECT pg_log_standby_snapshot();
421 COMMIT;
422 ));
423
424 # Advance the restart_lsn to the position of the first xl_running_xacts log
425 # generated above. Note that there might be concurrent xl_running_xacts logs
426 # written by the bgwriter, which could cause the position to be advanced to an
427 # unexpected point, but that would be a rare scenario and doesn't affect the
428 # test results.
429 $primary->safe_psql('postgres',
430 "SELECT pg_replication_slot_advance('snap_test_slot', pg_current_wal_lsn());"
431 );
432
433 # Wait for the standby to catch up so that the standby is not lagging behind
434 # the failover slots.
435 $primary->wait_for_replay_catchup($standby1);
436
437 # Log a message that will be consumed on the standby after promotion using the
438 # synced slot. See the test where we promote standby (Promote the standby1 to
439 # primary.)
440 $primary->safe_psql('postgres',
441 "SELECT pg_logical_emit_message(false, 'test', 'test');");
442
443 # Get the confirmed_flush_lsn for the logical slot snap_test_slot on the primary
444 my $confirmed_flush_lsn = $primary->safe_psql('postgres',
445 "SELECT confirmed_flush_lsn from pg_replication_slots WHERE slot_name = 'snap_test_slot';"
446 );
447
448 $standby1->safe_psql('postgres', "SELECT pg_sync_replication_slots();");
449
450 # Verify that confirmed_flush_lsn of snap_test_slot slot is synced to the standby
451 ok( $standby1->poll_query_until(
452 'postgres',
453 "SELECT '$confirmed_flush_lsn' = confirmed_flush_lsn from pg_replication_slots WHERE slot_name = 'snap_test_slot' AND synced AND NOT temporary;"
454 ),
455 'confirmed_flush_lsn of slot snap_test_slot synced to standby');
456
457 ##################################################
458 # Test to confirm that the slot synchronization is protected from malicious
459 # users.
460 ##################################################
461
462 $primary->psql('postgres', "CREATE DATABASE slotsync_test_db");
463 $primary->wait_for_replay_catchup($standby1);
464
465 $standby1->stop;
466
467 # On the primary server, create '=' operator in another schema mapped to
468 # inequality function and redirect the queries to use new operator by setting
469 # search_path. The new '=' operator is created with leftarg as 'bigint' and
470 # right arg as 'int' to redirect 'count(*) = 1' in slot sync's query to use
471 # new '=' operator.
472 $primary->safe_psql(
473 'slotsync_test_db', q{
474
475 CREATE ROLE repl_role REPLICATION LOGIN;
476 CREATE SCHEMA myschema;
477
478 CREATE FUNCTION myschema.myintne(bigint, int) RETURNS bool as $$
479 BEGIN
480 RETURN $1 <> $2;
481 END;
482 $$ LANGUAGE plpgsql immutable;
483
484 CREATE OPERATOR myschema.= (
485 leftarg = bigint,
486 rightarg = int,
487 procedure = myschema.myintne);
488
489 ALTER DATABASE slotsync_test_db SET SEARCH_PATH TO myschema,pg_catalog;
490 GRANT USAGE on SCHEMA myschema TO repl_role;
491 });
492
493 # Start the standby with changed primary_conninfo.
494 $standby1->append_conf('postgresql.conf',
495 "primary_conninfo = '$connstr_1 dbname=slotsync_test_db user=repl_role'");
496 $standby1->start;
497
498 # Run the synchronization function. If the sync flow was not prepared
499 # to handle such attacks, it would have failed during the validation
500 # of the primary_slot_name itself resulting in
501 # ERROR: slot synchronization requires valid primary_slot_name
502 $standby1->safe_psql('slotsync_test_db',
503 "SELECT pg_sync_replication_slots();");
504
505 # Reset the dbname and user in primary_conninfo to the earlier values.
506 $standby1->append_conf('postgresql.conf',
507 "primary_conninfo = '$connstr_1 dbname=postgres'");
508 $standby1->reload;
509
510 # Drop the newly created database.
511 $primary->psql('postgres', q{DROP DATABASE slotsync_test_db;});
512
513 ##################################################
514 # Test to confirm that the slot sync worker exits on invalid GUC(s) and
515 # get started again on valid GUC(s).
516 ##################################################
517
518 $log_offset = -s $standby1->logfile;
519
520 # Enable slot sync worker.
521 $standby1->append_conf('postgresql.conf', qq(sync_replication_slots = on));
522 $standby1->reload;
523
524 # Confirm that the slot sync worker is able to start.
525 $standby1->wait_for_log(qr/slot sync worker started/, $log_offset);
526
527 $log_offset = -s $standby1->logfile;
528
529 # Disable another GUC required for slot sync.
530 $standby1->append_conf('postgresql.conf', qq(hot_standby_feedback = off));
531 $standby1->reload;
532
533 # Confirm that slot sync worker acknowledge the GUC change and logs the msg
534 # about wrong configuration.
535 $standby1->wait_for_log(
536 qr/slot synchronization worker will restart because of a parameter change/,
537 $log_offset);
538 $standby1->wait_for_log(
539 qr/slot synchronization requires "hot_standby_feedback" to be enabled/,
540 $log_offset);
541
542 $log_offset = -s $standby1->logfile;
543
544 # Re-enable the required GUC
545 $standby1->append_conf('postgresql.conf', "hot_standby_feedback = on");
546 $standby1->reload;
547
548 # Confirm that the slot sync worker is able to start now.
549 $standby1->wait_for_log(qr/slot sync worker started/, $log_offset);
550
551 ##################################################
552 # Test to confirm that confirmed_flush_lsn of the logical slot on the primary
553 # is synced to the standby via the slot sync worker.
554 ##################################################
555
556 # Insert data on the primary
557 $primary->safe_psql(
558 'postgres', qq[
559 CREATE TABLE tab_int (a int PRIMARY KEY);
560 INSERT INTO tab_int SELECT generate_series(1, 10);
561 ]);
562
563 # Subscribe to the new table data and wait for it to arrive
564 $subscriber1->safe_psql(
565 'postgres', qq[
566 CREATE TABLE tab_int (a int PRIMARY KEY);
567 CREATE SUBSCRIPTION regress_mysub1 CONNECTION '$publisher_connstr' PUBLICATION regress_mypub WITH (slot_name = lsub1_slot, failover = true, create_slot = false);
568 ]);
569
570 $subscriber1->wait_for_subscription_sync;
571
572 # Do not allow any further advancement of the confirmed_flush_lsn for the
573 # lsub1_slot.
574 $subscriber1->safe_psql('postgres',
575 "ALTER SUBSCRIPTION regress_mysub1 DISABLE");
576
577 # Wait for the replication slot to become inactive on the publisher
578 $primary->poll_query_until(
579 'postgres',
580 "SELECT COUNT(*) FROM pg_catalog.pg_replication_slots WHERE slot_name = 'lsub1_slot' AND active='f'",
581 1);
582
583 # Get the confirmed_flush_lsn for the logical slot lsub1_slot on the primary
584 my $primary_flush_lsn = $primary->safe_psql('postgres',
585 "SELECT confirmed_flush_lsn from pg_replication_slots WHERE slot_name = 'lsub1_slot';"
586 );
587
588 # Confirm that confirmed_flush_lsn of lsub1_slot slot is synced to the standby
589 ok( $standby1->poll_query_until(
590 'postgres',
591 "SELECT '$primary_flush_lsn' = confirmed_flush_lsn from pg_replication_slots WHERE slot_name = 'lsub1_slot' AND synced AND NOT temporary;"
592 ),
593 'confirmed_flush_lsn of slot lsub1_slot synced to standby');
594
595 ##################################################
596 # Test that logical failover replication slots wait for the specified
597 # physical replication slots to receive the changes first. It uses the
598 # following set up:
599 #
600 # (physical standbys)
601 # | ----> standby1 (primary_slot_name = sb1_slot)
602 # | ----> standby2 (primary_slot_name = sb2_slot)
603 # primary ----- |
604 # (logical replication)
605 # | ----> subscriber1 (failover = true, slot_name = lsub1_slot)
606 # | ----> subscriber2 (failover = false, slot_name = lsub2_slot)
607 #
608 # synchronized_standby_slots = 'sb1_slot'
609 #
610 # The setup is configured in such a way that the logical slot of subscriber1 is
611 # enabled for failover, and thus the subscriber1 will wait for the physical
612 # slot of standby1(sb1_slot) to catch up before receiving the decoded changes.
613 ##################################################
614
615 $backup_name = 'backup3';
616
617 $primary->psql('postgres',
618 q{SELECT pg_create_physical_replication_slot('sb2_slot');});
619
620 $primary->backup($backup_name);
621
622 # Create another standby
623 my $standby2 = PostgreSQL::Test::Cluster->new('standby2');
624 $standby2->init_from_backup(
625 $primary, $backup_name,
626 has_streaming => 1,
627 has_restoring => 1);
628 $standby2->append_conf(
629 'postgresql.conf', qq(
630 primary_slot_name = 'sb2_slot'
631 ));
632 $standby2->start;
633 $primary->wait_for_replay_catchup($standby2);
634
635 # Configure primary to disallow any logical slots that have enabled failover
636 # from getting ahead of the specified physical replication slot (sb1_slot).
637 $primary->append_conf(
638 'postgresql.conf', qq(
639 synchronized_standby_slots = 'sb1_slot'
640 ));
641 $primary->reload;
642
643 # Create another subscriber node without enabling failover, wait for sync to
644 # complete
645 my $subscriber2 = PostgreSQL::Test::Cluster->new('subscriber2');
646 $subscriber2->init;
647 $subscriber2->start;
648 $subscriber2->safe_psql(
649 'postgres', qq[
650 CREATE TABLE tab_int (a int PRIMARY KEY);
651 CREATE SUBSCRIPTION regress_mysub2 CONNECTION '$publisher_connstr' PUBLICATION regress_mypub WITH (slot_name = lsub2_slot);
652 ]);
653
654 $subscriber2->wait_for_subscription_sync;
655
656 $subscriber1->safe_psql('postgres',
657 "ALTER SUBSCRIPTION regress_mysub1 ENABLE");
658
659 my $offset = -s $primary->logfile;
660
661 # Stop the standby associated with the specified physical replication slot
662 # (sb1_slot) so that the logical replication slot (lsub1_slot) won't receive
663 # changes until the standby comes up.
664 $standby1->stop;
665
666 # Create some data on the primary
667 my $primary_row_count = 20;
668 $primary->safe_psql('postgres',
669 "INSERT INTO tab_int SELECT generate_series(11, $primary_row_count);");
670
671 # Wait until the standby2 that's still running gets the data from the primary
672 $primary->wait_for_replay_catchup($standby2);
673 $result = $standby2->safe_psql('postgres',
674 "SELECT count(*) = $primary_row_count FROM tab_int;");
675 is($result, 't', "standby2 gets data from primary");
676
677 # Wait for regress_mysub2 to get the data from the primary. This subscription
678 # was not enabled for failover so it gets the data without waiting for any
679 # standbys.
680 $primary->wait_for_catchup('regress_mysub2');
681 $result = $subscriber2->safe_psql('postgres',
682 "SELECT count(*) = $primary_row_count FROM tab_int;");
683 is($result, 't', "subscriber2 gets data from primary");
684
685 # Wait until the primary server logs a warning indicating that it is waiting
686 # for the sb1_slot to catch up.
687 $primary->wait_for_log(
688 qr/replication slot \"sb1_slot\" specified in parameter "synchronized_standby_slots" does not have active_pid/,
689 $offset);
690
691 # The regress_mysub1 was enabled for failover so it doesn't get the data from
692 # primary and keeps waiting for the standby specified in synchronized_standby_slots
693 # (sb1_slot aka standby1).
694 $result =
695 $subscriber1->safe_psql('postgres',
696 "SELECT count(*) <> $primary_row_count FROM tab_int;");
697 is($result, 't',
698 "subscriber1 doesn't get data from primary until standby1 acknowledges changes"
699 );
700
701 # Start the standby specified in synchronized_standby_slots (sb1_slot aka standby1) and
702 # wait for it to catch up with the primary.
703 $standby1->start;
704 $primary->wait_for_replay_catchup($standby1);
705 $result = $standby1->safe_psql('postgres',
706 "SELECT count(*) = $primary_row_count FROM tab_int;");
707 is($result, 't', "standby1 gets data from primary");
708
709 # Now that the standby specified in synchronized_standby_slots is up and running, the
710 # primary can send the decoded changes to the subscription enabled for failover
711 # (i.e. regress_mysub1). While the standby was down, regress_mysub1 didn't
712 # receive any data from the primary. i.e. the primary didn't allow it to go
713 # ahead of standby.
714 $primary->wait_for_catchup('regress_mysub1');
715 $result = $subscriber1->safe_psql('postgres',
716 "SELECT count(*) = $primary_row_count FROM tab_int;");
717 is($result, 't',
718 "subscriber1 gets data from primary after standby1 acknowledges changes");
719
720 ##################################################
721 # Verify that when using pg_logical_slot_get_changes to consume changes from a
722 # logical failover slot, it will also wait for the slots specified in
723 # synchronized_standby_slots to catch up.
724 ##################################################
725
726 # Stop the standby associated with the specified physical replication slot so
727 # that the logical replication slot won't receive changes until the standby
728 # slot's restart_lsn is advanced or the slot is removed from the
729 # synchronized_standby_slots list.
730 $primary->safe_psql('postgres', "TRUNCATE tab_int;");
731 $primary->wait_for_catchup('regress_mysub1');
732 $standby1->stop;
733
734 # Disable the regress_mysub1 to prevent the logical walsender from generating
735 # more warnings.
736 $subscriber1->safe_psql('postgres',
737 "ALTER SUBSCRIPTION regress_mysub1 DISABLE");
738
739 # Wait for the replication slot to become inactive on the publisher
740 $primary->poll_query_until(
741 'postgres',
742 "SELECT COUNT(*) FROM pg_catalog.pg_replication_slots WHERE slot_name = 'lsub1_slot' AND active = 'f'",
743 1);
744
745 # Create a logical 'test_decoding' replication slot with failover enabled
746 $primary->safe_psql('postgres',
747 "SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding', false, false, true);"
748 );
749
750 my $back_q = $primary->background_psql(
751 'postgres',
752 on_error_stop => 0,
753 timeout => $PostgreSQL::Test::Utils::timeout_default);
754
755 # pg_logical_slot_get_changes will be blocked until the standby catches up,
756 # hence it needs to be executed in a background session.
757 $offset = -s $primary->logfile;
758 $back_q->query_until(
759 qr/logical_slot_get_changes/, q(
760 \echo logical_slot_get_changes
761 SELECT pg_logical_slot_get_changes('test_slot', NULL, NULL);
762 ));
763
764 # Wait until the primary server logs a warning indicating that it is waiting
765 # for the sb1_slot to catch up.
766 $primary->wait_for_log(
767 qr/replication slot \"sb1_slot\" specified in parameter "synchronized_standby_slots" does not have active_pid/,
768 $offset);
769
770 # Remove the standby from the synchronized_standby_slots list and reload the
771 # configuration.
772 $primary->adjust_conf('postgresql.conf', 'synchronized_standby_slots', "''");
773 $primary->reload;
774
775 # Since there are no slots in synchronized_standby_slots, the function
776 # pg_logical_slot_get_changes should now return, and the session can be
777 # stopped.
778 $back_q->quit;
779
780 $primary->safe_psql('postgres',
781 "SELECT pg_drop_replication_slot('test_slot');");
782
783 # Add the physical slot (sb1_slot) back to the synchronized_standby_slots for further
784 # tests.
785 $primary->adjust_conf('postgresql.conf', 'synchronized_standby_slots',
786 "'sb1_slot'");
787 $primary->reload;
788
789 # Enable the regress_mysub1 for further tests
790 $subscriber1->safe_psql('postgres',
791 "ALTER SUBSCRIPTION regress_mysub1 ENABLE");
792
793 ##################################################
794 # Test that logical replication will wait for the user-created inactive
795 # physical slot to catch up until we remove the slot from synchronized_standby_slots.
796 ##################################################
797
798 $offset = -s $primary->logfile;
799
800 # Create some data on the primary
801 $primary_row_count = 10;
802 $primary->safe_psql('postgres',
803 "INSERT INTO tab_int SELECT generate_series(1, $primary_row_count);");
804
805 # Wait until the primary server logs a warning indicating that it is waiting
806 # for the sb1_slot to catch up.
807 $primary->wait_for_log(
808 qr/replication slot \"sb1_slot\" specified in parameter "synchronized_standby_slots" does not have active_pid/,
809 $offset);
810
811 # The regress_mysub1 doesn't get the data from primary because the specified
812 # standby slot (sb1_slot) in synchronized_standby_slots is inactive.
813 $result =
814 $subscriber1->safe_psql('postgres', "SELECT count(*) = 0 FROM tab_int;");
815 is($result, 't',
816 "subscriber1 doesn't get data as the sb1_slot doesn't catch up");
817
818 # Remove the standby from the synchronized_standby_slots list and reload the
819 # configuration.
820 $primary->adjust_conf('postgresql.conf', 'synchronized_standby_slots', "''");
821 $primary->reload;
822
823 # Since there are no slots in synchronized_standby_slots, the primary server should now
824 # send the decoded changes to the subscription.
825 $primary->wait_for_catchup('regress_mysub1');
826 $result = $subscriber1->safe_psql('postgres',
827 "SELECT count(*) = $primary_row_count FROM tab_int;");
828 is($result, 't',
829 "subscriber1 gets data from primary after standby1 is removed from the synchronized_standby_slots list"
830 );
831
832 # Add the physical slot (sb1_slot) back to the synchronized_standby_slots for further
833 # tests.
834 $primary->adjust_conf('postgresql.conf', 'synchronized_standby_slots',
835 "'sb1_slot'");
836 $primary->reload;
837
838 ##################################################
839 # Test the synchronization of the two_phase setting for a subscription with the
840 # standby. Additionally, prepare a transaction before enabling the two_phase
841 # option; subsequent tests will verify if it can be correctly replicated to the
842 # subscriber after committing it on the promoted standby.
843 ##################################################
844
845 $standby1->start;
846
847 # Prepare a transaction
848 $primary->safe_psql(
849 'postgres', qq[
850 BEGIN;
851 INSERT INTO tab_int values(0);
852 PREPARE TRANSACTION 'test_twophase_slotsync';
853 ]);
854
855 $primary->wait_for_replay_catchup($standby1);
856 $primary->wait_for_catchup('regress_mysub1');
857
858 # Disable the subscription to allow changing the two_phase option.
859 $subscriber1->safe_psql('postgres',
860 "ALTER SUBSCRIPTION regress_mysub1 DISABLE");
861
862 # Wait for the replication slot to become inactive on the publisher
863 $primary->poll_query_until(
864 'postgres',
865 "SELECT COUNT(*) FROM pg_catalog.pg_replication_slots WHERE slot_name = 'lsub1_slot' AND active='f'",
866 1);
867
868 # Set two_phase to true and enable the subscription
869 $subscriber1->safe_psql(
870 'postgres', qq[
871 ALTER SUBSCRIPTION regress_mysub1 SET (two_phase = true);
872 ALTER SUBSCRIPTION regress_mysub1 ENABLE;
873 ]);
874
875 $primary->wait_for_catchup('regress_mysub1');
876
877 my $two_phase_at = $primary->safe_psql('postgres',
878 "SELECT two_phase_at from pg_replication_slots WHERE slot_name = 'lsub1_slot';"
879 );
880
881 # Confirm that two_phase setting of lsub1_slot slot is synced to the standby
882 ok( $standby1->poll_query_until(
883 'postgres',
884 "SELECT two_phase AND '$two_phase_at' = two_phase_at from pg_replication_slots WHERE slot_name = 'lsub1_slot' AND synced AND NOT temporary;"
885 ),
886 'two_phase setting of slot lsub1_slot synced to standby');
887
888 # Confirm that the prepared transaction is not yet replicated to the
889 # subscriber.
890 $result = $subscriber1->safe_psql('postgres',
891 "SELECT count(*) = 0 FROM pg_prepared_xacts;");
892 is($result, 't',
893 "the prepared transaction is not replicated to the subscriber");
894
895 ##################################################
896 # Promote the standby1 to primary. Confirm that:
897 # a) the slot 'lsub1_slot' and 'snap_test_slot' are retained on the new primary
898 # b) logical replication for regress_mysub1 is resumed successfully after failover
899 # c) changes from the transaction prepared 'test_twophase_slotsync' can be
900 # consumed from the synced slot 'snap_test_slot' once committed on the new
901 # primary.
902 # d) changes can be consumed from the synced slot 'snap_test_slot'
903 ##################################################
904 $primary->wait_for_replay_catchup($standby1);
905
906 # Capture the time before the standby is promoted
907 my $promotion_time_on_primary = $standby1->safe_psql(
908 'postgres', qq[
909 SELECT current_timestamp;
910 ]);
911
912 $standby1->promote;
913
914 # Capture the inactive_since of the synced slot after the promotion.
915 # The expectation here is that the slot gets its inactive_since as part of the
916 # promotion. We do this check before the slot is enabled on the new primary
917 # below, otherwise, the slot gets active setting inactive_since to NULL.
918 my $inactive_since_on_new_primary =
919 $standby1->validate_slot_inactive_since('lsub1_slot',
920 $promotion_time_on_primary);
921
922 is( $standby1->safe_psql(
923 'postgres',
924 "SELECT '$inactive_since_on_new_primary'::timestamptz > '$inactive_since_on_primary'::timestamptz"
925 ),
926 "t",
927 'synchronized slot has got its own inactive_since on the new primary after promotion'
928 );
929
930 # Update subscription with the new primary's connection info
931 my $standby1_conninfo = $standby1->connstr . ' dbname=postgres';
932 $subscriber1->safe_psql('postgres',
933 "ALTER SUBSCRIPTION regress_mysub1 CONNECTION '$standby1_conninfo';");
934
935 # Confirm the synced slot 'lsub1_slot' is retained on the new primary
936 is( $standby1->safe_psql(
937 'postgres',
938 q{SELECT count(*) = 2 FROM pg_replication_slots WHERE slot_name IN ('lsub1_slot', 'snap_test_slot') AND synced AND NOT temporary;}
939 ),
940 't',
941 'synced slot retained on the new primary');
942
943 # Commit the prepared transaction
944 $standby1->safe_psql('postgres', "COMMIT PREPARED 'test_twophase_slotsync';");
945 $standby1->wait_for_catchup('regress_mysub1');
946
947 # Confirm that the prepared transaction is replicated to the subscriber
948 is($subscriber1->safe_psql('postgres', q{SELECT count(*) FROM tab_int;}),
949 "11", 'prepared data replicated from the new primary');
950
951 # Insert data on the new primary
952 $standby1->safe_psql('postgres',
953 "INSERT INTO tab_int SELECT generate_series(11, 20);");
954 $standby1->wait_for_catchup('regress_mysub1');
955
956 # Confirm that data in tab_int replicated on the subscriber
957 is($subscriber1->safe_psql('postgres', q{SELECT count(*) FROM tab_int;}),
958 "21", 'data replicated from the new primary');
959
960 # Consume the data from the snap_test_slot. The synced slot should reach a
961 # consistent point by restoring the snapshot at the restart_lsn serialized
962 # during slot synchronization.
963 $result = $standby1->safe_psql('postgres',
964 "SELECT count(*) FROM pg_logical_slot_get_changes('snap_test_slot', NULL, NULL) WHERE data ~ 'message*';"
965 );
966
967 is($result, '1', "data can be consumed using snap_test_slot");
968
969 done_testing();