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/