1. Full Stack Disaster Recovery
  2. Preparing PostgreSQL DB System for Disaster Recovery

Preparing PostgreSQL DB System for Disaster Recovery

Perform the following prerequisites before adding a PostgreSQL DB System to a disaster recovery (DR) protection group.

Ensure that you have access to the OCI Console and meet the requirements to create a DR protection group.

  1. IAM Setup and Permissions:

    Ensure that the user configuring PostgreSQL replication has the required IAM permissions to manage PostgreSQL DB Systems and create replication relationships.

    Allow group <group_name> to manage postgres-db-systems in compartment <compartment_name>

  2. Database Systems:

    1. Create the primary PostgreSQL DB System in the source region using the OCI Console, REST API, CLI, or SDK.
    2. Ensure that the primary DB System is in the Active lifecycle state.
    3. Ensure that both the primary and standby DB Systems use PostgreSQL version 14 or later.

  3. Configure Replication with Warm Standby:

    1. In the OCI Console, select the secondary region where you want to create the standby DB System.
    2. Navigate to Databases, select PostgreSQL, and then select DB Systems.
    3. Click Create PostgreSQL Database system.
    4. Select Create Replica Database System as the creation type.
    5. Select the source region and primary PostgreSQL DB System.
    6. Configure the standby DB System and complete the creation process.
    7. Wait until the standby DB System reaches the Active lifecycle state.

  4. Network Connectivity:

    1. Ensure that the primary and standby DB Systems are configured in the appropriate VCNs and subnets.
    2. Configure security lists or Network Security Groups (NSGs) to allow the required database and application connectivity.
    3. Ensure that the application can connect to the PostgreSQL DB System in the standby region after a DR operation.

  5. Configuration Consistency:

    1. Ensure that the PostgreSQL configurations on the primary and standby DB Systems are compatible.
    2. Verify that extensions installed on the primary DB System are also available on the standby DB System.
    3. Configure any required PostgreSQL settings on the standby DB System because database configurations aren't automatically replicated.

  6. Verify Replication:

    1. Navigate to the primary PostgreSQL DB System details page and verify that the DB system role is Primary.
    2. Select Replica DB systems and open the standby DB System.
    3. Verify that the standby DB system role is Warm Standby.
    4. Verify that replication is configured and monitor replication lag.

Note:

  • The warm standby PostgreSQL DB System operates in read-only mode. No manual database mode change is required.
  • PostgreSQL replication with warm standby uses asynchronous replication.
  • Oracle recommends configuring the primary and standby DB Systems with similar shapes and performance characteristics to maintain comparable performance after a role transition.
  • PostgreSQL DB System configurations aren't automatically replicated between the primary and standby DB Systems.

Related Topics