RE: Synchronizing slots from primary to standby

On Wednesday, May 8, 2024 5:21 PM Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> A few comments:
Thanks for the comments!

> 2 ===
>
> +test_sub=# SELECT
> + array_agg(slotname) AS slots
> + FROM
> + ((
> + SELECT r.srsubid AS subid, CONCAT('pg_', srsubid, '_sync_',
> srrelid, '_', ctl.system_identifier) AS slotname
> + FROM pg_control_system() ctl, pg_subscription_rel r,
> pg_subscription s
> + WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND
> s.subfailover
> + ) UNION (
>
> I guess this format comes from ReplicationSlotNameForTablesync(). What
> about creating a SQL callable function on top of it and make use of it in the
> query above? (that would ensure to keep the doc up to date even if the format
> changes in ReplicationSlotNameForTablesync()).

We could add a new function as suggested but I think it's not the right
time(beta1) to add this function because new function will bring
catversion bump which I think may not be worth at this stage. I think we can
consider this after releasing and maybe gather more use cases for the new
function you suggested.

>
> 3 ===
>
> +test_sub=# SELECT
> + MAX(remote_lsn) AS remote_lsn_on_subscriber
> + FROM
> + ((
> + SELECT (CASE WHEN r.srsubstate = 'f' THEN
> pg_replication_origin_progress(CONCAT('pg_', r.srsubid, '_', r.srrelid), false)
> + WHEN r.srsubstate IN ('s', 'r') THEN r.srsublsn
> END) AS remote_lsn
> + FROM pg_subscription_rel r, pg_subscription s
> + WHERE r.srsubstate IN ('f', 's', 'r') AND s.oid = r.srsubid AND
> s.subfailover
> + ) UNION (
> + SELECT pg_replication_origin_progress(CONCAT('pg_',
> s.oid), false) AS remote_lsn
> + FROM pg_subscription s
> + WHERE s.subfailover
> + ));
>
> What about adding a join to pg_replication_origin to get rid of the "hardcoded"
> format "CONCAT('pg_', r.srsubid, '_', r.srrelid)" and "CONCAT('pg_', s.oid)"?

I tried a bit, but it doesn't seem feasible to get the relationship between
subscription and origin by querying pg_subscription and
pg_replication_origin.

Best Regards,
Hou zj