Debian Codegraph @

src/test/recovery/t/006_logical_decoding.pl

raw
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();