diff options
Diffstat (limited to 'doc/src/sgml/logical-replication.sgml')
| -rw-r--r-- | doc/src/sgml/logical-replication.sgml | 62 |
1 files changed, 47 insertions, 15 deletions
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index c32e6bc000d..e26f7f59d4a 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -575,8 +575,8 @@ HINT: To initiate replication, you must manually create the replication slot, e <programlisting> /* pub # */ SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput'); slot_name | lsn ------------+----------- - sub1 | 0/19404D0 +-----------+------------ + sub1 | 0/019404D0 (1 row) </programlisting></para> </listitem> @@ -617,8 +617,8 @@ HINT: To initiate replication, you must manually create the replication slot, e <programlisting> /* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); slot_name | lsn ------------+----------- - myslot | 0/19059A0 +-----------+------------ + myslot | 0/019059A0 (1 row) </programlisting></para> </listitem> @@ -655,8 +655,8 @@ HINT: To initiate replication, you must manually create the replication slot, e <programlisting> /* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput'); slot_name | lsn ------------+----------- - myslot | 0/1905930 +-----------+------------ + myslot | 0/01905930 (1 row) </programlisting></para> </listitem> @@ -709,8 +709,8 @@ HINT: To initiate replication, you must manually create the replication slot, e </para> <para> - To confirm that the standby server is indeed ready for failover, follow these - steps to verify that all necessary logical replication slots have been + To confirm that the standby server is indeed ready for failover for a given subscriber, follow these + steps to verify that all the logical replication slots required by that subscriber have been synchronized to the standby server: </para> @@ -764,7 +764,7 @@ HINT: To initiate replication, you must manually create the replication slot, e Check that the logical replication slots identified above exist on the standby server and are ready for failover. <programlisting> -/* standby # */ SELECT slot_name, (synced AND NOT temporary AND NOT conflicting) AS failover_ready +/* standby # */ SELECT slot_name, (synced AND NOT temporary AND invalidation_reason IS NULL) AS failover_ready FROM pg_replication_slots WHERE slot_name IN ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164'); @@ -782,10 +782,42 @@ HINT: To initiate replication, you must manually create the replication slot, e <para> If all the slots are present on the standby server and the result (<literal>failover_ready</literal>) of the above SQL query is true, then - existing subscriptions can continue subscribing to publications now on the - new primary server. + existing subscriptions can continue subscribing to publications on the new + primary server. + </para> + + <para> + The first two steps in the above procedure are meant for a + <productname>PostgreSQL</productname> subscriber. It is recommended to run + these steps on each subscriber node, that will be served by the designated + standby after failover, to obtain the complete list of replication + slots. This list can then be verified in Step 3 to ensure failover readiness. + Non-<productname>PostgreSQL</productname> subscribers, on the other hand, may + use their own methods to identify the replication slots used by their + respective subscriptions. + </para> + + <para> + In some cases, such as during a planned failover, it is necessary to confirm + that all subscribers, whether <productname>PostgreSQL</productname> or + non-<productname>PostgreSQL</productname>, will be able to continue + replication after failover to a given standby server. In such cases, use the + following SQL, instead of performing the first two steps above, to identify + which replication slots on the primary need to be synced to the standby that + is intended for promotion. This query returns the relevant replication slots + associated with all the failover-enabled subscriptions. </para> + <para> +<programlisting> +/* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots + FROM pg_replication_slots r + WHERE r.failover AND NOT r.temporary; + slots +------- + {'sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164'} +(1 row) +</programlisting></para> </sect1> <sect1 id="logical-replication-row-filter"> @@ -1965,15 +1997,15 @@ DETAIL: <replaceable class="parameter">detailed_explanation</replaceable>. ERROR: conflict detected on relation "public.test": conflict=insert_exists DETAIL: Key already exists in unique index "t_pkey", which was modified locally in transaction 740 at 2024-06-26 10:47:04.727375+08. Key (c)=(1); existing local tuple (1, 'local'); remote tuple (1, 'remote'). -CONTEXT: processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378 +CONTEXT: processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/014C0378 </screen> The LSN of the transaction that contains the change violating the constraint and - the replication origin name can be found from the server log (LSN 0/14C0378 and + the replication origin name can be found from the server log (LSN 0/014C0378 and replication origin <literal>pg_16395</literal> in the above case). The transaction that produced the conflict can be skipped by using <link linkend="sql-altersubscription-params-skip"><command>ALTER SUBSCRIPTION ... SKIP</command></link> with the finish LSN - (i.e., LSN 0/14C0378). The finish LSN could be an LSN at which the transaction + (i.e., LSN 0/014C0378). The finish LSN could be an LSN at which the transaction is committed or prepared on the publisher. Alternatively, the transaction can also be skipped by calling the <link linkend="pg-replication-origin-advance"> <function>pg_replication_origin_advance()</function></link> function. @@ -1984,7 +2016,7 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER <link linkend="sql-createsubscription-params-with-disable-on-error"><literal>disable_on_error</literal></link> option. Then, you can use <function>pg_replication_origin_advance()</function> function with the <parameter>node_name</parameter> (i.e., <literal>pg_16395</literal>) - and the next LSN of the finish LSN (i.e., 0/14C0379). The current position of + and the next LSN of the finish LSN (i.e., 0/014C0379). The current position of origins can be seen in the <link linkend="view-pg-replication-origin-status"> <structname>pg_replication_origin_status</structname></link> system view. Please note that skipping the whole transaction includes skipping changes that |