Provision OCI Database with PostgreSQL and Migrate from On-Premises with Minimum Downtime using OCI GoldenGate

Introduction

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) is fully managed PostgreSQL-compatible service which provides 3 times higher performance through database optimized storage, half cost compared to Amazon Web Service (AWS), provides automated backups, high availability, scheduled maintenance, dynamically storage scale, vertical scaling and so on.

Example: Business Continuity

The OCI Database with PostgreSQL service offers a 99.99% uptime Service Level Agreement (SLA), a Recovery Time Objective (RTO) of less than 2 minutes for multi-node database systems, and a Recovery Point Objective (RPO) of 0 with no data loss. For single-node database systems in single Availability Domains (AD) regions, the service offers a 99.9% uptime SLA, an RTO of less than 20 minutes, and an RPO of 0.

OCI Database with PostgreSQL leverages OCI Block Volumes to store user data. So the same durability, security, and performance guarantees apply. For more information, see Overview of OCI Database with PostgreSQL and First Principles: Optimizing PostgreSQL for the cloud.

enter image description here

This tutorial focuses on migration from an on-premises PostgreSQL database to OCI Database with PostgreSQL using OCI GoldenGate with minimal downtime.

Environment Details:

  Source On-Premises Target OCI
DB Name product_inventory targetdb
User ordermanager admin
Schema data_analyze data_analyze
DB Version 15.12 15.6

Objectives

  1. Create OCI Database with PostgreSQL database System.

  2. Provision OCI GoldenGate deployment.

  3. Create on-premises database and OCI database connection on deployment and assign connections.

  4. Prepare source database server and database.

  5. Create initial load Extract.

  6. Backup source database metadata using the pg_dump utility.

  7. Import metadata into OCI Database with PostgreSQL.

  8. Create initial load Replicat using trail file.

  9. Create online or Change Data Capture (CDC) Extract altering to starting Log Sequence Number (LSN) for capture.

  10. Create online Replicat giving CDC Extract trail file.

Task 1: Provision OCI Database with PostgreSQL Database System

This tasks explains how to create database system and the following policies are required before you provision. For more information, see Managing a Database System.

  1. Add the following Oracle Cloud Infrastructure Identity and Access Management (IAM) policies.

    Allow group <postgresql-admin-group> to read compartments in tenancy
Allow group <postgresql-admin-group> to manage postgres-db-systems in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to manage postgres-backups in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to read postgres-work-requests in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to manage postgres-configuration in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to manage virtual-network-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to read secret-family in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to read vaults in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to read metrics in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to use tag-namespaces in tenancy

  2. Create or select a vault for the database administrator password. For more information, see Managing Vaults.

  3. Create a key for the database password. For more information, see Managing Keys.

  4. Create a secret for the database password. For more information, see Managing Vault Secrets.

  5. Log in to the OCI Console, navigate to Database, PostgreSQL, DB systems, Create new database system and click Next.

    enter image description here

    enter image description here

  6. Select Hardware configuration.

    enter image description here

  7. Select Virtual Cloud Network and Subnet and enter Database system administrator credentials. In this example, Username will be admin.

    enter image description here

    Once creation completed and the database system shows Active state.

    enter image description here

  8. Run the following command to verify the connection to OCI Database with PostgreSQL from compute node.

    -bash-4.2$ psql "sslmode=verify-full sslrootcert=c.pub host=<OCI DB server ip address>  user=admin dbname=postgres"
