Hi,
Update: I added another test to the attached test-only patch. This new test uses pg_terminate_backend to trigger reconnection.
Assuming the tests are fully correct (your input appreciated on this) we can use them to validate the solution.
Kind regards, Mircea Cadariu
From dfb6e7d49ca544e9876e724ff2b0aeb89550358b Mon Sep 17 00:00:00 2001 From: Mircea Cadariu <[email protected]> Date: Wed, 19 Nov 2025 12:09:24 +0000 Subject: [PATCH v1] Test demonstrating pg_recvlogical receives duplicate data on reconnection When pg_recvlogical reconnects after losing connection, it can re-send already flushed data. This happens because the replication restart position is taken from the write position in the last status update, which may be older than the actual flushed position. This test documents the current behavior. A fix will be provided in a subsequent commit. --- src/bin/pg_basebackup/t/030_pg_recvlogical.pl | 133 ++++++++++++++++++ 1 file changed, 133 insertions(+) diff --git a/src/bin/pg_basebackup/t/030_pg_recvlogical.pl b/src/bin/pg_basebackup/t/030_pg_recvlogical.pl index 1b7a6f6f43..25449a4e82 100644 --- a/src/bin/pg_basebackup/t/030_pg_recvlogical.pl +++ b/src/bin/pg_basebackup/t/030_pg_recvlogical.pl @@ -151,4 +151,137 @@ my $result = $node->safe_psql('postgres', ); is($result, 't', "failover is enabled for the new slot"); +$node->command_ok( + [ + 'pg_recvlogical', + '--slot' => 'test', + '--drop-slot' + ], + 'drop failover slot'); + +# Test data re-transmission after disconnection (client killed) +use IPC::Run qw(start); + +my $outfile = $node->basedir . '/reconnect.out'; + +$node->command_ok( + [ + 'pg_recvlogical', + '--slot' => 'reconnect_test', + '--dbname' => $node->connstr('postgres'), + '--create-slot', + ], + 'slot created for reconnection test'); + +$node->safe_psql('postgres', 'CREATE TABLE t(x int); INSERT INTO t VALUES (1);'); + +my $recv = start [ + 'pg_recvlogical', + '--slot', 'reconnect_test', + '--dbname', $node->connstr('postgres'), + '--start', + '--file', $outfile, + '--fsync-interval', '1', + '--status-interval', '10' +], '>', \my $out, '2>', \my $err; + +sleep(3); + +$recv->kill_kill(); + +$node->safe_psql('postgres', 'INSERT INTO t VALUES (2);'); +my $endlsn = $node->safe_psql('postgres', 'SELECT pg_current_wal_lsn();'); + +$node->command_ok([ + 'pg_recvlogical', + '--slot', 'reconnect_test', + '--dbname', $node->connstr('postgres'), + '--start', + '--endpos', $endlsn, + '--file', $outfile +], 'second run after forced disconnect'); + +open(my $file, '<', $outfile); +my $count = 0; +while (<$file>) { + if (/INSERT/) { + $count = $count + 1; + } +} +close($file); + +cmp_ok($count, '>', 2, 'more than two INSERT after disconnect'); + +$node->command_ok( + [ + 'pg_recvlogical', + '--slot' => 'reconnect_test', + '--drop-slot' + ], + 'test slot dropped'); + +# Test data re-transmission after disconnection (backend killed) +$outfile = $node->basedir . '/reconnect2.out'; + +$node->command_ok( + [ + 'pg_recvlogical', + '--slot' => 'reconnect_test2', + '--dbname' => $node->connstr('postgres'), + '--create-slot', + ], + 'slot created for reconnection test 2'); + +$node->safe_psql('postgres', 'INSERT INTO t VALUES (1);'); + +my $recv2 = start [ + 'pg_recvlogical', + '--slot', 'reconnect_test2', + '--dbname', $node->connstr('postgres'), + '--start', + '--file', $outfile, + '--fsync-interval', '1', + '--status-interval', '60', + '--verbose' +], '>', \my $out2, '2>', \my $err2; + +sleep(3); + +my $backend_pid = $node->safe_psql('postgres', + "SELECT active_pid FROM pg_replication_slots WHERE slot_name = 'reconnect_test2'"); + +if ($backend_pid ne '') +{ + $node->safe_psql('postgres', "SELECT pg_terminate_backend($backend_pid)"); +} + +sleep(6); + +$node->safe_psql('postgres', 'INSERT INTO t VALUES (2);'); + +sleep(3); + +$recv2->signal('TERM'); +$recv2->finish(); + +open(my $file2, '<', $outfile); +$count = 0; +while (<$file2>) { + if (/INSERT/) { + $count = $count + 1; + } +} +close($file2); + +cmp_ok($count, '>', 2, 'more than two INSERTs'); + +$node->command_ok( + [ + 'pg_recvlogical', + '--slot' => 'reconnect_test2', + '--dbname' => $node->connstr('postgres'), + '--drop-slot' + ], + 'reconnect_test2 slot dropped'); + done_testing(); -- 2.39.5 (Apple Git-154)
