Debian Codegraph @

src/test/recovery/t/001_stream_rep.pl

raw
1
2 # Copyright (c) 2021-2025, PostgreSQL Global Development Group
3
4 # Minimal test testing streaming replication
5 use strict;
6 use warnings FATAL => 'all';
7 use PostgreSQL::Test::Cluster;
8 use PostgreSQL::Test::Utils;
9 use Test::More;
10
11 # Initialize primary node
12 my $node_primary = PostgreSQL::Test::Cluster->new('primary');
13 # A specific role is created to perform some tests related to replication,
14 # and it needs proper authentication configuration.
15 $node_primary->init(
16 allows_streaming => 1,
17 auth_extra => [ '--create-role' => 'repl_role' ]);
18 $node_primary->start;
19 my $backup_name = 'my_backup';
20
21 # Take backup
22 $node_primary->backup($backup_name);
23
24 # Create streaming standby linking to primary
25 my $node_standby_1 = PostgreSQL::Test::Cluster->new('standby_1');
26 $node_standby_1->init_from_backup($node_primary, $backup_name,
27 has_streaming => 1);
28 $node_standby_1->start;
29
30 # Take backup of standby 1 (not mandatory, but useful to check if
31 # pg_basebackup works on a standby).
32 $node_standby_1->backup($backup_name);
33
34 # Take a second backup of the standby while the primary is offline.
35 $node_primary->stop;
36 $node_standby_1->backup('my_backup_2');
37 $node_primary->start;
38
39 # Create second standby node linking to standby 1
40 my $node_standby_2 = PostgreSQL::Test::Cluster->new('standby_2');
41 $node_standby_2->init_from_backup($node_standby_1, $backup_name,
42 has_streaming => 1);
43 $node_standby_2->start;
44
45 # Reset IO statistics, for the WAL sender check with pg_stat_io.
46 $node_primary->safe_psql('postgres', "SELECT pg_stat_reset_shared('io')");
47
48 # Create some content on primary and check its presence in standby nodes
49 $node_primary->safe_psql('postgres',
50 "CREATE TABLE tab_int AS SELECT generate_series(1,1002) AS a");
51
52 $node_primary->safe_psql(
53 'postgres', q{
54 CREATE TABLE user_logins(id serial, who text);
55
56 CREATE FUNCTION on_login_proc() RETURNS EVENT_TRIGGER AS $$
57 BEGIN
58 IF NOT pg_is_in_recovery() THEN
59 INSERT INTO user_logins (who) VALUES (session_user);
60 END IF;
61 IF session_user = 'regress_hacker' THEN
62 RAISE EXCEPTION 'You are not welcome!';
63 END IF;
64 END;
65 $$ LANGUAGE plpgsql SECURITY DEFINER;
66
67 CREATE EVENT TRIGGER on_login_trigger ON login EXECUTE FUNCTION on_login_proc();
68 ALTER EVENT TRIGGER on_login_trigger ENABLE ALWAYS;
69 });
70
71 # Wait for standbys to catch up
72 $node_primary->wait_for_replay_catchup($node_standby_1);
73 $node_standby_1->wait_for_replay_catchup($node_standby_2, $node_primary);
74
75 my $result =
76 $node_standby_1->safe_psql('postgres', "SELECT count(*) FROM tab_int");
77 print "standby 1: $result\n";
78 is($result, qq(1002), 'check streamed content on standby 1');
79
80 $result =
81 $node_standby_2->safe_psql('postgres', "SELECT count(*) FROM tab_int");
82 print "standby 2: $result\n";
83 is($result, qq(1002), 'check streamed content on standby 2');
84
85 # Likewise, but for a sequence
86 $node_primary->safe_psql('postgres',
87 "CREATE SEQUENCE seq1; SELECT nextval('seq1')");
88
89 # Wait for standbys to catch up
90 $node_primary->wait_for_replay_catchup($node_standby_1);
91 $node_standby_1->wait_for_replay_catchup($node_standby_2, $node_primary);
92
93 $result = $node_standby_1->safe_psql('postgres', "SELECT * FROM seq1");
94 print "standby 1: $result\n";
95 is($result, qq(33|0|t), 'check streamed sequence content on standby 1');
96
97 $result = $node_standby_2->safe_psql('postgres', "SELECT * FROM seq1");
98 print "standby 2: $result\n";
99 is($result, qq(33|0|t), 'check streamed sequence content on standby 2');
100
101 # Check pg_sequence_last_value() returns NULL for unlogged sequence on standby
102 $node_primary->safe_psql('postgres',
103 "CREATE UNLOGGED SEQUENCE ulseq; SELECT nextval('ulseq')");
104 $node_primary->wait_for_replay_catchup($node_standby_1);
105 is( $node_standby_1->safe_psql(
106 'postgres',
107 "SELECT pg_sequence_last_value('ulseq'::regclass) IS NULL"),
108 't',
109 'pg_sequence_last_value() on unlogged sequence on standby 1');
110
111 # Check that only READ-only queries can run on standbys
112 is($node_standby_1->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
113 3, 'read-only queries on standby 1');
114 is($node_standby_2->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
115 3, 'read-only queries on standby 2');
116
117 # Tests for connection parameter target_session_attrs
118 note "testing connection parameter \"target_session_attrs\"";
119
120 # Attempt to connect to $node1, then $node2, using target_session_attrs=$mode.
121 # Expect to connect to $target_node (undef for failure) with given $status.
122 sub test_target_session_attrs
123 {
124 local $Test::Builder::Level = $Test::Builder::Level + 1;
125
126 my $node1 = shift;
127 my $node2 = shift;
128 my $target_node = shift;
129 my $mode = shift;
130 my $status = shift;
131
132 my $node1_host = $node1->host;
133 my $node1_port = $node1->port;
134 my $node1_name = $node1->name;
135 my $node2_host = $node2->host;
136 my $node2_port = $node2->port;
137 my $node2_name = $node2->name;
138 my $target_port = undef;
139 $target_port = $target_node->port if (defined $target_node);
140 my $target_name = undef;
141 $target_name = $target_node->name if (defined $target_node);
142
143 # Build connection string for connection attempt.
144 my $connstr = "host=$node1_host,$node2_host ";
145 $connstr .= "port=$node1_port,$node2_port ";
146 $connstr .= "target_session_attrs=$mode";
147
148 # Attempt to connect, and if successful, get the server port number
149 # we connected to. Note we must pass the SQL command via the command
150 # line not stdin, else Perl may spit up trying to write to stdin of
151 # an already-failed psql process.
152 my ($ret, $stdout, $stderr) = $node1->psql(
153 'postgres',
154 undef,
155 extra_params => [
156 '--dbname' => $connstr,
157 '--command' => 'SHOW port;',
158 ]);
159 if ($status == 0)
160 {
161 is( $status == $ret && $stdout eq $target_port,
162 1,
163 "connect to node $target_name with mode \"$mode\" and $node1_name,$node2_name listed"
164 );
165 }
166 else
167 {
168 print "status = $status\n";
169 print "ret = $ret\n";
170 print "stdout = $stdout\n";
171 print "stderr = $stderr\n";
172
173 is( $status == $ret && !defined $target_node,
174 1,
175 "fail to connect with mode \"$mode\" and $node1_name,$node2_name listed"
176 );
177 }
178
179 return;
180 }
181
182 # Connect to primary in "read-write" mode with primary,standby1 list.
183 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
184 "read-write", 0);
185
186 # Connect to primary in "read-write" mode with standby1,primary list.
187 test_target_session_attrs($node_standby_1, $node_primary, $node_primary,
188 "read-write", 0);
189
190 # Connect to primary in "any" mode with primary,standby1 list.
191 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
192 "any", 0);
193
194 # Connect to standby1 in "any" mode with standby1,primary list.
195 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
196 "any", 0);
197
198 # Connect to primary in "primary" mode with primary,standby1 list.
199 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
200 "primary", 0);
201
202 # Connect to primary in "primary" mode with standby1,primary list.
203 test_target_session_attrs($node_standby_1, $node_primary, $node_primary,
204 "primary", 0);
205
206 # Connect to standby1 in "read-only" mode with primary,standby1 list.
207 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
208 "read-only", 0);
209
210 # Connect to standby1 in "read-only" mode with standby1,primary list.
211 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
212 "read-only", 0);
213
214 # Connect to primary in "prefer-standby" mode with primary,primary list.
215 test_target_session_attrs($node_primary, $node_primary, $node_primary,
216 "prefer-standby", 0);
217
218 # Connect to standby1 in "prefer-standby" mode with primary,standby1 list.
219 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
220 "prefer-standby", 0);
221
222 # Connect to standby1 in "prefer-standby" mode with standby1,primary list.
223 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
224 "prefer-standby", 0);
225
226 # Connect to standby1 in "standby" mode with primary,standby1 list.
227 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
228 "standby", 0);
229
230 # Connect to standby1 in "standby" mode with standby1,primary list.
231 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
232 "standby", 0);
233
234 # Fail to connect in "read-write" mode with standby1,standby2 list.
235 test_target_session_attrs($node_standby_1, $node_standby_2, undef,
236 "read-write", 2);
237
238 # Fail to connect in "primary" mode with standby1,standby2 list.
239 test_target_session_attrs($node_standby_1, $node_standby_2, undef,
240 "primary", 2);
241
242 # Fail to connect in "read-only" mode with primary,primary list.
243 test_target_session_attrs($node_primary, $node_primary, undef,
244 "read-only", 2);
245
246 # Fail to connect in "standby" mode with primary,primary list.
247 test_target_session_attrs($node_primary, $node_primary, undef, "standby", 2);
248
249 # Test for SHOW commands using a WAL sender connection with a replication
250 # role.
251 note "testing SHOW commands for replication connection";
252
253 $node_primary->psql(
254 'postgres', "
255 CREATE ROLE repl_role REPLICATION LOGIN;
256 GRANT pg_read_all_settings TO repl_role;");
257 my $primary_host = $node_primary->host;
258 my $primary_port = $node_primary->port;
259 my $connstr_common = "host=$primary_host port=$primary_port user=repl_role";
260 my $connstr_rep = "$connstr_common replication=1";
261 my $connstr_db = "$connstr_common replication=database dbname=postgres";
262
263 # Test SHOW ALL
264 my ($ret, $stdout, $stderr) = $node_primary->psql(
265 'postgres', 'SHOW ALL;',
266 on_error_die => 1,
267 extra_params => [ '--dbname' => $connstr_rep ]);
268 ok($ret == 0, "SHOW ALL with replication role and physical replication");
269 ($ret, $stdout, $stderr) = $node_primary->psql(
270 'postgres', 'SHOW ALL;',
271 on_error_die => 1,
272 extra_params => [ '--dbname' => $connstr_db ]);
273 ok($ret == 0, "SHOW ALL with replication role and logical replication");
274
275 # Test SHOW with a user-settable parameter
276 ($ret, $stdout, $stderr) = $node_primary->psql(
277 'postgres', 'SHOW work_mem;',
278 on_error_die => 1,
279 extra_params => [ '--dbname' => $connstr_rep ]);
280 ok( $ret == 0,
281 "SHOW with user-settable parameter, replication role and physical replication"
282 );
283 ($ret, $stdout, $stderr) = $node_primary->psql(
284 'postgres', 'SHOW work_mem;',
285 on_error_die => 1,
286 extra_params => [ '--dbname' => $connstr_db ]);
287 ok( $ret == 0,
288 "SHOW with user-settable parameter, replication role and logical replication"
289 );
290
291 # Test SHOW with a superuser-settable parameter
292 ($ret, $stdout, $stderr) = $node_primary->psql(
293 'postgres', 'SHOW primary_conninfo;',
294 on_error_die => 1,
295 extra_params => [ '--dbname' => $connstr_rep ]);
296 ok( $ret == 0,
297 "SHOW with superuser-settable parameter, replication role and physical replication"
298 );
299 ($ret, $stdout, $stderr) = $node_primary->psql(
300 'postgres', 'SHOW primary_conninfo;',
301 on_error_die => 1,
302 extra_params => [ '--dbname' => $connstr_db ]);
303 ok( $ret == 0,
304 "SHOW with superuser-settable parameter, replication role and logical replication"
305 );
306
307 note "testing READ_REPLICATION_SLOT command for replication connection";
308
309 my $slotname = 'test_read_replication_slot_physical';
310
311 ($ret, $stdout, $stderr) = $node_primary->psql(
312 'postgres',
313 'READ_REPLICATION_SLOT non_existent_slot;',
314 extra_params => [ '--dbname' => $connstr_rep ]);
315 ok($ret == 0, "READ_REPLICATION_SLOT exit code 0 on success");
316 like($stdout, qr/^\|\|$/,
317 "READ_REPLICATION_SLOT returns NULL values if slot does not exist");
318
319 $node_primary->psql(
320 'postgres',
321 "CREATE_REPLICATION_SLOT $slotname PHYSICAL RESERVE_WAL;",
322 extra_params => [ '--dbname' => $connstr_rep ]);
323
324 ($ret, $stdout, $stderr) = $node_primary->psql(
325 'postgres',
326 "READ_REPLICATION_SLOT $slotname;",
327 extra_params => [ '--dbname' => $connstr_rep ]);
328 ok($ret == 0, "READ_REPLICATION_SLOT success with existing slot");
329 like($stdout, qr/^physical\|[^|]*\|1$/,
330 "READ_REPLICATION_SLOT returns tuple with slot information");
331
332 $node_primary->psql(
333 'postgres',
334 "DROP_REPLICATION_SLOT $slotname;",
335 extra_params => [ '--dbname' => $connstr_rep ]);
336
337 note "switching to physical replication slot";
338
339 # Wait for the physical WAL sender to update its IO statistics. This is
340 # done before the next restart, which would force a flush of its stats, and
341 # far enough from the reset done above to not impact the run time.
342 $node_primary->poll_query_until(
343 'postgres',
344 qq[SELECT sum(reads) > 0
345 FROM pg_catalog.pg_stat_io
346 WHERE backend_type = 'walsender'
347 AND object = 'wal']
348 )
349 or die
350 "Timed out while waiting for the walsender to update its IO statistics";
351
352 # Switch to using a physical replication slot. We can do this without a new
353 # backup since physical slots can go backwards if needed. Do so on both
354 # standbys. Since we're going to be testing things that affect the slot state,
355 # also increase the standby feedback interval to ensure timely updates.
356 my ($slotname_1, $slotname_2) = ('standby_1', 'standby_2');
357 $node_primary->append_conf('postgresql.conf', "max_replication_slots = 4");
358 $node_primary->restart;
359 is( $node_primary->psql(
360 'postgres',
361 qq[SELECT pg_create_physical_replication_slot('$slotname_1');]),
362 0,
363 'physical slot created on primary');
364 $node_standby_1->append_conf('postgresql.conf',
365 "primary_slot_name = $slotname_1");
366 $node_standby_1->append_conf('postgresql.conf',
367 "wal_receiver_status_interval = 1");
368 $node_standby_1->append_conf('postgresql.conf', "max_replication_slots = 4");
369 $node_standby_1->restart;
370 is( $node_standby_1->psql(
371 'postgres',
372 qq[SELECT pg_create_physical_replication_slot('$slotname_2');]),
373 0,
374 'physical slot created on intermediate replica');
375 $node_standby_2->append_conf('postgresql.conf',
376 "primary_slot_name = $slotname_2");
377 $node_standby_2->append_conf('postgresql.conf',
378 "wal_receiver_status_interval = 1");
379 # should be able change primary_slot_name without restart
380 # will wait effect in get_slot_xmins above
381 $node_standby_2->reload;
382
383 # Fetch xmin columns from slot's pg_replication_slots row, after waiting for
384 # given boolean condition to be true to ensure we've reached a quiescent state
385 sub get_slot_xmins
386 {
387 my ($node, $slotname, $check_expr) = @_;
388
389 $node->poll_query_until(
390 'postgres', qq[
391 SELECT $check_expr
392 FROM pg_catalog.pg_replication_slots
393 WHERE slot_name = '$slotname';
394 ]) or die "Timed out waiting for slot xmins to advance";
395
396 my $slotinfo = $node->slot($slotname);
397 return ($slotinfo->{'xmin'}, $slotinfo->{'catalog_xmin'});
398 }
399
400 # There's no hot standby feedback and there are no logical slots on either peer
401 # so xmin and catalog_xmin should be null on both slots.
402 my ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
403 "xmin IS NULL AND catalog_xmin IS NULL");
404 is($xmin, '', 'xmin of non-cascaded slot null with no hs_feedback');
405 is($catalog_xmin, '',
406 'catalog xmin of non-cascaded slot null with no hs_feedback');
407
408 ($xmin, $catalog_xmin) = get_slot_xmins($node_standby_1, $slotname_2,
409 "xmin IS NULL AND catalog_xmin IS NULL");
410 is($xmin, '', 'xmin of cascaded slot null with no hs_feedback');
411 is($catalog_xmin, '',
412 'catalog xmin of cascaded slot null with no hs_feedback');
413
414 # Replication still works?
415 $node_primary->safe_psql('postgres', 'CREATE TABLE replayed(val integer);');
416
417 sub replay_check
418 {
419 my $newval = $node_primary->safe_psql('postgres',
420 'INSERT INTO replayed(val) SELECT coalesce(max(val),0) + 1 AS newval FROM replayed RETURNING val'
421 );
422 $node_primary->wait_for_replay_catchup($node_standby_1);
423 $node_standby_1->wait_for_replay_catchup($node_standby_2, $node_primary);
424
425 $node_standby_1->safe_psql('postgres',
426 qq[SELECT 1 FROM replayed WHERE val = $newval])
427 or die "standby_1 didn't replay primary value $newval";
428 $node_standby_2->safe_psql('postgres',
429 qq[SELECT 1 FROM replayed WHERE val = $newval])
430 or die "standby_2 didn't replay standby_1 value $newval";
431 return;
432 }
433
434 replay_check();
435
436 my $evttrig = $node_standby_1->safe_psql('postgres',
437 "SELECT evtname FROM pg_event_trigger WHERE evtevent = 'login'");
438 is($evttrig, 'on_login_trigger', 'Name of login trigger');
439 $evttrig = $node_standby_2->safe_psql('postgres',
440 "SELECT evtname FROM pg_event_trigger WHERE evtevent = 'login'");
441 is($evttrig, 'on_login_trigger', 'Name of login trigger');
442
443 note "enabling hot_standby_feedback";
444
445 # Enable hs_feedback. The slot should gain an xmin. We set the status interval
446 # so we'll see the results promptly.
447 $node_standby_1->safe_psql('postgres',
448 'ALTER SYSTEM SET hot_standby_feedback = on;');
449 $node_standby_1->reload;
450 $node_standby_2->safe_psql('postgres',
451 'ALTER SYSTEM SET hot_standby_feedback = on;');
452 $node_standby_2->reload;
453 replay_check();
454
455 ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
456 "xmin IS NOT NULL AND catalog_xmin IS NULL");
457 isnt($xmin, '', 'xmin of non-cascaded slot non-null with hs feedback');
458 is($catalog_xmin, '',
459 'catalog xmin of non-cascaded slot still null with hs_feedback');
460
461 my ($xmin1, $catalog_xmin1) = get_slot_xmins($node_standby_1, $slotname_2,
462 "xmin IS NOT NULL AND catalog_xmin IS NULL");
463 isnt($xmin1, '', 'xmin of cascaded slot non-null with hs feedback');
464 is($catalog_xmin1, '',
465 'catalog xmin of cascaded slot still null with hs_feedback');
466
467 note "doing some work to advance xmin";
468 $node_primary->safe_psql(
469 'postgres', q{
470 do $$
471 begin
472 for i in 10000..11000 loop
473 -- use an exception block so that each iteration eats an XID
474 begin
475 insert into tab_int values (i);
476 exception
477 when division_by_zero then null;
478 end;
479 end loop;
480 end$$;
481 });
482
483 $node_primary->safe_psql('postgres', 'VACUUM;');
484 $node_primary->safe_psql('postgres', 'CHECKPOINT;');
485
486 my ($xmin2, $catalog_xmin2) =
487 get_slot_xmins($node_primary, $slotname_1, "xmin <> '$xmin'");
488 note "primary slot's new xmin $xmin2, old xmin $xmin";
489 isnt($xmin2, $xmin, 'xmin of non-cascaded slot with hs feedback has changed');
490 is($catalog_xmin2, '',
491 'catalog xmin of non-cascaded slot still null with hs_feedback unchanged'
492 );
493
494 ($xmin2, $catalog_xmin2) =
495 get_slot_xmins($node_standby_1, $slotname_2, "xmin <> '$xmin1'");
496 note "standby_1 slot's new xmin $xmin2, old xmin $xmin1";
497 isnt($xmin2, $xmin1, 'xmin of cascaded slot with hs feedback has changed');
498 is($catalog_xmin2, '',
499 'catalog xmin of cascaded slot still null with hs_feedback unchanged');
500
501 note "disabling hot_standby_feedback";
502
503 # Disable hs_feedback. Xmin should be cleared.
504 $node_standby_1->safe_psql('postgres',
505 'ALTER SYSTEM SET hot_standby_feedback = off;');
506 $node_standby_1->reload;
507 $node_standby_2->safe_psql('postgres',
508 'ALTER SYSTEM SET hot_standby_feedback = off;');
509 $node_standby_2->reload;
510 replay_check();
511
512 ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
513 "xmin IS NULL AND catalog_xmin IS NULL");
514 is($xmin, '', 'xmin of non-cascaded slot null with hs feedback reset');
515 is($catalog_xmin, '',
516 'catalog xmin of non-cascaded slot still null with hs_feedback reset');
517
518 ($xmin, $catalog_xmin) = get_slot_xmins($node_standby_1, $slotname_2,
519 "xmin IS NULL AND catalog_xmin IS NULL");
520 is($xmin, '', 'xmin of cascaded slot null with hs feedback reset');
521 is($catalog_xmin, '',
522 'catalog xmin of cascaded slot still null with hs_feedback reset');
523
524 note "check change primary_conninfo without restart";
525 $node_standby_2->append_conf('postgresql.conf', "primary_slot_name = ''");
526 $node_standby_2->enable_streaming($node_primary);
527 $node_standby_2->reload;
528
529 # The WAL receiver should have generated some IO statistics.
530 my $stats_reads = $node_standby_1->safe_psql(
531 'postgres',
532 qq{SELECT sum(writes) > 0 FROM pg_stat_io
533 WHERE backend_type = 'walreceiver' AND object = 'wal'});
534 is($stats_reads, 't', "WAL receiver generates statistics for WAL writes");
535
536 # be sure do not streaming from cascade
537 $node_standby_1->stop;
538
539 my $newval = $node_primary->safe_psql('postgres',
540 'INSERT INTO replayed(val) SELECT coalesce(max(val),0) + 1 AS newval FROM replayed RETURNING val'
541 );
542 $node_primary->wait_for_catchup($node_standby_2);
543 my $is_replayed = $node_standby_2->safe_psql('postgres',
544 qq[SELECT 1 FROM replayed WHERE val = $newval]);
545 is($is_replayed, qq(1), "standby_2 didn't replay primary value $newval");
546
547 # Drop any existing slots on the primary, for the follow-up tests.
548 $node_primary->safe_psql('postgres',
549 "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots;");
550
551 # Test physical slot advancing and its durability. Create a new slot on
552 # the primary, not used by any of the standbys. This reserves WAL at creation.
553 my $phys_slot = 'phys_slot';
554 $node_primary->safe_psql('postgres',
555 "SELECT pg_create_physical_replication_slot('$phys_slot', true);");
556 # Generate some WAL, and switch to a new segment, used to check that
557 # the previous segment is correctly getting recycled as the slot advancing
558 # would recompute the minimum LSN calculated across all slots.
559 my $segment_removed = $node_primary->safe_psql('postgres',
560 'SELECT pg_walfile_name(pg_current_wal_lsn())');
561 chomp($segment_removed);
562 $node_primary->advance_wal(1);
563 my $current_lsn =
564 $node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
565 chomp($current_lsn);
566 my $psql_rc = $node_primary->psql('postgres',
567 "SELECT pg_replication_slot_advance('$phys_slot', '$current_lsn'::pg_lsn);"
568 );
569 is($psql_rc, '0', 'slot advancing with physical slot');
570 my $phys_restart_lsn_pre = $node_primary->safe_psql('postgres',
571 "SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$phys_slot';"
572 );
573 chomp($phys_restart_lsn_pre);
574 # Slot advance should persist across clean restarts.
575 $node_primary->restart;
576 my $phys_restart_lsn_post = $node_primary->safe_psql('postgres',
577 "SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$phys_slot';"
578 );
579 chomp($phys_restart_lsn_post);
580 ok( ($phys_restart_lsn_pre cmp $phys_restart_lsn_post) == 0,
581 "physical slot advance persists across restarts");
582
583 # Check if the previous segment gets correctly recycled after the
584 # server stopped cleanly, causing a shutdown checkpoint to be generated.
585 my $primary_data = $node_primary->data_dir;
586 ok(!-f "$primary_data/pg_wal/$segment_removed",
587 "WAL segment $segment_removed recycled after physical slot advancing");
588
589 note "testing pg_backup_start() followed by BASE_BACKUP";
590 my $connstr = $node_primary->connstr('postgres') . " replication=database";
591
592 # This test requires a replication connection with a database, as it mixes
593 # a replication command and a SQL command.
594 $node_primary->command_fails_like(
595 [
596 'psql',
597 '--no-psqlrc',
598 '--command' => "SELECT pg_backup_start('backup', true)",
599 '--command' => 'BASE_BACKUP',
600 '--dbname' => $connstr
601 ],
602 qr/a backup is already in progress in this session/,
603 'BASE_BACKUP cannot run in session already running backup');
604
605 note "testing BASE_BACKUP cancellation";
606
607 my $sigchld_bb_timeout =
608 IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
609
610 # This test requires a replication connection with a database, as it mixes
611 # a replication command and a SQL command. The first BASE_BACKUP is throttled
612 # to give enough room for the cancellation running below. The second command
613 # for pg_backup_stop() should fail.
614 my ($sigchld_bb_stdin, $sigchld_bb_stdout, $sigchld_bb_stderr) = ('', '', '');
615 my $sigchld_bb = IPC::Run::start(
616 [
617 'psql', '--no-psqlrc',
618 '--command' => "BASE_BACKUP (CHECKPOINT 'fast', MAX_RATE 32);",
619 '--command' => 'SELECT pg_backup_stop()',
620 '--dbname' => $connstr
621 ],
622 '<' => \$sigchld_bb_stdin,
623 '>' => \$sigchld_bb_stdout,
624 '2>' => \$sigchld_bb_stderr,
625 $sigchld_bb_timeout);
626
627 # The cancellation is issued once the database files are streamed and
628 # the checkpoint issued at backup start completes.
629 is( $node_primary->poll_query_until(
630 'postgres',
631 "SELECT pg_cancel_backend(a.pid) FROM "
632 . "pg_stat_activity a, pg_stat_progress_basebackup b WHERE "
633 . "a.pid = b.pid AND a.query ~ 'BASE_BACKUP' AND "
634 . "b.phase = 'streaming database files';"),
635 "1",
636 "WAL sender sending base backup killed");
637
638 # The psql command should fail on pg_backup_stop().
639 ok( pump_until(
640 $sigchld_bb, $sigchld_bb_timeout,
641 \$sigchld_bb_stderr, qr/backup is not in progress/),
642 'base backup cleanly canceled');
643 $sigchld_bb->finish();
644
645 done_testing();