Note:

Migrate Data from On-premises PostgreSQL Database to OCI Database with PostgreSQL

Introduction

PostgreSQL is a fully managed service that saves your time on routing tasks such as patching, backups and storage management. Our special feature is database-optimized storage architecture which decouples a SQL transaction processing engine from the storage layer, and it boosts the system resilience and performance, and it is designed to be 60 percent less expensive than Amazon Aurora and it offers around three times more performance than an open-source PostgreSQL deployment. In addition to that it is fully compatible with open-source PostgreSQL.

image

We can use PostgreSQL for analytical processing. PostgreSQL is based on HTAP (Hybrid transactional/analytical processing) architecture, so it can handle both Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP). We can use PostgreSQL for Create-Read-Update-Delete (CRUD) operations.

Spatial data can be used with PostGIS which is a spatial database extender. PostgreSQL offers additional support for running location queries on geographic objects in SQL and spatial data analysis. PostGIS is highly standard compliant, provides hundreds of functions for processing geometric data in various formats, and can be used with both Quantum Geographic Information System (QGIS) and GeoServer for easier handling of geodata.

In this tutorial, we will discuss how to migrate data from an on-premises PostgreSQL Database to Oracle Cloud Infrastructure (OCI) Database with PostgreSQL. PostgreSQL major versions are represented by the first two digit groups of the version number, for example, 14.9. We will migrate the database by migrating data via pg_dump from one major version of on-premises PostgreSQL and reload it in another OCI. Since OCI Database with PostgreSQL version 14.9 is available now, you can test migrating data from on-premises PostgreSQL to OCI Database with PostgreSQL.

Objectives

Prerequisites

Considerations

Source Environment

Target Environment

Task 1: Create a PostgreSQL database on OCI

  1. Go to OCI Console, navigate to Databases and PostgreSQL.

    image

  2. Click Create Database System to create a PostgreSQL database system.

    image

  3. In the Selection creation type, click Create new Database system.

    image

    It only supports PostgreSQL version 14 and higher.

    image

  4. We will create a single node cluster, but we can add more nodes so enter Node count as 1 and Performance tier supported is 300k IOPS. In that case, the first node will act as a primary and rest of the nodes will act as replica or secondary nodes.

    image

  5. Change the Shape and RAM according to the usage.

    image

    It supports the private endpoint, you cannot have a public IP address created for PostgreSQL OCI.

    image

    Once we have provisioned it, we can see the details of private endpoint and FQDN in General Information which is associated with it.

    image

    As mentioned earlier, it only creates private endpoint so you cannot directly connect to PostgreSQL. If we need to connect from local pgAdmin client, we have to create a Bastion session for port 5432 (optional) for this endpoint and connect from local pgAdmin.

    image

    In order to connect to the PostgreSQL database on OCI, we can use a compute instance.

    image

  6. Create a database named dvdrental_oci.

    image

Task 2: Transfer Objects and Data on the target OCI PostgreSQL database system

In this task, we use pgAdmin dump utility in order to export dump from the on-premises PostgreSQL database, our source database version is 13.13.

image

We will export dump from on-premises PostgreSQL database.

image

Note: In case your target database is going to be Autonomous Data Warehouse, Oracle Database Cloud Service or Oracle Database, so you can even use OCI GoldenGate to do the initial load as well as migrate an on-premises PostgreSQL database to OCI Database with PostgreSQL (online) with OCI GoldenGate.

Once it is finished, then move to the target side and start the import. Before starting the import, grant the permission on OCI using the following command.

GRANT ALL PRIVILAGES ON DATABASE dvdrental_oci to postgres;

image

We need to load the data copied from the on-premises PostgreSQL database into this OCI Database.

image

Task 3: Verify the Transferred Database Objects and Data

Once it gets imported, verify the transferred database objects and data on the target OCI PostgreSQL database system to ensure that the migration was successful and there are no issues or discrepancies.

image

image

Acknowledgments

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.