Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXowe-00Cp3p-NM for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 20:55:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rXowd-00DLZo-IJ for pgsql-hackers@arkaria.postgresql.org; Wed, 07 Feb 2024 20:54:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXowc-00DLZg-GT for pgsql-hackers@lists.postgresql.org; Wed, 07 Feb 2024 20:54:59 +0000 Received: from wfhigh4-smtp.messagingengine.com ([64.147.123.155]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rXowY-0060iY-4L for pgsql-hackers@lists.postgresql.org; Wed, 07 Feb 2024 20:54:57 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailfhigh.west.internal (Postfix) with ESMTP id 002B21800089; Wed, 7 Feb 2024 15:54:50 -0500 (EST) Received: from imap49 ([10.202.2.99]) by compute2.internal (MEProxy); Wed, 07 Feb 2024 15:54:51 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eulerto.com; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:subject :subject:to:to; s=fm1; t=1707339290; x=1707425690; bh=CvnFbKCXlI WocDEJLQP2/tZVi0Jx9yAJvFQxPkx6Q9Q=; b=ksanP0ggnyOQ+nnl533SNH5Q4n +v212kh9KeJJGSX5SIoQW+qr5+ZgqjNfeAsCWACT7ET6pOeAmgHsRiXUIfLR8UzY D5lrRZbKwXDimJF5xtYUtX2E6H+BkdcqI7oSfQi0xnwKKtxF2L7IcmzeayoTApyS 1YWz0YOufArn2ENBkyZSVvY7bVSV5VW2qUcVOM2iqIIPVyOkTYwn+yJwBXj0xHr8 hW8bc4MyV9dkZXmd/0dYOZnvm/cli7v1NjLDBnYg+sr+62JviVOlAiSoQIMrGlw3 7XWLPM4lONJ+L6b1909Qg+YqHKU5UfMeNpvab6vUL6qLtMjzPSikJro09TYg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:subject:subject:to :to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1707339290; x=1707425690; bh=CvnFbKCXlIWocDEJLQP2/tZVi0Jx 9yAJvFQxPkx6Q9Q=; b=LvyFx6P1U/NXihqyU9J0ECRgOMLcYcN0ZQgGmCSc/iwj SOf6ab0VzoESsh+leMI2ssOB6kh0PjtQiz8tBhfrX02rFXbhIuL50vQzrZOM8D9y sqU1uxZvfjeF7zOTPbyR0snCJ8i/sR5mLogDQlbpAXhoTykEnqZrTXPl3seqBEkh uUrYCrTfG/7Oi/N4Ku3uo/sZ9mqH/YcsmBLaYvxahKv+O+L7sC7OwDWZZqlP+M9M jGxlS2TG1KzdbhrYlTenzQJ/FyXHKWATwWIJvHvfoD3boVxyU41aSXbaeEeVR9hl GFFRuFkPtBBh/52TNG55r6SCbhOqRiMWOq7zu0ynAQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrtddvgddufeelucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepofgfggfkjghffffhvfevufgtsegrtderreerredtnecuhfhrohhmpedfgfhu lhgvrhcuvfgrvhgvihhrrgdfuceovghulhgvrhesvghulhgvrhhtohdrtghomheqnecugg ftrfgrthhtvghrnhepkeffffethedvvdekkedtjedvhfdtheegffetvdekieekudehuddv fffhvdefvedvnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghdpghhithhhuh gsrdgtohhmpdgvnhhtvghrphhrihhsvggusgdrtghomhenucevlhhushhtvghrufhiiigv pedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegvuhhlvghrsegvuhhlvghrthhordgtoh hm X-ME-Proxy: Feedback-ID: i0c21471d:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id DA9AA15A0091; Wed, 7 Feb 2024 15:54:49 -0500 (EST) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.11.0-alpha0-144-ge5821d614e-fm-20240125.002-ge5821d61 MIME-Version: 1.0 Message-Id: <34075bd4-c85d-4654-a782-0cd6713b2c89@app.fastmail.com> In-Reply-To: References: <9a9c7e65-c47b-4400-a63e-ac6169c0860e@app.fastmail.com> <8d8d9960-8130-4216-b0e4-adf36b8b1c43@app.fastmail.com> <370673e8-e2da-4249-8f07-cb67e9d9c20e@app.fastmail.com> Date: Wed, 07 Feb 2024 17:54:29 -0300 From: "Euler Taveira" To: "kuroda.hayato@fujitsu.com" , =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= Cc: "pgsql-hackers@lists.postgresql.org" , "vignesh C" , "Michael Paquier" , "Peter Eisentraut" , "Andres Freund" , "Ashutosh Bapat" , "Amit Kapila" , "Shlok Kyal" Subject: Re: speed up a logical replica setup Content-Type: multipart/alternative; boundary=bce557adf5eb4c59afaffb8324e47c83 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --bce557adf5eb4c59afaffb8324e47c83 Content-Type: text/plain On Wed, Feb 7, 2024, at 2:31 AM, Hayato Kuroda (Fujitsu) wrote: > Ah, actually I did not have such a point of view. Assuming that changed port number > can avoid connection establishments, there are four options: > a) Does not overwrite port and listen_addresses. This allows us to monitor by > external agents, but someone can modify GUCs and data during operations. > b) Overwrite port but do not do listen_addresses. Not sure it is useful... > c) Overwrite listen_addresses but do not do port. This allows us to monitor by > local agents, and we can partially protect the database. But there is still a > room. > d) Overwrite both port and listen_addresses. This can protect databases perfectly > but no one can monitor. Remember the target server was a standby (read only access). I don't expect an application trying to modify it; unless it is a buggy application. Regarding GUCs, almost all of them is PGC_POSTMASTER (so it cannot be modified unless the server is restarted). The ones that are not PGC_POSTMASTER, does not affect the pg_createsubscriber execution [1]. postgres=# select name, setting, context from pg_settings where name in ('max_replication_slots', 'max_logical_replication_workers', 'max_worker_processes', 'max_sync_workers_per_subscription', 'max_parallel_apply_workers_per_subscription'); name | setting | context ---------------------------------------------+---------+------------ max_logical_replication_workers | 4 | postmaster max_parallel_apply_workers_per_subscription | 2 | sighup max_replication_slots | 10 | postmaster max_sync_workers_per_subscription | 2 | sighup max_worker_processes | 8 | postmaster (5 rows) I'm just pointing out that this case is a different from pg_upgrade (from which this idea was taken). I'm not saying that's a bad idea. I'm just arguing that you might be preventing some access read only access (monitoring) when it is perfectly fine to connect to the database and execute queries. As I said before, the current UI allows anyone to setup the standby to accept only local connections. Of course, it is an extra step but it is possible. However, once you apply v16-0007, there is no option but use only local connection during the transformation. Is it an acceptable limitation? Under reflection, I don't expect a big window 1802 /* 1803 * Start subscriber and wait until accepting connections. 1804 */ 1805 pg_log_info("starting the subscriber"); 1806 if (!dry_run) 1807 start_standby_server(pg_bin_dir, opt.subscriber_dir, server_start_log); 1808 1809 /* 1810 * Waiting the subscriber to be promoted. 1811 */ 1812 wait_for_end_recovery(dbinfo[0].subconninfo, pg_bin_dir, &opt); . . . 1845 /* 1846 * Stop the subscriber. 1847 */ 1848 pg_log_info("stopping the subscriber"); 1849 if (!dry_run) 1850 stop_standby_server(pg_bin_dir, opt.subscriber_dir); ... mainly because the majority of the time will be wasted in wait_for_end_recovery() if the server takes some time to reach consistent state (and during this phase it cannot accept connections anyway). Aren't we worrying too much about it? > Hmm, which one should be chosen? I prefer c) or d). > Do you know how pglogical_create_subscriber does? pglogical_create_subscriber does nothing [2][3]. [1] https://www.postgresql.org/docs/current/logical-replication-config.html [2] https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/pglogical_create_subscriber.c#L488 [3] https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/pglogical_create_subscriber.c#L529 -- Euler Taveira EDB https://www.enterprisedb.com/ --bce557adf5eb4c59afaffb8324e47c83 Content-Type: text/html Content-Transfer-Encoding: quoted-printable
On Wed, Feb 7, = 2024, at 2:31 AM, Hayato Kuroda (Fujitsu) wrote:
Ah, actually I did not have such a= point of view. Assuming that changed port number
can avoi= d connection establishments, there are four options:
a) Do= es not overwrite port and listen_addresses. This allows us to monitor by=
   external agents, but someone can modify GUCs= and data during operations.