Password for user admin:
psql (15.12, server 15.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
postgres=>

Task 2: Create an OCI GoldenGate Deployment for Migration

  1. Go to the OCI Console, navigate to Oracle Database, GoldenGate, Deployments and click Create Deployment.

  2. Enter the required information specified in the following images and select PostgreSQL as Select a Technology.

    enter image description here

    enter image description here

  3. In Configure your deployment, use oggadmin as Username to log in to the OCI GoldenGate Console as administrator.

    enter image description here

    enter image description here

  4. Click Create to start the creation and deployment. Once successfully completed, it will show Active state.

    enter image description here

Task 3: Create On-Premises Database Connection, OCI Database Connections on Deployment and Assign Connections

OCI GoldenGate requires the both source, target database connection and assign both connections to deployment.

  1. Create source database connection.

    1. Click the deployment name (PG_Mig_deployment), select Connections and click Create Connection. Enter the required information as shown in the following images.

      enter image description here

    2. Select Type as PostgreSQL Server, Security Protocol as Plain and click Create.

      enter image description here

  2. Create target database connection.

    1. Click the deployment name, select Connections and click Create Connection. Enter the required information as shown in the following images.

      enter image description here

    2. Select Type as OCI PostgreSQL.

      enter image description here

    3. Select Security Protocol as TLS and SSL Mode as Require.

      enter image description here

  3. Assign the database connections to deployment.

    1. Go to Deployments, click the deployment name (PG_Mig_deployment), select Assign connections and click Assign Connection.

      enter image description here

    2. Enter the required information as shown in the following images.

      enter image description here

      You will see Active state after assign connection completes.

      enter image description here

Task 4: Prepare Source Database Server and Database

  1. Install the postgresql15-contrib package.

    OCI GoldenGate requires the postgresql14-contrib package on source database server in order to to extract from PostgreSQL server database. Package can be installed using the sudo yum install postgresql15-contrib command (source database version is 15).

    Note: It is recommended that the version of the client libraries should match the PostgreSQL database version. Client versions below 10 are not supported.

  2. Prepare database users creation on source database and required privileges.

    product_inventory=# create user  ggreplication with password 'abc1234';
ERROR:  role "ggreplication" already exists
product_inventory=#
product_inventory=#  select current_database();
 current_database
-------------------
 product_inventory
(1 row)

product_inventory=# GRANT CONNECT ON DATABASE product_inventory TO ggreplication;
GRANT
product_inventory=# ALTER USER ggreplication WITH 	REPLICATION;
ALTER ROLE
product_inventory=# ALTER USER ggreplication WITH SUPERUSER;
ALTER ROLE
product_inventory=# GRANT USAGE ON SCHEMA ordermanager TO ggreplication;
GRANT
product_inventory=# GRANT USAGE ON SCHEMA data_analyze TO ggreplication;
GRANT
product_inventory=# GRANT SELECT ON ALL TABLES IN SCHEMA ordermanager TO ggreplication;
GRANT
product_inventory=# GRANT SELECT ON ALL TABLES IN SCHEMA data_analyze TO ggreplication;
GRANT
product_inventory=# GRANT CREATE ON DATABASE product_inventory  TO ggreplication;
GRANT
product_inventory=# GRANT CREATE, USAGE ON SCHEMA ordermanager TO ggreplication;
GRANT
product_inventory=# GRANT CREATE, USAGE ON SCHEMA data_analyze TO ggreplication;
GRANT
product_inventory=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ordermanager TO ggreplication;
GRANT
product_inventory=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data_analyze TO ggreplication;
GRANT
product_inventory=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ordermanager TO ggreplication;
GRANT
product_inventory=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA data_analyze TO ggreplication;
GRANT
product_inventory=#

    For more information about specific purpose of each privileges, see Prepare Database Users and Privileges for PostgreSQL.

  3. The following are the parameters in the PostgreSQL database configuration file. The location of the configuration file is $PGDATA/postgresql.conf.

    wal_level = logical   **# set to logical for Capture**

max_replication_slots = 1 **# max number of replication and one slot for Extract/Client**

max_wal_senders = 1 **# one sender per max repl slot**

track_commit_timestamp = on **# optional, correlates tx commit time**  with begin tx log record (useful for  # timestamp-	based positioning)

    Note:

    • After making any of the preceding changes, restart the database.
    • OCI GoldenGate replication supported PostgreSQL data types and limitation on the supported data types, supported objects and operations can be verified from the documentation. For more information, see Prepare Databases.

Task 5: Create Initial Load Extract

We need to create initial load Extract and this will provide the LSN on OCI GoldenGate Extract report section.

  1. Go to the OCI Console, navigate to Database, GoldenGate, Deployments and click the deployment name created in Task 2.

    enter image description here

  2. Click Launch Console and enter login credentials.

    enter image description here

  3. Go to DB Connections and this should display the assigned source and OCI Database with PostgreSQL database connections and verify the connectivity check.

    enter image description here

    Successful connection is good to start initial load at this stage.

  4. Go to the OCI GoldenGate Console, navigate to Extract and click +.

    enter image description here

    enter image description here

    enter image description here

    Once the initial load Extract is stopped, verify the OCI GoldenGate report files and note the LSN details and keep it handy to use in Task 9.

    enter image description here

Task 6: Backup Source Database Metadata using the pg_dump Command

The pg_dump command is used to dump the source database product_inventory metadata only.

pg_dump -h localhost  -U ggreplication -d product_inventory -F c -v -s --file=/tmp/exp_source_db.dump

Task 7: Import Metadata into OCI Database with PostgreSQL

Use the pg_restore binary to import into target OCI Database with PostgreSQL, here the OCI database name is targetdb.

Run the restore command from OCI Compute node in which we have successfully executed connection command in Task 1.8.

pg_restore -h <OCI IP address>  -U admin -d targetdb  -v exp_source_db.dump

postgres=> \c targetdb
psql (15.12, server 15.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
You are now connected to database "targetdb" as user "admin".
targetdb=> \dn
         List of schemas
     Name     |       Owner
--------------+-------------------
 data_analyze | admin
 ordermanager | admin
 public       | pg_database_owner
(3 rows)

Note: Use the select table_name,table_schema from information_schema.tables where table_type='BASE TABLE' and table_schema in ('data_analyze','ordermanager') order by table_schema,table_name; query to verify the table names after metadata import on target.

Task 8: Create Initial Load Replicat using Trail File

  1. Go to the OCI GoldenGate Console, navigate to Replicats and click + to create Replicat.

    enter image description here

    enter image description here

    enter image description here

  2. Click Create and run to start initial load Replicate, once it is completed, verify the table row counts.

  3. Go to Reports and verify the initial load Replicate reports. Verifying the few table rows counts at both databases.

    enter image description here

    enter image description here

Task 9: Create CDC Extract Altering to Starting LSN for Capture

CDC Extract is online extract used to capture ongoing changes on existing database and CDC Extract starts capturing from LSN number collected.

  1. Go to the OCI GoldenGate Console, navigate to Extracts, click + and enter the required information as shown in the following images.

    enter image description here

    enter image description here

    enter image description here

    enter image description here

  2. Once CDC is registered and created, click Start with Options to instruct reading from specific LSN which is collected in Task 5.

    enter image description here

    enter image description here

    We can see zero seconds lag on CDC Extract after some time.

    enter image description here

Task 10: Create Online Replicate giving CDC Extract Trail File

  1. Go to the OCI GoldenGate Console, navigate to Replicats, click + and enter the required information as shown in the following images.

    enter image description here

    enter image description here

  2. Click Create and Run after modifying the MAP command.

    enter image description here

    We can see zero seconds lag on Online Replicat after some time.

    enter image description here

Verification

Run the following queries to insert into source table data_analyze.table3 replicates to target database data_analyze.table3 table.

enter image description here

Target side verification table count and data.

enter image description here

Acknowledgment

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.