Re: speed up a logical replica setup

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/