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.
This discussion assumes that the trigger-based method of synchronization replication is used by the publication database. If the publication database uses the log-based method, then you must determine whether the current subscription database meets the criteria for using the log-based method if you want to when it is switched to the role of the publication database. If the subscription database doesn't meet the criteria, then you must implement and use the trigger-based method. See Synchronization replication with the log-based method for information on the log-based method and the configuration steps to perform when using the log-based method.
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.
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).
Stop all transaction processing against the publication database.
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.
Stop the publication server and the subscription server.
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
Create a backup of schemas
_edb_schedulerfrom the publication database on node 1. Delete these schemas from the publication database on node 1 after making the backup.
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
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
Delete or disable these triggers on node 1.
Create a backup of schema
_edb_replicator_subfrom the subscription database on node 2. Delete this schema from the subscription database on node 2 after making the backup.
Restore the backups of
_edb_schedulerto the subscription database on node 2. Also restore the backup of the replication triggers and trigger functions to the subscription database on node 2.
Restore the backup of schema
_edb_replicator_subto 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.
The connection information that might require updating includes:
- Host IP address
- Port number
- User name
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 (
The following shows the update to the subscription database definition so that its network IP address is now node 1 (192.168.2.19).
- 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 the publication metadata to the new location of the subscription server managing its associated subscription.
- 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.
pg_hba.conffiles 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.
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:
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.
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:
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.
- 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.