Replication with Warm Standby

After the source region is restored, the original primary database (PHX) remains a standalone database system.

Update the role of the original primary database system (PHX) to a warm standby database system as described in Converting a Standalone Database System to Warm Standby. Specify the original warm standby database server (LHR, now converted to standalone) as the primary database system.

Now the primary database system is LHR with Read/Write access, and the warm standby database server is PHX with Read-only access.

Use the switchover function to return the current primary database system (LHR) back to the original primary (PHX) as described in Switching Roles Between Primary and Warm Standby Database Systems. After the switchover occurs, the updated primary is PHX and the updated warm standby is LHR. You can resume normal database operations with the primary in the source region and the warm standby in the replica region.

• Current RPO (seconds): Current RPO of the primary database system. Displays the current replication lag in seconds, indicating how far the standby is behind the primary.
• Current transaction mode: Current transaction mode of the primary database system.
  • 1: Read/Write
  • 0: Read-only

    A value of 0 indicates that the database system has turned into read-only mode due to RPO enforcement.

• Last transaction replay epoch: Displays the time when last replayed transaction's write-ahead logs record was generated on the primary.
• Last transaction replay lag: Displays the time lag in milliseconds between the epoch at which the last transaction was replayed on a warm standby database system and the epoch at which the same transaction was committed on the primary database system.

Switch to the region where you want your primary database system to reside and create the database using the CLI as described in Creating a Database System.

Switch to the region where you want the warm standby database system to reside. Use the following command to create the warm standby.

Update source database system details like sourceType and primaryDbSystemId with the appropriate values. For example:

oci raw-request --http-method POST --auth security_token --config-file ~/.oci/config --profile DEFAULT --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems' \
  --request-body '{
  "displayName": "Warm Standby Database",
  "description": "Test db",
  "compartmentId": "ocid.compartment.oc1..exampleuniqueID",
  "dbVersion": "14",
  "storageDetails": {
    "systemType": "OCI_OPTIMIZED_STORAGE",
    "isRegionallyDurable": false,
    "availabilityDomain": "gXfg:UK-LONDON-1-AD-2"
  },
  "shape": "PostgreSQL.VM.Standard.E5.Flex",
  "instanceOcpuCount": 2,
  "instanceCount": 1,
  "instanceMemorySizeInGBs": 32,
  "networkDetails": {
    "subnetId": "ocid1.subnet..exampleuniqueID"
  },
  "source": {
    "sourceType": "DB_SYSTEM",
    "primaryDbSystemId": "ocid1.postgresqldbsystem..exampleuniqueID"
  }
}'

Use the following command to list warm standby database systems associated with the primary database system.

oci raw-request --http-method GET --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems/<dbSystemId>/replicas' --region us-phoenix-1 --profile DEFAULT --config-file ~/.oci/config --auth security_token

where <dbSystemId> is the OCID of the primary database system.

Use the following command to convert a warm standby database system to standalone.

oci raw-request --http-method POST --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems/<warm_standby>/actions/changeRoleToStandalone' --region us-phoenix-1 --request-body '{"changeMode":"[immediately | "Replay Pending Updates"]"}'  --profile DEFAULT --config-file ~/.oci/config --auth security_token

where <warm_standby> is the warm standby database system.

Use the following command to convert a standalone database system to warm standby.

oci raw-request --http-method POST --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems/<warm_standby>/actions/changeRoleToReplica' --region us-phoenix-1 --request-body '{"primaryDbSystemId":"<primary>"}'  --profile DEFAULT --config-file ~/.oci/config --auth security_token

where <warm_standby> is the warm standby database system and <primary> is the primary database system.

Use the following command to update the replication configuration (RPO settings) for a database system.

oci raw-request --http-method PUT --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems/<dbSystemId>' --region us-phoenix-1 --request-body '{"replicationConfig": {"isRpoEnforced": true, "rpoInSeconds": 300}}' --profile DEFAULT --config-file ~/.oci/config --auth security_token

where <dbSystemId> is the OCID of the database system.

• isRpoEnforced: Specifies whether RPO enforcement is enabled or disabled.

• rpoInSeconds: Specifies the RPO value (in seconds) to enforce when enabled. The value must be between 300 and 10,800 seconds.

Use the following command to perform a switchover between a standalone database system and a warm standby.

oci raw-request --http-method POST --target-uri 'https://postgresql.us-phoenix-1.oci.oraclecloud.com/20220915/dbSystems/<primary>/actions/switchover' --region us-phoenix-1 --request-body
'{"replicaDbSystemId":"<warm-standby>"}' --profile DEFAULT --config-file ~/.oci/config --auth security_token

where <primary> is the primary database system and <warm_standby> is the warm standby database system.