Performing controlled switchover v7

Controlled switchover is the exchanging of roles between a publication database and a subscription database. The tables that were formerly publications become the subscription tables. The former subscription tables become the publications.

Controlled switchover is useful for situations in which you must take the publication database 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's 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 again, switching the roles back.

Controlled switchover overview

When you perform controlled switchover, you're 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 Replication Server uses to capture and store updates for replication. To accomplish the controlled switchover, perform the following:

• 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 to exchange the connection information for the publication database and subscription database. Make these in the control schema of all publication databases to ensure consistency of the control schema across all publication databases.
• Modify the Replication Server configuration file to reference a new controller database if the former publication database was the designated controller database.

Controlled switchover steps

In these examples, 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).

1. Stop all transaction processing against the publication database.

2. Perform an on-demand synchronization replication or a snapshot replication (for snapshot-only publications) to replicate any pending updates in the publication database shadow tables to the subscription database.

3. Stop the publication server and the subscription server.

4. Review the prerequisites in Prerequisite steps to ensure that you can use the subscription database and its host in the role of a publication database. Also make sure that you can use the publication database and its host in the role of a subscription database.

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 might be needed in the pg_hba.conf files.

1. 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 making the backup.

2. 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 have the prefixes rrpd_, rrpi_, and rrpu_. The trigger functions have the same prefixes. For the log-based method, a trigger for each table has the prefixed rrpt_. The function is named capturetruncateevent.

   Delete or disable these triggers on node 1.

3. 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 making the backup.

4. Restore the backups of schemas _edb_replicator_pub, _edb_replicator_sub, and _edb_scheduler to the subscription database on node 2. Also restore the backup of the replication triggers and trigger functions to the subscription database on node 2.

5. Restore the backup of schema _edb_replicator_sub to the publication database on node 1.

6. 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 might require updating includes:

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

Make these updates in the control schema of all publication databases to ensure consistency of the control schema information in case the controller database is switched later.

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';  

1. If you decide to use a publication server or subscription server on a new host, perform this step.

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';  

1. Edit the Replication Server 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 Replication Server 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  

1. 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 Postgres server authentication.

2. 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 get 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 might 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 later. Keeping the replication slot on the database server eliminates the need to recreate the replication slot since it still exists but is 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. Delete the replication slot with the following command:

SELECT pg_drop_replication_slot('xdb_47919_5');

See Dropping replication slots for log-based synchronization replication for more information on deleting the replication slot if the pg_drop_replication_slot function isn't successful. If you switch the databases back to their original roles, you have to recreate the replication slot on the publication database server as described in this step.

1. 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, you can perform synchronization replications.

• On this page
• Controlled switchover overview
• Controlled switchover steps