On Mon, Apr 24, 2023 at 3:36 PM Drouvot, Bertrand
<bertranddrouvot.pg@gmail.com> wrote:
>
> On 4/24/23 8:24 AM, Amit Kapila wrote:
>
> > 2.
> > +# Speed up the subscription creation
> > +$node_primary->safe_psql('postgres', "SELECT pg_log_standby_snapshot()");
> > +
> > +# Explicitly shut down psql instance gracefully - to avoid hangs
> > +# or worse on windows
> > +$psql_subscriber{subscriber_stdin} .= "\\q\n";
> > +$psql_subscriber{run}->finish;
> > +
> > +# Insert some rows on the primary
> > +$node_primary->safe_psql('postgres',
> > + qq[INSERT INTO tab_rep select generate_series(1,10);]);
> > +
> > +$node_primary->wait_for_replay_catchup($node_standby);
> > +
> > +# To speed up the wait_for_subscription_sync
> > +$node_primary->safe_psql('postgres', "SELECT pg_log_standby_snapshot()");
> > +$node_subscriber->wait_for_subscription_sync($node_standby, 'tap_sub');
> >
> > It is not clear to me why you need to do pg_log_standby_snapshot() twice.
>
> That's because there is 2 logical slot creations that have the be done on the standby.
>
> The one for the subscription:
>
> "
> CREATE_REPLICATION_SLOT "tap_sub" LOGICAL pgoutput (SNAPSHOT 'nothing')
> "
>
> And the one for the data sync:
>
> "
> CREATE_REPLICATION_SLOT "pg_16389_sync_16384_7225540800768250444" LOGICAL pgoutput (SNAPSHOT 'use')
> "
>
> Without the second "pg_log_standby_snapshot()" then wait_for_subscription_sync() would be waiting
> some time on the poll for "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');"
>
> Adding a comment in V3 to explain the need for the second pg_log_standby_snapshot().
>
Won't this still be unpredictable because it is possible that the
tablesync worker may take more time to get launched or create a
replication slot? If that happens after your second
pg_log_standby_snapshot() then wait_for_subscription_sync() will be
hanging. Wouldn't it be better to create a subscription with
(copy_data = false) to make it predictable and then we won't need
pg_log_standby_snapshot() to be performed twice?
If you agree with the above suggestion then you probably need to move
wait_for_subscription_sync() before Insert.
--
With Regards,
Amit Kapila.
