is the exchanging of roles between a publication database and a subscription database. That is, the tables that were formerly publications become the subscription tables. The former subscription tables now become the publications.
This discussion assumes that the trigger-based method of synchronization replication is used by the publication database. If the publication database employs the log-based method, then it must be determined if the current subscription database meets the criteria for using the log-based method if that is so desired when it is switched to the role of the publication database. If the subscription database does not meet the criteria, then the trigger-based method must be implemented and used. See Section 2.2.10
for information on the log-based method and the necessary configuration steps that must be performed if the log-based method is to be used.
Stop all transaction processing against the publication database.
Perform an on demand synchronization replication or a snapshot replication (for snapshot-only publications) in order to replicate any pending updates in the publication database shadow tables to the subscription database.
Stop the publication server and the subscription server.
Review the prerequisites in Section 5.1
to ensure that the subscription database and its host can be used in the role of a publication database, and the publication database and its host can be used in the role of a subscription database.
Create a backup of schemas _edb_replicator_pub
, and _edb_scheduler
from the publication database on node 1.
Create a backup of the replication triggers and their corresponding trigger functions on the publication tables on node 1. For the trigger-based method, these triggers are named with prefixes of rrpd_
. The trigger functions are named with the same prefixes. For the log-based method, a trigger for each table is prefixed with rrpt_
. The function is named capturetruncateevent
Create a backup of schema _edb_replicator_sub
from the subscription database on node 2.
Restore the backups of schemas _edb_replicator_pub
, and _edb_scheduler
created in Step 5 to the subscription database on node 2. Also restore the backup of the replication triggers and trigger functions created in Step 6 to the subscription database on node 2.
Restore the backup of schema _edb_replicator_sub
created in Step 7 to the publication database on node 1.
Update the control schema objects so that the publication database definition references the new publication database (that is, the former subscription database) on node 2 and the subscription database definition references the new subscription database (that is, the former publication database) on node 1.
If you decide to use a publication server or subscription server on a new host, perform the following step, otherwise go to Step 12.
Edit the xDB Replication Configuration file on the publication server and subscription server host so that it contains the controller database connection and authentication information for the new publication database now running on node 2.
Update the pg_hba.conf
files of the database servers to allow access to the subscription database now on node 1 and the publication database now on node 2 in accordance with Section 18.104.22.168
When using the log-based method, create a replication slot on the database server that now contains the publication database.
See Section 10.3.4.4
for additional information on deleting the replication slot if the pg_drop_replication_slot
function is not successful. If you switch back the databases to their original roles, you will just have to recreate the replication slot on the publication database server as previously described in this step.
The controlled switchover is now complete. Start the publication server and the subscription server.
After confirming that the publication tables are consistent with the subscription tables, the first replication operation must be a snapshot. After performing a snapshot, synchronization replications may be performed.