RE: speed up a logical replica setup - Mailing list pgsql-hackers

Dear Euler,

>
If someone modifies data after promotion, fine; she has to deal with conflicts,
if any. IMO it is solved adding one or two sentences in the documentation.
>

OK. I could find issues, for now.

>
> >
> 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].
> >
> 
> IIUC,  primary_conninfo and primary_slot_name is PGC_SIGHUP.

Ditto.
>

Just to confirm - even if the primary_slot_name would be changed during
the conversion, the slot initially set would be dropped. Currently we do not
find any issues.

>
> >
> 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?
> >
> 
> My remained concern is written above. If they do not problematic we may not have
> to restrict them for now. At that time, changes 
> 
> 1) overwriting a port number,
> 2) setting listen_addresses = ''

It can be implemented later if people are excited by it.

> are not needed, right? IIUC inconsistency of -P may be still problematic.

I still think we shouldn't have only the transformed primary_conninfo as
option.
>


Hmm, OK. So let me summarize current status and discussions. 

Policy)

Basically, we do not prohibit to connect to primary/standby.
primary_slot_name may be changed during the conversion and
tuples may be inserted on target just after the promotion, but it seems no issues.

API)

-D (data directory) and -d (databases) are definitively needed.

Regarding the -P (a connection string for source), we can require it for now.
But note that it may cause an inconsistency if the pointed not by -P is different
from the node pointde by primary_conninfo.

As for the connection string for the target server, we can choose two ways:
a)
accept native connection string as -S. This can reuse the same parsing mechanism as -P,
but there is a room that non-local server is specified.

b)
accept username/port as -U/-p
(Since the policy is like above, listen_addresses would not be overwritten. Also, the port just specify the listening
port).
This can avoid connecting to non-local, but more options may be needed.
(E.g., Is socket directory needed? What about password?)

Other discussing point, reported issue)

Points raised by me [1] are not solved yet.

* What if the target version is PG16-?
* What if the found executables have diffent version with pg_createsubscriber?
* What if the target is sending WAL to another server?
   I.e., there are clusters like `node1->node2-.node3`, and the target is node2.
* Can we really cleanup the standby in case of failure?
   Shouldn't we suggest to remove the target once?
* Can we move outputs to stdout?

[1]:
https://www.postgresql.org/message-id/TYCPR01MB1207713BEC5C379A05D65E342F54B2%40TYCPR01MB12077.jpnprd01.prod.outlook.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/global/