<procedure>

<step performance="required">

<para>

On the subscriber node, use the following SQL to identify which slots

should be synced to the standby that we plan to promote. This query will

return the relevant replication slots, including the main slots and table

synchronization slots associated with the failover-enabled subscriptions.

Note that the table sync slot should be synced to the standby server only

if the table copy is finished (See <xref linkend="catalog-pg-subscription-rel"/>).

On the subscriber node, use the following SQL to identify which replication

slots should be synced to the standby that we plan to promote. This query

will return the relevant replication slots associated with the

failover-enabled subscriptions.

<programlisting>

test_sub=# SELECT

array_agg(quote_literal(s.subslotname)) AS slots

FROM pg_subscription s

WHERE s.subfailover AND

s.subslotname IS NOT NULL;

slots

-------

{'sub1','sub2','sub3'}

(1 row)

</programlisting></para>

</step>

<step performance="required">

<para>

On the subscriber node, use the following SQL to identify which table

synchronization slots should be synced to the standby that we plan to promote.

This query needs to be run on each database that includes the failover-enabled

subscription(s). Note that the table sync slot should be synced to the standby

server only if the table copy is finished

(See <xref linkend="catalog-pg-subscription-rel"/>).

We don't need to ensure that the table sync slots are synced in other scenarios

as they will either be dropped or re-created on the new primary server in those

cases.

<programlisting>

test_sub=# SELECT

array_agg(slot_name) AS slots

array_agg(quote_literal(slot_name)) AS slots

FROM

((

SELECTr.srsubid AS subid,CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name

(

SELECT CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name

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 (

SELECT s.oid AS subid, s.subslotname as slot_name

FROM pg_subscription s

WHERE s.subfailover

))

WHERE slot_name IS NOT NULL;

);

slots

-------

{sub1,sub2,sub3}

{'pg_16394_sync_16385_7394666715149055164'}

(1 row)

</programlisting></para>

</step>

<programlisting>

test_standby=# SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready

FROM pg_replication_slots

WHERE slot_name IN ('sub1','sub2','sub3');

slot_name | failover_ready

-------------+----------------

sub1 | t

sub2 | t

sub3 | t

(3 rows)

WHERE slot_name IN

('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');

slot_name | failover_ready

--------------------------------------------+----------------

sub1 | t

sub2 | t

sub3 | t

pg_16394_sync_16385_7394666715149055164 | t

(4 rows)

</programlisting></para>

</step>

</procedure>

<command>ALTER SUBSCRIPTION ... SET (failover = true|false)</command> and

<command>ALTER SUBSCRIPTION ... SET (two_phase = false)</command>

cannot be executed inside a transaction block.

</para>

These commands also cannot be executed when the subscription has

<para>

Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command> and

<command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>

with <literal>refresh</literal> option as <literal>true</literal> also cannot

be executed when the subscription has

<link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>

commit enabled, unless

<link linkend="sql-createsubscription-params-with-copy-data"><literal>copy_data</literal></link>