Message-ID: <bde6ac67-69cc-c104-5ab6-dd4f5deadf24@gmail.com>
Date: Tue, 9 Jul 2024 14: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: Amit Kapila <amit.kapila16@gmail.com>,
 "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@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>
 <0dffca12-bf17-4a7a-334d-225569de5e6e@gmail.com>
 <OSBPR01MB25528300C71FDD83EA1DCA12F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com>
 <CAA4eK1+Qmc34cooSNm2=U6YsySSjZTn2_eD_deDFEAZv+aj-AA@mail.gmail.com>
 <OSBPR01MB2552440DCE1AE5EDC51BAEC6F5DD2@OSBPR01MB2552.jpnprd01.prod.outlook.com>
 <CAA4eK1J9Vn1HB7sXNx-cH4COBd_moGZtrZd82Z160mK6u0HspQ@mail.gmail.com>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <CAA4eK1J9Vn1HB7sXNx-cH4COBd_moGZtrZd82Z160mK6u0HspQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bde6ac67-69cc-c104-5ab6-dd4f5deadf24%40gmail.com>
Precedence: bulk

Hello Amit and Kuroda-san,

03.07.2024 14:02, Amit Kapila wrote:
> Pushed 0002 and 0003. Let's wait for a discussion on 0001.

Please look at another failure of the test [1]:
[13:28:05.647](2.460s) not ok 26 - failover slot is synced
[13:28:05.648](0.001s) #   Failed test 'failover slot is synced'
#   at /home/bf/bf-build/skink-master/HEAD/pgsql/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl line 307.
[13:28:05.648](0.000s) #          got: ''
#     expected: 'failover_slot'

with 040_pg_createsubscriber_node_s.log containing:
2024-07-08 13:28:05.369 UTC [3985464][client backend][0/2:0] LOG: statement: SELECT pg_sync_replication_slots()
2024-07-08 13:28:05.557 UTC [3985464][client backend][0/2:0] LOG: could not sync slot "failover_slot" as remote slot 
precedes local slot
2024-07-08 13:28:05.557 UTC [3985464][client backend][0/2:0] DETAIL:  Remote slot has LSN 0/30047B8 and catalog xmin 
743, but local slot has LSN 0/30047B8 and catalog xmin 744.

I could not reproduce it locally, but I've discovered that that subtest
somehow depends on pg_createsubscriber executed for the
'primary contains unmet conditions on node P' check. For example with this
test modification:
@@ -249,7 +249,7 @@ command_fails(
          $node_p->connstr($db1), '--socket-directory',
          $node_s->host, '--subscriber-port',
          $node_s->port, '--database',
-        $db1, '--database',
+        'XXX', '--database',
          $db2
      ],
      'primary contains unmet conditions on node P');

I see the same failure:
2024-07-09 10:19:43.284 UTC [938890] 040_pg_createsubscriber.pl LOG:  statement: SELECT pg_sync_replication_slots()
2024-07-09 10:19:43.292 UTC [938890] 040_pg_createsubscriber.pl LOG:  could not sync slot "failover_slot" as remote slot 
precedes local slot
2024-07-09 10:19:43.292 UTC [938890] 040_pg_createsubscriber.pl DETAIL:  Remote slot has LSN 0/3004780 and catalog xmin 
743, but local slot has LSN 0/3004780 and catalog xmin 744.

Thus maybe even a normal pg_createsubscriber run can affect the primary
server (it's catalog xmin) differently?

One difference I found in the logs, is that the skink failure's
regress_log_040_pg_createsubscriber contains:
pg_createsubscriber: error: publisher requires 2 wal sender processes, but only 1 remain

Though for a successful run I see locally (I can't find logs of
successful test runs on skink):
pg_createsubscriber: error: publisher requires 2 wal sender processes, but only 0 remain

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=skink&dt=2024-07-08%2013%3A16%3A35

Best regards,
Alexander