Note:

Migrate to Oracle Cloud Infrastructure Database with PostgreSQL using OCI Object Storage and Rclone

Introduction

Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) is a fully managed service that reduces the time spent on routine tasks such as patching, backups and storage management. It’s key features include a database-optimized storage architecture that decouples the SQL transaction processing engine from the storage layer, multiple replicas to boost user activity, cross-regional backup copies, and full compatibility with the open-source PostgreSQL database.

OCI Database with PostgreSQL architecture

Note: PostgreSQL versions 14 and 15 are supported (as of June 2025).

OCI Object Storage provides scalable, durable, low-cost storage for any type of data. It this tutorial, it will be used to store PostgreSQL dump file.

Rclone is an open source, command-line program to manage or transfer files on cloud storages.

In this tutorial, we will discuss how to effectively migrate data from an on-premises PostgreSQL database to OCI Database with PostgreSQL. We will use the pg_dump and pg_restore utilities leveraging cost effective OCI Object Storage and Rclone for fast, multipart upload and the mount feature.

Objectives

Prerequisites

Note OCI Database with PostgreSQL is not available in the OCI Always Free Tier.

Simplified architecture

Task 1: Create OCI API Key User Credentials

  1. Log in to the OCI Console, click the profile menu Profile menu icon and select My profile.

  2. In Resources, click API Keys.

  3. Click Add API Key at the top left of the API keys list.

  4. Click Download Private Key and save the key to your .oci directory. In most cases, you do not need to download the public key.

  5. Click Add.

The key is added and the Configuration File Preview is displayed. The file snippet includes required parameters and values that you will need to create your configuration file.

For more information, see Required Keys and OCIDs.

Task 2: Export PostgreSQL Data with pg_dump

In your on-premises host, use pg_dump to export PostgreSQL database.

pg_dump -U your_username -h your_host -p your_port -F c -f /path/to/output.dump your-database-name

Task 3: Install, Configure and Transfer Data with Rclone

  1. In your on-premises host, install rclone.

    sudo -v ; curl https://rclone.org/install.sh | sudo bash

  2. Run the following command to configure rclone.

    rclone config

    For more information to configure OCI Object Storage connection, see Rclone documentation. Use Option 2: an OCI user and an API key for authentication (created in Task 1).

  3. Use rclone multipart upload to copy the exported database to an OCI Object Storage bucket. For example:

    rclone --progress  --oos-no-check-bucket --fast-list --no-traverse --transfers 8 --oos-chunk-size 10M --oos-upload-concurrency 10 --checkers 10  copy <source> <dest>

    Example command and an output:

    	rclone --progress  --oos-no-check-bucket --fast-list --no-traverse --transfers 8 --oos-chunk-size 10M --oos-upload-concurrency 10 --checkers 10  copy output.dump OCI:bucket_db-dump
	Transferred:        4.500 KiB / 4.500 KiB, 100%, 0 B/s, ETA -
	Transferred:            1 / 1, 100%
	Elapsed time:         0.0s

    For more information, see Copy Data to Oracle Cloud Using Rclone to Build Insights in Oracle Analytics Cloud.

Task 4: Mount OCI Object Storage using Rclone

  1. In your OCI Compute bastion host, install PostgreSQL tools.

    dnf install postgresql

    OCI Database with PostgreSQL supports versions 14 and 15, so install PostgreSQL tools accordingly.

    sudo dnf module reset postgresql
sudo dnf module enable postgresql:15
sudo dnf install postgresql

  2. Install and configure rclone in your bastion host - repeat Task 3, Step 2.

  3. Mount your bucket with your data copied in Task 3. For example, /mnt/oci.

    rclone mount OCI:bucket_db-dump /mnt/oci --vfs-cache-mode full --log-file /home/opc/rclone.log --config ~/.config/rclone/rclone.conf --log-level DEBUG --daemon --attr-timeout 1s

  4. Check files on your mounted OCI Object Storage.

    ls /mnt/oci

Task 5: Restore OCI Database with PostgreSQL using pg_restore

Restore your OCI Database with PostgreSQL database with parallel jobs, in a background, as a daemon process, using mounted file system.

nohup pg_restore -d test -h <OCI PostgreSQL hostname or IP> -p 5432 -U <dbuser> -v -c -j 10 -F c /mnt/oci/output.dump >import.log 2>&1

The target database (test in this example) must already exist before running pg_restore. To create the database beforehand:

createdb -U postgres -h localhost -p 5432 test

You can also add –verbose for progress details or –clean to drop objects before recreating them.

Task 6: Verify the Restored Database

Using psql, log in to OCI Database with PostgreSQL and verify completeness of restored data.

psql -h <OCI PostgreSQL hostname or IP> -p 5432 -U <dbuser> -d test

Now you can use SQL select statements to verify the number of rows in tables.

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.