b) Overwrite port but do not = do listen_addresses. Not sure it is useful... 
c) Ove= rwrite listen_addresses but do not do port. This allows us to monitor by=
   local agents, and we can partially protect t= he database. But there is still a 
   room.=
d) Overwrite both port and listen_addresses. This can pro= tect databases perfectly
but no one can monitor.
=

Remember the target server was a standb= y (read only access). I don't expect an
application trying= to modify it; unless it is a buggy application. Regarding
GUCs, almost all of them is PGC_POSTMASTER (so it cannot be modified un= less the
server is restarted). The ones that are not PGC_P= OSTMASTER, does not affect the
pg_createsubscriber executi= on [1].

postgres=3D# select name, setting, = context from pg_settings where name in ('max_replication_slots', 'max_lo= gical_replication_workers', 'max_worker_processes', 'max_sync_workers_pe= r_subscription', 'max_parallel_apply_workers_per_subscription');
           = ;         name   =             =       | setting |  context  &nbs= p;
---------------------------------------------+---------= +------------
max_logical_replication_workers  &= nbsp;          | 4 &nb= sp;     | postmaster
max_parallel_appl= y_workers_per_subscription | 2       | sig= hup
max_replication_slots     &nb= sp;           &nb= sp;     | 10      | postmas= ter
max_sync_workers_per_subscription   &nb= sp;       | 2     = ;  | sighup
max_worker_processes   &nb= sp;           &nb= sp;        | 8    = ;   | postmaster
(5 rows)

