Re: speed up a logical replica setup

On Wed, Nov 1, 2023 at 7:10 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> Here are some comments about functionality and design.
>
> + <step>
> + <para>
> + <application>pg_subscriber</application> creates one replication slot for
> + each specified database on the source server. The replication slot name
> + contains a <literal>pg_subscriber</literal> prefix. These replication
> + slots will be used by the subscriptions in a future step. Another
> + replication slot is used to get a consistent start location. This
> + consistent LSN will be used as a stopping point in the <xref
> + linkend="guc-recovery-target-lsn"/> parameter and by the
> + subscriptions as a replication starting point. It guarantees that no
> + transaction will be lost.
> + </para>
> + </step>
>
> CREATE_REPLICATION_SLOT would wait for any incomplete transaction to
> complete. So it may not be possible to have an incomplete transaction
> on standby when it comes out of recovery. Am I correct? Can we please
> have a testcase where we test this scenario? What about a prepared
> transactions?
>

It will wait even for prepared transactions to commit. So, there
shouldn't be any behavior difference for prepared and non-prepared
transactions.

> +
> + <step>
> + <para>
> + <application>pg_subscriber</application> writes recovery parameters into
> + the target data directory and start the target server. It specifies a LSN
> + (consistent LSN that was obtained in the previous step) of write-ahead
> + log location up to which recovery will proceed. It also specifies
> + <literal>promote</literal> as the action that the server should take once
> + the recovery target is reached. This step finishes once the server ends
> + standby mode and is accepting read-write operations.
> + </para>
> + </step>
>
> At this stage the standby would have various replication objects like
> publications, subscriptions, origins inherited from the upstream
> server and possibly very much active. With failover slots, it might
> inherit replication slots. Is it intended that the new subscriber also
> acts as publisher for source's subscribers OR that the new subscriber
> should subscribe to the upstreams of the source? Some use cases like
> logical standby might require that but a multi-master multi-node setup
> may not. The behaviour should be user configurable.
>

Good points but even if we make it user configurable how to exclude
such replication objects? And if we don't exclude then what will be
their use because if one wants to use it as a logical standby then we
only need publications and failover/sync slots in it and also there
won't be a need to create new slots, publications on the primary to
make the current physical standby as logical subscriber.

> There may be other objects in this category which need special consideration on
> the subscriber. I haven't fully thought through the list of such objects.
>
> + uses the replication slot that was created in a previous step. The
> + subscription is created but it is not enabled yet. The reason is the
> + replication progress must be set to the consistent LSN but replication
> + origin name contains the subscription oid in its name. Hence, the
>
> Not able to understand the sentence "The reason is ... in its name".
> Why is subscription OID in origin name matters?
>

Using subscription OID in origin is probably to uniquely identify the
origin corresponding to the subscription, we do that while creating a
subscription as well.

--
With Regards,
Amit Kapila.