Performing Controlled Switchover v6.2

Controlled switchover 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.

Controlled switchover is useful for situations where the publication database must be taken offline such as for periodic maintenance. After the switchover, applications connect to the former subscription database to perform their queries and updates, while the former publication database is kept synchronized by replication.

Updates for replication are accumulated in shadow tables that are created on the former subscription tables during the controlled switchover procedure. When the former publication database is online, it is synchronized as the target of replication.

When you determine that you want to reverse the roles again so that the original publication database directly receives queries and updates from applications, and the original subscription database receives updates by replication, you perform the controlled switchover procedure once again, switching the roles back.

Controlled Switchover Overview

When you perform controlled switchover, you are modifying the replication system so that the database identified and referenced in the control schema as the publication database is the physical database that was originally defined as the subscription database.

Similarly, the database identified and referenced in the control schema as the subscription database is changed to the physical database on which the publication was originally defined.

You must also create the database objects on the former subscription database that xDB Replication Server uses to capture and store updates for replication. In order to accomplish the controlled switchover, the following tasks must be performed:

• Copy the control schema of the publication database (that is, all control schema objects, shadow tables, sequences, triggers, and a package) to the subscription database.
• Copy the control schema of the subscription database to the publication database.
• Update certain control schema tables so as to exchange the connection information for the publication database and subscription database. These updates must be made in the control schema of all publication databases to ensure consistency of the control schema across all publication databases.
• Modify the xDB Replication Configuration file to reference a new controller database if the former publication database was the designated controller database.

Controlled Switchover Steps

This section describes the steps for performing a controlled switchover.

The following assumptions are made about the replication system environment:

• Node 1 is the server where the publication database originally resides. Its network IP address is 192.168.2.19.
• Node 2 is the server where the subscription database originally resides. Its network IP address is 192.168.2.20.
• The publication and subscription databases have the same name.
• You use the publication database user for the role of the subscription database user and the subscription database user for the role of the publication database user in the switched environment.
• The publication server and subscription server are running on the same host (node 1).

Step 1: Stop all transaction processing against the publication database.

Step 2: 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.

Step 3: Stop the publication server and the subscription server.

Step 4: Review the prerequisites in Section Prerequisite Steps 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.

For practical purposes, the following items are the most likely to be affected:

• The publication database user must be a superuser with system catalog modification privileges to allow it to act as the new subscription database user.
• Additional entries may be needed in the pg_hba.conf files.

Step 5: Create a backup of schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler from the publication database on node 1. Delete these schemas from the publication database on node 1 after the backup has been made.

Step 6: 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_, rrpi_ and rrpu_. 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.

Delete or disable these triggers on node 1.

Step 7: Create a backup of schema _edb_replicator_sub from the subscription database on node 2.

Delete this schema from the subscription database on node 2 after the backup has been made.

Step 8: Restore the backups of schemas _edb_replicator_pub, _edb_replicator_sub, 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.

Step 9: Restore the backup of schema _edb_replicator_sub created in Step 7 to the publication database on node 1.

Step 10: 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.

The connection information that may require updating includes the following:

• Host IP address
• Port number
• User name
• Password

These updates must be made in the control schema of all publication databases to ensure consistency of the control schema information should the controller database be switched at some later point in time.

For example, the following shows the update to the publication database definition so that its network IP address is now node 2 (192.168.2.20).

UPDATE _edb_replicator_pub.xdb_pub_database SET db_host = '192.168.2.20'; 

The following shows the update to the subscription database definition so that its network IP address is now node 1 (192.168.2.19).

UPDATE _edb_replicator_sub.xdb_sub_database SET db_host = '192.168.2.19';  

Step 11: If you decide to use a publication server or subscription server on a new host, perform the following step, otherwise go to Step 12.

The following example assumes you decide to use the publication server and subscription server running on node 2. Update the subscription metadata to the new location of the publication server managing its associated publication.

UPDATE _edb_replicator_sub.xdb_subscriptions SET pub_server_ip = '192.168.2.20';  

Update the publication metadata to the new location of the subscription server managing its associated subscription.

UPDATE _edb_replicator_pub.xdb_sub_servers SET sub_server_ip = '192.168.2.20';  

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.

The following is the modified xDB Replication Configuration file with the network location and authentication information of the new controller database now running on node 2.

#xDB Replication Server Configuration Properties   
#Fri Jan 30 17:34:06 GMT-05:00 2015   
port=5444   
admin_password=ygJ9AxoJEX854elcVIJPTw\=\=   
user=enterprisedb   
admin_user=enterprisedb   
type=enterprisedb   
database=edb   
password=ygJ9AxoJEX854elcVIJPTw\=\=   
host=192.168.2.20  

Step 13: Update thepg_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 Postgres Server Authentication.

Step 14: When using the log-based method, create a replication slot on the database server that now contains the publication database.

Use the following query to obtain the slot name from the database server that was previously running the publication database, but is now the subscription database server:

SELECT slot_name FROM pg_replication_slots WHERE plugin = 'test_decoding';
  slot_name
-------------
 xdb_47919_5
(1 row)

Create a new replication slot on the database server that is now running the publication database, but was previously the subscription database server. The slot name from the previous query is used when creating the new replication slot.

SELECT pg_create_logical_replication_slot('xdb_47919_5', 'test_decoding');
pg_create_logical_replication_slot
------------------------------------
 (xdb_47919_5,0/37A1270)
(1 row)

You may choose to keep the replication slot on the database server that now contains the subscription database, particularly if you plan to switch the publication and subscription databases back to their original roles at some future point. This eliminates the necessity for recreating the replication slot since it will still exist, but will be inactive until the publication is switched back to that database server.

Alternatively, you can delete the replication slot from the database server that now contains the subscription database. The replication slot is deleted with the following command:

SELECT pg_drop_replication_slot('xdb_47919_5');

See Dropping Replication Slots for Log-Based Synchronization Replication 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.

Step 15: The controlled switchover is now complete. Start the publication server and the subscription server.

Step 16: 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.

• On this page
• Controlled Switchover Overview
• Controlled Switchover Steps