| 1 |
|
| 2 |
# Copyright (c) 2021-2025, PostgreSQL Global Development Group |
| 3 |
|
| 4 |
# Testing of logical decoding using SQL interface and/or pg_recvlogical |
| 5 |
# |
| 6 |
# Most logical decoding tests are in contrib/test_decoding. This module |
| 7 |
# is for work that doesn't fit well there, like where server restarts |
| 8 |
# are required. |
| 9 |
use strict; |
| 10 |
use warnings FATAL => 'all'; |
| 11 |
use PostgreSQL::Test::Cluster; |
| 12 |
use PostgreSQL::Test::Utils; |
| 13 |
use Test::More; |
| 14 |
use Config; |
| 15 |
|
| 16 |
# Initialize primary node |
| 17 |
my $node_primary = PostgreSQL::Test::Cluster->new('primary'); |
| 18 |
$node_primary->init(allows_streaming => 1); |
| 19 |
$node_primary->append_conf( |
| 20 |
'postgresql.conf', qq( |
| 21 |
wal_level = logical |
| 22 |
)); |
| 23 |
$node_primary->start; |
| 24 |
my $backup_name = 'primary_backup'; |
| 25 |
|
| 26 |
$node_primary->safe_psql('postgres', |
| 27 |
qq[CREATE TABLE decoding_test(x integer, y text);]); |
| 28 |
|
| 29 |
$node_primary->safe_psql('postgres', |
| 30 |
qq[SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding');] |
| 31 |
); |
| 32 |
|
| 33 |
# Cover walsender error shutdown code |
| 34 |
my ($result, $stdout, $stderr) = $node_primary->psql( |
| 35 |
'template1', |
| 36 |
qq[START_REPLICATION SLOT test_slot LOGICAL 0/0], |
| 37 |
replication => 'database'); |
| 38 |
ok( $stderr =~ |
| 39 |
m/replication slot "test_slot" was not created in this database/, |
| 40 |
"Logical decoding correctly fails to start"); |
| 41 |
|
| 42 |
($result, $stdout, $stderr) = $node_primary->psql( |
| 43 |
'template1', |
| 44 |
qq[READ_REPLICATION_SLOT test_slot;], |
| 45 |
replication => 'database'); |
| 46 |
like( |
| 47 |
$stderr, |
| 48 |
qr/cannot use READ_REPLICATION_SLOT with a logical replication slot/, |
| 49 |
'READ_REPLICATION_SLOT not supported for logical slots'); |
| 50 |
|
| 51 |
# Check case of walsender not using a database connection. Logical |
| 52 |
# decoding should not be allowed. |
| 53 |
($result, $stdout, $stderr) = $node_primary->psql( |
| 54 |
'template1', |
| 55 |
qq[START_REPLICATION SLOT s1 LOGICAL 0/1], |
| 56 |
replication => 'true'); |
| 57 |
ok($stderr =~ /ERROR: logical decoding requires a database connection/, |
| 58 |
"Logical decoding fails on non-database connection"); |
| 59 |
|
| 60 |
$node_primary->safe_psql('postgres', |
| 61 |
qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(1,10) s;] |
| 62 |
); |
| 63 |
|
| 64 |
# Basic decoding works |
| 65 |
$result = $node_primary->safe_psql('postgres', |
| 66 |
qq[SELECT pg_logical_slot_get_changes('test_slot', NULL, NULL);]); |
| 67 |
is(scalar(my @foobar = split /^/m, $result), |
| 68 |
12, 'Decoding produced 12 rows inc BEGIN/COMMIT'); |
| 69 |
|
| 70 |
# If we immediately crash the server we might lose the progress we just made |
| 71 |
# and replay the same changes again. But a clean shutdown should never repeat |
| 72 |
# the same changes when we use the SQL decoding interface. |
| 73 |
$node_primary->restart; |
| 74 |
|
| 75 |
# There are no new writes, so the result should be empty. |
| 76 |
$result = $node_primary->safe_psql('postgres', |
| 77 |
qq[SELECT pg_logical_slot_get_changes('test_slot', NULL, NULL);]); |
| 78 |
chomp($result); |
| 79 |
is($result, '', 'Decoding after fast restart repeats no rows'); |
| 80 |
|
| 81 |
# Insert some rows and verify that we get the same results from pg_recvlogical |
| 82 |
# and the SQL interface. |
| 83 |
$node_primary->safe_psql('postgres', |
| 84 |
qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(1,4) s;] |
| 85 |
); |
| 86 |
|
| 87 |
my $expected = q{BEGIN |
| 88 |
table public.decoding_test: INSERT: x[integer]:1 y[text]:'1' |
| 89 |
table public.decoding_test: INSERT: x[integer]:2 y[text]:'2' |
| 90 |
table public.decoding_test: INSERT: x[integer]:3 y[text]:'3' |
| 91 |
table public.decoding_test: INSERT: x[integer]:4 y[text]:'4' |
| 92 |
COMMIT}; |
| 93 |
|
| 94 |
my $stdout_sql = $node_primary->safe_psql('postgres', |
| 95 |
qq[SELECT data FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1');] |
| 96 |
); |
| 97 |
is($stdout_sql, $expected, 'got expected output from SQL decoding session'); |
| 98 |
|
| 99 |
my $endpos = $node_primary->safe_psql('postgres', |
| 100 |
"SELECT lsn FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL) ORDER BY lsn DESC LIMIT 1;" |
| 101 |
); |
| 102 |
print "waiting to replay $endpos\n"; |
| 103 |
|
| 104 |
# Insert some rows after $endpos, which we won't read. |
| 105 |
$node_primary->safe_psql('postgres', |
| 106 |
qq[INSERT INTO decoding_test(x,y) SELECT s, s::text FROM generate_series(5,50) s;] |
| 107 |
); |
| 108 |
|
| 109 |
my $stdout_recv = $node_primary->pg_recvlogical_upto( |
| 110 |
'postgres', 'test_slot', $endpos, |
| 111 |
$PostgreSQL::Test::Utils::timeout_default, |
| 112 |
'include-xids' => '0', |
| 113 |
'skip-empty-xacts' => '1'); |
| 114 |
chomp($stdout_recv); |
| 115 |
is($stdout_recv, $expected, |
| 116 |
'got same expected output from pg_recvlogical decoding session'); |
| 117 |
|
| 118 |
$node_primary->poll_query_until('postgres', |
| 119 |
"SELECT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = 'test_slot' AND active_pid IS NULL)" |
| 120 |
) or die "slot never became inactive"; |
| 121 |
|
| 122 |
$stdout_recv = $node_primary->pg_recvlogical_upto( |
| 123 |
'postgres', 'test_slot', $endpos, |
| 124 |
$PostgreSQL::Test::Utils::timeout_default, |
| 125 |
'include-xids' => '0', |
| 126 |
'skip-empty-xacts' => '1'); |
| 127 |
chomp($stdout_recv); |
| 128 |
is($stdout_recv, '', 'pg_recvlogical acknowledged changes'); |
| 129 |
|
| 130 |
$node_primary->safe_psql('postgres', 'CREATE DATABASE otherdb'); |
| 131 |
|
| 132 |
is( $node_primary->psql( |
| 133 |
'otherdb', |
| 134 |
"SELECT lsn FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL) ORDER BY lsn DESC LIMIT 1;" |
| 135 |
), |
| 136 |
3, |
| 137 |
'replaying logical slot from another database fails'); |
| 138 |
|
| 139 |
$node_primary->safe_psql('otherdb', |
| 140 |
qq[SELECT pg_create_logical_replication_slot('otherdb_slot', 'test_decoding');] |
| 141 |
); |
| 142 |
|
| 143 |
# make sure you can't drop a slot while active |
| 144 |
SKIP: |
| 145 |
{ |
| 146 |
|
| 147 |
# some Windows Perls at least don't like IPC::Run's start/kill_kill regime. |
| 148 |
skip "Test fails on Windows perl", 2 if $Config{osname} eq 'MSWin32'; |
| 149 |
|
| 150 |
my $pg_recvlogical = IPC::Run::start( |
| 151 |
[ |
| 152 |
'pg_recvlogical', |
| 153 |
'--dbname' => $node_primary->connstr('otherdb'), |
| 154 |
'--slot' => 'otherdb_slot', |
| 155 |
'--file' => '-', |
| 156 |
'--start' |
| 157 |
]); |
| 158 |
$node_primary->poll_query_until('otherdb', |
| 159 |
"SELECT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = 'otherdb_slot' AND active_pid IS NOT NULL)" |
| 160 |
) or die "slot never became active"; |
| 161 |
is($node_primary->psql('postgres', 'DROP DATABASE otherdb'), |
| 162 |
3, 'dropping a DB with active logical slots fails'); |
| 163 |
$pg_recvlogical->kill_kill; |
| 164 |
is($node_primary->slot('otherdb_slot')->{'plugin'}, |
| 165 |
'test_decoding', 'logical slot still exists'); |
| 166 |
} |
| 167 |
|
| 168 |
$node_primary->poll_query_until('otherdb', |
| 169 |
"SELECT EXISTS (SELECT 1 FROM pg_replication_slots WHERE slot_name = 'otherdb_slot' AND active_pid IS NULL)" |
| 170 |
) or die "slot never became inactive"; |
| 171 |
|
| 172 |
is($node_primary->psql('postgres', 'DROP DATABASE otherdb'), |
| 173 |
0, 'dropping a DB with inactive logical slots succeeds'); |
| 174 |
is($node_primary->slot('otherdb_slot')->{'plugin'}, |
| 175 |
'', 'logical slot was actually dropped with DB'); |
| 176 |
|
| 177 |
# Test logical slot advancing and its durability. |
| 178 |
# Passing failover=true (last arg) should not have any impact on advancing. |
| 179 |
my $logical_slot = 'logical_slot'; |
| 180 |
$node_primary->safe_psql('postgres', |
| 181 |
"SELECT pg_create_logical_replication_slot('$logical_slot', 'test_decoding', false, false, true);" |
| 182 |
); |
| 183 |
$node_primary->psql( |
| 184 |
'postgres', " |
| 185 |
CREATE TABLE tab_logical_slot (a int); |
| 186 |
INSERT INTO tab_logical_slot VALUES (generate_series(1,10));"); |
| 187 |
my $current_lsn = |
| 188 |
$node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn();"); |
| 189 |
chomp($current_lsn); |
| 190 |
my $psql_rc = $node_primary->psql('postgres', |
| 191 |
"SELECT pg_replication_slot_advance('$logical_slot', '$current_lsn'::pg_lsn);" |
| 192 |
); |
| 193 |
is($psql_rc, '0', 'slot advancing with logical slot'); |
| 194 |
my $logical_restart_lsn_pre = $node_primary->safe_psql('postgres', |
| 195 |
"SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$logical_slot';" |
| 196 |
); |
| 197 |
chomp($logical_restart_lsn_pre); |
| 198 |
# Slot advance should persist across clean restarts. |
| 199 |
$node_primary->restart; |
| 200 |
my $logical_restart_lsn_post = $node_primary->safe_psql('postgres', |
| 201 |
"SELECT restart_lsn from pg_replication_slots WHERE slot_name = '$logical_slot';" |
| 202 |
); |
| 203 |
chomp($logical_restart_lsn_post); |
| 204 |
ok(($logical_restart_lsn_pre cmp $logical_restart_lsn_post) == 0, |
| 205 |
"logical slot advance persists across restarts"); |
| 206 |
|
| 207 |
my $stats_test_slot1 = 'test_slot'; |
| 208 |
my $stats_test_slot2 = 'logical_slot'; |
| 209 |
|
| 210 |
# Test that reset works for pg_stat_replication_slots |
| 211 |
|
| 212 |
# Stats exist for stats test slot 1 |
| 213 |
is( $node_primary->safe_psql( |
| 214 |
'postgres', |
| 215 |
qq(SELECT total_bytes > 0, stats_reset IS NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1') |
| 216 |
), |
| 217 |
qq(t|t), |
| 218 |
qq(Total bytes is > 0 and stats_reset is NULL for slot '$stats_test_slot1'.) |
| 219 |
); |
| 220 |
|
| 221 |
# Do reset of stats for stats test slot 1 |
| 222 |
$node_primary->safe_psql('postgres', |
| 223 |
qq(SELECT pg_stat_reset_replication_slot('$stats_test_slot1'))); |
| 224 |
|
| 225 |
# Get reset value after reset |
| 226 |
my $reset1 = $node_primary->safe_psql('postgres', |
| 227 |
qq(SELECT stats_reset FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1') |
| 228 |
); |
| 229 |
|
| 230 |
# Do reset again |
| 231 |
$node_primary->safe_psql('postgres', |
| 232 |
qq(SELECT pg_stat_reset_replication_slot('$stats_test_slot1'))); |
| 233 |
|
| 234 |
is( $node_primary->safe_psql( |
| 235 |
'postgres', |
| 236 |
qq(SELECT stats_reset > '$reset1'::timestamptz, total_bytes = 0 FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1') |
| 237 |
), |
| 238 |
qq(t|t), |
| 239 |
qq(Check that reset timestamp is later after the second reset of stats for slot '$stats_test_slot1' and confirm total_bytes was set to 0.) |
| 240 |
); |
| 241 |
|
| 242 |
# Check that test slot 2 has NULL in reset timestamp |
| 243 |
is( $node_primary->safe_psql( |
| 244 |
'postgres', |
| 245 |
qq(SELECT stats_reset IS NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot2') |
| 246 |
), |
| 247 |
qq(t), |
| 248 |
qq(Stats_reset is NULL for slot '$stats_test_slot2' before reset.)); |
| 249 |
|
| 250 |
# Get reset value again for test slot 1 |
| 251 |
$reset1 = $node_primary->safe_psql('postgres', |
| 252 |
qq(SELECT stats_reset FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1') |
| 253 |
); |
| 254 |
|
| 255 |
# Reset stats for all replication slots |
| 256 |
$node_primary->safe_psql('postgres', |
| 257 |
qq(SELECT pg_stat_reset_replication_slot(NULL))); |
| 258 |
|
| 259 |
# Check that test slot 2 reset timestamp is no longer NULL after reset |
| 260 |
is( $node_primary->safe_psql( |
| 261 |
'postgres', |
| 262 |
qq(SELECT stats_reset IS NOT NULL FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot2') |
| 263 |
), |
| 264 |
qq(t), |
| 265 |
qq(Stats_reset is not NULL for slot '$stats_test_slot2' after reset all.) |
| 266 |
); |
| 267 |
|
| 268 |
is( $node_primary->safe_psql( |
| 269 |
'postgres', |
| 270 |
qq(SELECT stats_reset > '$reset1'::timestamptz FROM pg_stat_replication_slots WHERE slot_name = '$stats_test_slot1') |
| 271 |
), |
| 272 |
qq(t), |
| 273 |
qq(Check that reset timestamp is later after resetting stats for slot '$stats_test_slot1' again.) |
| 274 |
); |
| 275 |
|
| 276 |
# done with the node |
| 277 |
$node_primary->stop; |
| 278 |
|
| 279 |
done_testing(); |