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

   1
   2 # Copyright (c) 2021-2022, PostgreSQL Global Development Group
   3
   4 # Minimal test testing streaming replication
   5 use strict;
   6 use warnings;
   7 use PostgreSQL::Test::Cluster;
   8 use PostgreSQL::Test::Utils;
   9 use Test::More tests => 53;
  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 # Create some content on primary and check its presence in standby 1
  46 $node_primary->safe_psql('postgres',
  47         "CREATE TABLE tab_int AS SELECT generate_series(1,1002) AS a");
  48
  49 # Wait for standbys to catch up
  50 $node_primary->wait_for_catchup($node_standby_1, 'replay',
  51         $node_primary->lsn('insert'));
  52 $node_standby_1->wait_for_catchup($node_standby_2, 'replay',
  53         $node_standby_1->lsn('replay'));
  54
  55 my $result =
  56   $node_standby_1->safe_psql('postgres', "SELECT count(*) FROM tab_int");
  57 print "standby 1: $result\n";
  58 is($result, qq(1002), 'check streamed content on standby 1');
  59
  60 $result =
  61   $node_standby_2->safe_psql('postgres', "SELECT count(*) FROM tab_int");
  62 print "standby 2: $result\n";
  63 is($result, qq(1002), 'check streamed content on standby 2');
  64
  65 # Check that only READ-only queries can run on standbys
  66 is($node_standby_1->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
  67         3, 'read-only queries on standby 1');
  68 is($node_standby_2->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
  69         3, 'read-only queries on standby 2');
  70
  71 # Tests for connection parameter target_session_attrs
  72 note "testing connection parameter \"target_session_attrs\"";
  73
  74 # Attempt to connect to $node1, then $node2, using target_session_attrs=$mode.
  75 # Expect to connect to $target_node (undef for failure) with given $status.
  76 sub test_target_session_attrs
  77 {
  78         local $Test::Builder::Level = $Test::Builder::Level + 1;
  79
  80         my $node1       = shift;
  81         my $node2       = shift;
  82         my $target_node = shift;
  83         my $mode        = shift;
  84         my $status      = shift;
  85
  86         my $node1_host  = $node1->host;
  87         my $node1_port  = $node1->port;
  88         my $node1_name  = $node1->name;
  89         my $node2_host  = $node2->host;
  90         my $node2_port  = $node2->port;
  91         my $node2_name  = $node2->name;
  92         my $target_port = undef;
  93         $target_port = $target_node->port if (defined $target_node);
  94         my $target_name = undef;
  95         $target_name = $target_node->name if (defined $target_node);
  96
  97         # Build connection string for connection attempt.
  98         my $connstr = "host=$node1_host,$node2_host ";
  99         $connstr .= "port=$node1_port,$node2_port ";
 100         $connstr .= "target_session_attrs=$mode";
 101
 102         # Attempt to connect, and if successful, get the server port number
 103         # we connected to.  Note we must pass the SQL command via the command
 104         # line not stdin, else Perl may spit up trying to write to stdin of
 105         # an already-failed psql process.
 106         my ($ret, $stdout, $stderr) =
 107           $node1->psql('postgres', undef,
 108                 extra_params => [ '-d', $connstr, '-c', 'SHOW port;' ]);
 109         if ($status == 0)
 110         {
 111                 is( $status == $ret && $stdout eq $target_port,
 112                         1,
 113                         "connect to node $target_name with mode \"$mode\" and $node1_name,$node2_name listed"
 114                 );
 115         }
 116         else
 117         {
 118                 print "status = $status\n";
 119                 print "ret = $ret\n";
 120                 print "stdout = $stdout\n";
 121                 print "stderr = $stderr\n";
 122
 123                 is( $status == $ret && !defined $target_node,
 124                         1,
 125                         "fail to connect with mode \"$mode\" and $node1_name,$node2_name listed"
 126                 );
 127         }
 128
 129         return;
 130 }
 131
 132 # Connect to primary in "read-write" mode with primary,standby1 list.
 133 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
 134         "read-write", 0);
 135
 136 # Connect to primary in "read-write" mode with standby1,primary list.
 137 test_target_session_attrs($node_standby_1, $node_primary, $node_primary,
 138         "read-write", 0);
 139
 140 # Connect to primary in "any" mode with primary,standby1 list.
 141 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
 142         "any", 0);
 143
 144 # Connect to standby1 in "any" mode with standby1,primary list.
 145 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
 146         "any", 0);
 147
 148 # Connect to primary in "primary" mode with primary,standby1 list.
 149 test_target_session_attrs($node_primary, $node_standby_1, $node_primary,
 150         "primary", 0);
 151
 152 # Connect to primary in "primary" mode with standby1,primary list.
 153 test_target_session_attrs($node_standby_1, $node_primary, $node_primary,
 154         "primary", 0);
 155
 156 # Connect to standby1 in "read-only" mode with primary,standby1 list.
 157 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
 158         "read-only", 0);
 159
 160 # Connect to standby1 in "read-only" mode with standby1,primary list.
 161 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
 162         "read-only", 0);
 163
 164 # Connect to primary in "prefer-standby" mode with primary,primary list.
 165 test_target_session_attrs($node_primary, $node_primary, $node_primary,
 166         "prefer-standby", 0);
 167
 168 # Connect to standby1 in "prefer-standby" mode with primary,standby1 list.
 169 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
 170         "prefer-standby", 0);
 171
 172 # Connect to standby1 in "prefer-standby" mode with standby1,primary list.
 173 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
 174         "prefer-standby", 0);
 175
 176 # Connect to standby1 in "standby" mode with primary,standby1 list.
 177 test_target_session_attrs($node_primary, $node_standby_1, $node_standby_1,
 178         "standby", 0);
 179
 180 # Connect to standby1 in "standby" mode with standby1,primary list.
 181 test_target_session_attrs($node_standby_1, $node_primary, $node_standby_1,
 182         "standby", 0);
 183
 184 # Fail to connect in "read-write" mode with standby1,standby2 list.
 185 test_target_session_attrs($node_standby_1, $node_standby_2, undef,
 186         "read-write", 2);
 187
 188 # Fail to connect in "primary" mode with standby1,standby2 list.
 189 test_target_session_attrs($node_standby_1, $node_standby_2, undef,
 190         "primary", 2);
 191
 192 # Fail to connect in "read-only" mode with primary,primary list.
 193 test_target_session_attrs($node_primary, $node_primary, undef,
 194         "read-only", 2);
 195
 196 # Fail to connect in "standby" mode with primary,primary list.
 197 test_target_session_attrs($node_primary, $node_primary, undef, "standby", 2);
 198
 199 # Test for SHOW commands using a WAL sender connection with a replication
 200 # role.
 201 note "testing SHOW commands for replication connection";
 202
 203 $node_primary->psql(
 204         'postgres', "
 205 CREATE ROLE repl_role REPLICATION LOGIN;
 206 GRANT pg_read_all_settings TO repl_role;");
 207 my $primary_host   = $node_primary->host;
 208 my $primary_port   = $node_primary->port;
 209 my $connstr_common = "host=$primary_host port=$primary_port user=repl_role";
 210 my $connstr_rep    = "$connstr_common replication=1";
 211 my $connstr_db     = "$connstr_common replication=database dbname=postgres";
 212
 213 # Test SHOW ALL
 214 my ($ret, $stdout, $stderr) = $node_primary->psql(
 215         'postgres', 'SHOW ALL;',
 216         on_error_die => 1,
 217         extra_params => [ '-d', $connstr_rep ]);
 218 ok($ret == 0, "SHOW ALL with replication role and physical replication");
 219 ($ret, $stdout, $stderr) = $node_primary->psql(
 220         'postgres', 'SHOW ALL;',
 221         on_error_die => 1,
 222         extra_params => [ '-d', $connstr_db ]);
 223 ok($ret == 0, "SHOW ALL with replication role and logical replication");
 224
 225 # Test SHOW with a user-settable parameter
 226 ($ret, $stdout, $stderr) = $node_primary->psql(
 227         'postgres', 'SHOW work_mem;',
 228         on_error_die => 1,
 229         extra_params => [ '-d', $connstr_rep ]);
 230 ok( $ret == 0,
 231         "SHOW with user-settable parameter, replication role and physical replication"
 232 );
 233 ($ret, $stdout, $stderr) = $node_primary->psql(
 234         'postgres', 'SHOW work_mem;',
 235         on_error_die => 1,
 236         extra_params => [ '-d', $connstr_db ]);
 237 ok( $ret == 0,
 238         "SHOW with user-settable parameter, replication role and logical replication"
 239 );
 240
 241 # Test SHOW with a superuser-settable parameter
 242 ($ret, $stdout, $stderr) = $node_primary->psql(
 243         'postgres', 'SHOW primary_conninfo;',
 244         on_error_die => 1,
 245         extra_params => [ '-d', $connstr_rep ]);
 246 ok( $ret == 0,
 247         "SHOW with superuser-settable parameter, replication role and physical replication"
 248 );
 249 ($ret, $stdout, $stderr) = $node_primary->psql(
 250         'postgres', 'SHOW primary_conninfo;',
 251         on_error_die => 1,
 252         extra_params => [ '-d', $connstr_db ]);
 253 ok( $ret == 0,
 254         "SHOW with superuser-settable parameter, replication role and logical replication"
 255 );
 256
 257 note "testing READ_REPLICATION_SLOT command for replication connection";
 258
 259 my $slotname = 'test_read_replication_slot_physical';
 260
 261 ($ret, $stdout, $stderr) = $node_primary->psql(
 262         'postgres',
 263         'READ_REPLICATION_SLOT non_existent_slot;',
 264         extra_params => [ '-d', $connstr_rep ]);
 265 ok($ret == 0, "READ_REPLICATION_SLOT exit code 0 on success");
 266 like($stdout, qr/^\|\|$/,
 267         "READ_REPLICATION_SLOT returns NULL values if slot does not exist");
 268
 269 $node_primary->psql(
 270         'postgres',
 271         "CREATE_REPLICATION_SLOT $slotname PHYSICAL RESERVE_WAL;",
 272         extra_params => [ '-d', $connstr_rep ]);
 273
 274 ($ret, $stdout, $stderr) = $node_primary->psql(
 275         'postgres',
 276         "READ_REPLICATION_SLOT $slotname;",
 277         extra_params => [ '-d', $connstr_rep ]);
 278 ok($ret == 0, "READ_REPLICATION_SLOT success with existing slot");
 279 like($stdout, qr/^physical\|[^|]*\|1$/,
 280         "READ_REPLICATION_SLOT returns tuple with slot information");
 281
 282 $node_primary->psql(
 283         'postgres',
 284         "DROP_REPLICATION_SLOT $slotname;",
 285         extra_params => [ '-d', $connstr_rep ]);
 286
 287 note "switching to physical replication slot";
 288
 289 # Switch to using a physical replication slot. We can do this without a new
 290 # backup since physical slots can go backwards if needed. Do so on both
 291 # standbys. Since we're going to be testing things that affect the slot state,
 292 # also increase the standby feedback interval to ensure timely updates.
 293 my ($slotname_1, $slotname_2) = ('standby_1', 'standby_2');
 294 $node_primary->append_conf('postgresql.conf', "max_replication_slots = 4");
 295 $node_primary->restart;
 296 is( $node_primary->psql(
 297                 'postgres',
 298                 qq[SELECT pg_create_physical_replication_slot('$slotname_1');]),
 299         0,
 300         'physical slot created on primary');
 301 $node_standby_1->append_conf('postgresql.conf',
 302         "primary_slot_name = $slotname_1");
 303 $node_standby_1->append_conf('postgresql.conf',
 304         "wal_receiver_status_interval = 1");
 305 $node_standby_1->append_conf('postgresql.conf', "max_replication_slots = 4");
 306 $node_standby_1->restart;
 307 is( $node_standby_1->psql(
 308                 'postgres',
 309                 qq[SELECT pg_create_physical_replication_slot('$slotname_2');]),
 310         0,
 311         'physical slot created on intermediate replica');
 312 $node_standby_2->append_conf('postgresql.conf',
 313         "primary_slot_name = $slotname_2");
 314 $node_standby_2->append_conf('postgresql.conf',
 315         "wal_receiver_status_interval = 1");
 316 # should be able change primary_slot_name without restart
 317 # will wait effect in get_slot_xmins above
 318 $node_standby_2->reload;
 319
 320 # Fetch xmin columns from slot's pg_replication_slots row, after waiting for
 321 # given boolean condition to be true to ensure we've reached a quiescent state
 322 sub get_slot_xmins
 323 {
 324         my ($node, $slotname, $check_expr) = @_;
 325
 326         $node->poll_query_until(
 327                 'postgres', qq[
 328                 SELECT $check_expr
 329                 FROM pg_catalog.pg_replication_slots
 330                 WHERE slot_name = '$slotname';
 331         ]) or die "Timed out waiting for slot xmins to advance";
 332
 333         my $slotinfo = $node->slot($slotname);
 334         return ($slotinfo->{'xmin'}, $slotinfo->{'catalog_xmin'});
 335 }
 336
 337 # There's no hot standby feedback and there are no logical slots on either peer
 338 # so xmin and catalog_xmin should be null on both slots.
 339 my ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
 340         "xmin IS NULL AND catalog_xmin IS NULL");
 341 is($xmin, '', 'xmin of non-cascaded slot null with no hs_feedback');
 342 is($catalog_xmin, '',
 343         'catalog xmin of non-cascaded slot null with no hs_feedback');
 344
 345 ($xmin, $catalog_xmin) = get_slot_xmins($node_standby_1, $slotname_2,
 346         "xmin IS NULL AND catalog_xmin IS NULL");
 347 is($xmin, '', 'xmin of cascaded slot null with no hs_feedback');
 348 is($catalog_xmin, '',
 349         'catalog xmin of cascaded slot null with no hs_feedback');
 350
 351 # Replication still works?
 352 $node_primary->safe_psql('postgres', 'CREATE TABLE replayed(val integer);');
 353
 354 sub replay_check
 355 {
 356         my $newval = $node_primary->safe_psql('postgres',
 357                 'INSERT INTO replayed(val) SELECT coalesce(max(val),0) + 1 AS newval FROM replayed RETURNING val'
 358         );
 359         $node_primary->wait_for_catchup($node_standby_1, 'replay',
 360                 $node_primary->lsn('insert'));
 361         $node_standby_1->wait_for_catchup($node_standby_2, 'replay',
 362                 $node_standby_1->lsn('replay'));
 363         $node_standby_1->safe_psql('postgres',
 364                 qq[SELECT 1 FROM replayed WHERE val = $newval])
 365           or die "standby_1 didn't replay primary value $newval";
 366         $node_standby_2->safe_psql('postgres',
 367                 qq[SELECT 1 FROM replayed WHERE val = $newval])
 368           or die "standby_2 didn't replay standby_1 value $newval";
 369         return;
 370 }
 371
 372 replay_check();
 373
 374 note "enabling hot_standby_feedback";
 375
 376 # Enable hs_feedback. The slot should gain an xmin. We set the status interval
 377 # so we'll see the results promptly.
 378 $node_standby_1->safe_psql('postgres',
 379         'ALTER SYSTEM SET hot_standby_feedback = on;');
 380 $node_standby_1->reload;
 381 $node_standby_2->safe_psql('postgres',
 382         'ALTER SYSTEM SET hot_standby_feedback = on;');
 383 $node_standby_2->reload;
 384 replay_check();
 385
 386 ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
 387         "xmin IS NOT NULL AND catalog_xmin IS NULL");
 388 isnt($xmin, '', 'xmin of non-cascaded slot non-null with hs feedback');
 389 is($catalog_xmin, '',
 390         'catalog xmin of non-cascaded slot still null with hs_feedback');
 391
 392 my ($xmin1, $catalog_xmin1) = get_slot_xmins($node_standby_1, $slotname_2,
 393         "xmin IS NOT NULL AND catalog_xmin IS NULL");
 394 isnt($xmin1, '', 'xmin of cascaded slot non-null with hs feedback');
 395 is($catalog_xmin1, '',
 396         'catalog xmin of cascaded slot still null with hs_feedback');
 397
 398 note "doing some work to advance xmin";
 399 $node_primary->safe_psql(
 400         'postgres', q{
 401 do $$
 402 begin
 403   for i in 10000..11000 loop
 404     -- use an exception block so that each iteration eats an XID
 405     begin
 406       insert into tab_int values (i);
 407     exception
 408       when division_by_zero then null;
 409     end;
 410   end loop;
 411 end$$;
 412 });
 413
 414 $node_primary->safe_psql('postgres', 'VACUUM;');
 415 $node_primary->safe_psql('postgres', 'CHECKPOINT;');
 416
 417 my ($xmin2, $catalog_xmin2) =
 418   get_slot_xmins($node_primary, $slotname_1, "xmin <> '$xmin'");
 419 note "primary slot's new xmin $xmin2, old xmin $xmin";
 420 isnt($xmin2, $xmin, 'xmin of non-cascaded slot with hs feedback has changed');
 421 is($catalog_xmin2, '',
 422         'catalog xmin of non-cascaded slot still null with hs_feedback unchanged'
 423 );
 424
 425 ($xmin2, $catalog_xmin2) =
 426   get_slot_xmins($node_standby_1, $slotname_2, "xmin <> '$xmin1'");
 427 note "standby_1 slot's new xmin $xmin2, old xmin $xmin1";
 428 isnt($xmin2, $xmin1, 'xmin of cascaded slot with hs feedback has changed');
 429 is($catalog_xmin2, '',
 430         'catalog xmin of cascaded slot still null with hs_feedback unchanged');
 431
 432 note "disabling hot_standby_feedback";
 433
 434 # Disable hs_feedback. Xmin should be cleared.
 435 $node_standby_1->safe_psql('postgres',
 436         'ALTER SYSTEM SET hot_standby_feedback = off;');
 437 $node_standby_1->reload;
 438 $node_standby_2->safe_psql('postgres',
 439         'ALTER SYSTEM SET hot_standby_feedback = off;');
 440 $node_standby_2->reload;
 441 replay_check();
 442
 443 ($xmin, $catalog_xmin) = get_slot_xmins($node_primary, $slotname_1,
 444         "xmin IS NULL AND catalog_xmin IS NULL");
 445 is($xmin, '', 'xmin of non-cascaded slot null with hs feedback reset');
 446 is($catalog_xmin, '',
 447         'catalog xmin of non-cascaded slot still null with hs_feedback reset');
 448
 449 ($xmin, $catalog_xmin) = get_slot_xmins($node_standby_1, $slotname_2,
 450         "xmin IS NULL AND catalog_xmin IS NULL");
 451 is($xmin, '', 'xmin of cascaded slot null with hs feedback reset');
 452 is($catalog_xmin, '',
 453         'catalog xmin of cascaded slot still null with hs_feedback reset');
 454
 455 note "check change primary_conninfo without restart";
 456 $node_standby_2->append_conf('postgresql.conf', "primary_slot_name = ''");
 457 $node_standby_2->enable_streaming($node_primary);
 458 $node_standby_2->reload;
 459
 460 # be sure do not streaming from cascade
 461 $node_standby_1->stop;
 462
 463 my $newval = $node_primary->safe_psql('postgres',
 464         'INSERT INTO replayed(val) SELECT coalesce(max(val),0) + 1 AS newval FROM replayed RETURNING val'
 465 );
 466 $node_primary->wait_for_catchup($node_standby_2, 'replay',
 467         $node_primary->lsn('insert'));
 468 my $is_replayed = $node_standby_2->safe_psql('postgres',
 469         qq[SELECT 1 FROM replayed WHERE val = $newval]);
 470 is($is_replayed, qq(1), "standby_2 didn't replay primary value $newval");
 471
 472 # Drop any existing slots on the primary, for the follow-up tests.
 473 $node_primary->safe_psql('postgres',
 474         "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots;");
 475
 476 # Test physical slot advancing and its durability.  Create a new slot on
 477 # the primary, not used by any of the standbys. This reserves WAL at creation.
 478 my $phys_slot = 'phys_slot';
 479 $node_primary->safe_psql('postgres',
 480         "SELECT pg_create_physical_replication_slot('$phys_slot', true);");
 481 # Generate some WAL, and switch to a new segment, used to check that
 482 # the previous segment is correctly getting recycled as the slot advancing
 483 # would recompute the minimum LSN calculated across all slots.
 484 my $segment_removed = $node_primary->safe_psql('postgres',
 485         'SELECT pg_walfile_name(pg_current_wal_lsn())');
 486 chomp($segment_removed);
 487 $node_primary->psql(
 488         'postgres', "
 489         CREATE TABLE tab_phys_slot (a int);
 490         INSERT INTO tab_phys_slot VALUES (generate_series(1,10));
 491         SELECT pg_switch_wal();");
 492 my $current_lsn =
 493   $node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();");
 494 chomp($current_lsn);
 495 my $psql_rc = $node_primary->psql('postgres',
 496         "SELECT pg_replication_slot_advance('$phys_slot', '$current_lsn'::pg_lsn);"
 497 );
 498 is($psql_rc, '0', 'slot advancing with physical slot');
 499 my $phys_restart_lsn_pre = $node_primary->safe_psql('postgres',
 500         "SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$phys_slot';"
 501 );
 502 chomp($phys_restart_lsn_pre);
 503 # Slot advance should persist across clean restarts.
 504 $node_primary->restart;
 505 my $phys_restart_lsn_post = $node_primary->safe_psql('postgres',
 506         "SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$phys_slot';"
 507 );
 508 chomp($phys_restart_lsn_post);
 509 ok( ($phys_restart_lsn_pre cmp $phys_restart_lsn_post) == 0,
 510         "physical slot advance persists across restarts");
 511
 512 # Check if the previous segment gets correctly recycled after the
 513 # server stopped cleanly, causing a shutdown checkpoint to be generated.
 514 my $primary_data = $node_primary->data_dir;
 515 ok(!-f "$primary_data/pg_wal/$segment_removed",
 516         "WAL segment $segment_removed recycled after physical slot advancing");