I'm just pointing out that this case is a different from pg_upgra= de (from which
this idea was taken). I'm not saying that's= a bad idea. I'm just arguing that
you might be preventing= some access read only access (monitoring) when it is
perf= ectly fine to connect to the database and execute queries. As I said
=
before, the current UI allows anyone to setup the standby to = accept only local
connections. Of course, it is an extra s= tep but it is possible. However, once
you apply v16-0007, = there is no option but use only local connection during the
transformation. Is it an acceptable limitation?

Under reflection, I don't expect a big window

<= /div>
1802     /*
1803  =     * Start subscriber and wait until accepting connectio= ns.
1804      */
18= 05     pg_log_info("starting the subscriber");
1806     if (!dry_run)
1807&n= bsp;        start_standby_server(pg_b= in_dir, opt.subscriber_dir, server_start_log);
1808 <= br>
1809     /*
1810 &n= bsp;    * Waiting the subscriber to be promoted.
1811      */
1812 &nbs= p;   wait_for_end_recovery(dbinfo[0].subconninfo, pg_bin_dir, = &opt);
.
.
.
1845     /*
1846   &nb= sp;  * Stop the subscriber.
1847   &nb= sp;  */
1848     pg_log_info("sto= pping the subscriber");
1849     if (!= dry_run)
1850       &nb= sp; stop_standby_server(pg_bin_dir, opt.subscriber_dir);
<= br>
... mainly because the majority of the time will be wasted= in
wait_for_end_recovery() if the server takes some time = to reach consistent state
(and during this phase it cannot= accept connections anyway). Aren't we worrying
too much a= bout it?

Hmm, which one should be chosen? I prefer c) or d).
Do you know how pglogical_create_subscriber does?

pglogical_create_subscriber does nothing [2][= 3].