Message-ID: <0dffca12-bf17-4a7a-334d-225569de5e6e@gmail.com>
Date: Sun, 30 Jun 2024 16:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: speed up a logical replica setup
Content-Language: en-US
To: Peter Eisentraut <peter@eisentraut.org>, Euler Taveira
 <euler@eulerto.com>, Amit Kapila <amit.kapila16@gmail.com>,
 Shlok Kyal <shlok.kyal.oss@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 "kuroda.hayato@fujitsu.com" <kuroda.hayato@fujitsu.com>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>,
 Michael Paquier <michael@paquier.xyz>, Andres Freund <andres@anarazel.de>,
 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 =?UTF-8?Q?Fabr=c3=adzio_de_Royes_Mello?= <fabriziomello@gmail.com>,
 vignesh C <vignesh21@gmail.com>
References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@app.fastmail.com>
 <65b841a8-0c2f-4953-b9cd-240fce84b90b@eisentraut.org>
 <TYCPR01MB12077907C052EC329C45AE198F5362@TYCPR01MB12077.jpnprd01.prod.outlook.com>
 <c8e92bcb-69c3-477e-93d4-6f39e030cab0@app.fastmail.com>
 <47524383-6fd1-4b13-8c4e-fb918b1d35ba@enterprisedb.com>
 <bbbeed26-41f9-468c-ac24-ad99f429ccd7@app.fastmail.com>
 <CAA4eK1KPoLUUGzjw9eEQd56LUAf2QQj8BqMcsoSe6vycB5CYUQ@mail.gmail.com>
 <2e86cf53-c806-49d2-9a10-1a1257776d1f@app.fastmail.com>
 <CAA4eK1JJq_ER6Kq_H=jKHR75QPRd8y9_D=RtYw=aPYKMfqLi9A@mail.gmail.com>
 <CAA4eK1KdCb+5sjYu6qCMXXdCX1y_ihr8kFzMozq0=P=auYxgog@mail.gmail.com>
 <CANhcyEV6q1Vhd37i1axUeScLi0UAGVxta1LDa0BV0Eh--TcPMg@mail.gmail.com>
 <CAA4eK1JaQcAZ=n513-4VDW4ujE9T9f007fczNCpVDbg1smhT4g@mail.gmail.com>
 <5da4b286-e570-4341-8262-92d763f6b1a4@app.fastmail.com>
 <776c5cac-5ef5-4001-b1bc-5b698bc0c62a@app.fastmail.com>
 <3c024250-3f13-4f4d-b8e9-55f176db4abf@eisentraut.org>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <3c024250-3f13-4f4d-b8e9-55f176db4abf@eisentraut.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0dffca12-bf17-4a7a-334d-225569de5e6e%40gmail.com>
Precedence: bulk

Hello Peter and Euler,

17.06.2024 14:04, Peter Eisentraut wrote:
> On 07.06.24 05:49, Euler Taveira wrote:
>> Here it is a patch series to fix the issues reported in recent discussions. The
>> patches 0001 and 0003 aim to fix the buildfarm issues. The patch 0002 removes
>> synchronized failover slots on subscriber since it has no use. I also included
>> an optional patch 0004 that improves the usability by checking both servers if
>> it already failed in any subscriber check.
>
> I have committed 0001, 0002, and 0003.  Let's keep an eye on the buildfarm to see if that stabilizes things.  So far 
> it looks good.
>
> For 0004, I suggest inverting the result values from check_publisher() and create_subscriber() so that it returns true 
> if the check is ok.

As a recent buildfarm failure [1] shows, that test addition introduced
new instability:
### Starting node "node_s"
# Running: pg_ctl -w -D 
/home/bf/bf-build/piculet/HEAD/pgsql.build/testrun/pg_basebackup/040_pg_createsubscriber/data/t_040_pg_createsubscriber_node_s_data/pgdata 
-l 
/home/bf/bf-build/piculet/HEAD/pgsql.build/testrun/pg_basebackup/040_pg_createsubscriber/log/040_pg_createsubscriber_node_s.log 
-o --cluster-name=node_s start
waiting for server to start.... done
server started
# Postmaster PID for node "node_s" is 416482
error running SQL: 'psql:<stdin>:1: ERROR:  skipping slot synchronization as the received slot sync LSN 0/30047F0 for 
slot "failover_slot" is ahead of the standby position 0/3004708'
while running 'psql -XAtq -d port=51506 host=/tmp/pqWohdD5Qj dbname='postgres' -f - -v ON_ERROR_STOP=1' with sql 'SELECT 
pg_sync_replication_slots()' at /home/bf/bf-build/piculet/HEAD/pgsql/src/test/perl/PostgreSQL/Test/Cluster.pm line 2126.

I could reproduce this failure with:
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -517,6 +517,7 @@ WalReceiverMain(char *startup_data, size_t startup_data_len)
                       * let the startup process and primary server know about
                       * them.
                       */
+pg_usleep(300000);
                      XLogWalRcvFlush(false, startpointTLI);

make -s check -C src/bin/pg_basebackup/ PROVE_TESTS="t/040*"

# +++ tap check in src/bin/pg_basebackup +++
t/040_pg_createsubscriber.pl .. 22/? # Tests were run but no plan was declared and done_testing() was not seen.
# Looks like your test exited with 29 just after 23.
t/040_pg_createsubscriber.pl .. Dubious, test returned 29 (wstat 7424, 0x1d00)
All 23 subtests passed

Test Summary Report
-------------------
t/040_pg_createsubscriber.pl (Wstat: 7424 Tests: 23 Failed: 0)
   Non-zero exit status: 29
   Parse errors: No plan found in TAP output
Files=1, Tests=23,  4 wallclock secs ( 0.01 usr  0.01 sys +  0.49 cusr  0.44 csys =  0.95 CPU)

Moreover, this test may suffer from autovacuum:
echo "
autovacuum_naptime = 1
autovacuum_analyze_threshold = 1
" > /tmp/temp.config
TEMP_CONFIG=/tmp/temp.config make -s check -C src/bin/pg_basebackup/ PROVE_TESTS="t/040*"

# +++ tap check in src/bin/pg_basebackup +++
t/040_pg_createsubscriber.pl .. 24/?
#   Failed test 'failover slot is synced'
#   at t/040_pg_createsubscriber.pl line 273.
#          got: ''
#     expected: 'failover_slot'
t/040_pg_createsubscriber.pl .. 28/? # Looks like you failed 1 test of 33.
t/040_pg_createsubscriber.pl .. Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/33 subtests

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=piculet&dt=2024-06-28%2004%3A42%3A48

Best regards,
Alexander