Oracle Cloud Infrastructure Documentation

Importing, Exporting, and Migrating Databases

Migrate data from another PostgreSQL database to an OCI Database with PostgreSQL database system.

Using PostgreSQL Utilties Such as pg_dump

The pg_dump utility comes with a PostgreSQL installation by default and can be used to extract a PostgreSQL database into a script file or other archive file. These files can be provided to an OCI Database with PostgreSQL with psql or pg_restore commands to re-create a database in the same state at the time of its dump.

When you create an OCI Database with PostgreSQL, you specify an admin user. That user can restore from a file created using these utilities. Because these utilities are regular PostgreSQL client applications, you can perform this migration procedure from any remote host that has access to the database.

Note

This guide uses pg_dump to create the dumps in plain text format and the psql utility to restore the dump. You can also create dumps in a different format and use pg_restore to restore the dumps.

Example: Export and import all the databases of a database system

The following example assumes that the source database system is a vanilla PostgreSQL system, with three databases: db_1, db_2, and db_3. The source database system has many users, some of them with SUPERUSER privileges.

  1. Take the schema-only dump of all the databases. Dump each database into an individual file with the object ownership information of users.

    /usr/lib/postgresql/bin/pg_dump -U psql -h <IP_of_Source_DB> -s -E 'UTF8' -d <db_1> -f <db_1_schema_dump>.sql
    • -U: User creating the dump
    • -h: Source database host address
    • -s: Dump only schema, no data
    • -E: Set the client encoding of the dump file to UTF-8
    • -d: Database to dump
    • -f: O/p file to dump database schema

    Repeat this for databases db_2 and db_3.

  2. Create a data-only dump of each of the databases into individual files.

    /usr/lib/postgresql/bin/pg_dump -U psql -h <IP_of_Source_DB> -a -E 'UTF8' -d db_1 -f <db_1_data_dump>.sql
    • -a: Dump only the data, not the schema

    Repeat this for databases db_2 and db_3.

  3. Dump global objects without tablespace information.

    /usr/lib/postgresql/bin/pg_dumpall -U psql -h <IP_of_Source_DB> -g --no-role-passwords --no-tablespaces -f <all_roles>.sql
    • -g: Dump only global objects, no databases
    • --no-role-passwords: Use this flag to avoid dumping of passwords.
    • --no-tablespaces: OCI Database with PostgreSQL supports only in-place tablespaces.

  4. Because the admin user of the OCI Database with PostgreSQL database system doesn't have SUPERUSER privileges, NOSUPERUSER, NOREPLICTION, and so on, need to be removed from the CREATE USER statements in the dump.

    Make the necessary changes in the global dump file to remove any commands that need SUPERUSER privileges. For example:

    ALTER ROLE/USER test WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'test';

    Should be modified to:

    ALTER ROLE/USER test WITH LOGIN PASSWORD 'test';

  5. Restore the global dump using the OCI Database with PostgreSQL admin user onto the OCI Database with PostgreSQL database system to create all the roles/users:

    /usr/lib/postgresql/bin/psql -U psql -d postgres -h <IP_of_Target_Database_System> -f <all_roles>.sql

  6. Restore the schema-only database dumps:

    /usr/lib/postgresql/bin/psql -U psql -d postgres -h <IP_of_Target_Database_System> -f <db_1_schema_dump>.sql

    Repeat this for databases db_2 and db_3.

    Note

    Fix any errors with privileges or object mismatches before proceeding further.

  7. Restore the data-only database dumps:

    /usr/lib/postgresql/bin/psql -U psql -d postgres -h <IP_of_Target_Database_System> -f <db_1_data_dump>.sql

    Repeat this for databases db_2 and db_3.

  8. Verify the row counts of all the tables against the source database.
  9. Ensure that all the source database system's permission levels are accurately reflected in the OCI Database with PostgreSQL database system.
  10. Set passwords for the users created in the OCI Database with PostgreSQL database system.

  11. Run VACUUM ANALYZE on each database or individual tables to update the stats of the databases. This command helps the PostgreSQL query planner create optimized query plans, resulting in better performance. To speed up the completion of VACUUM ANALYZE, we recommend increasing the maintenance_work_mem in the PSQL session. VACUUM ANALYZE can also be run in separate sessions in parallel to reduce the completion time.

    SET maintenance_work_mem = '<a_few_gigabytes_depending_on_shape>';
    VACUUM ANALYZE <db_1>;

    Repeat this for databases db_2 and db_3.

    Note

    We recommend running VACUUM ANALYZE and REINDEX periodically on bloated tables and indexes to maintain database system performance. Run this operation during off-peak hours to avoid any impact to applications.

Using GoldenGate

You can use GoldenGate to set up replication from a source PostgreSQL database to an OCI Database with PostgreSQL database system.

The following steps cover configuring source, target, and GoldenGate to keep databases in sync. You can choose to cut over to OCI Database with PostgreSQL at any point, with zero downtime, after the process is successfully completed.

  1. Preparing the Source Database
  2. Dumping the Source Database Schema
  3. Preparing the Target Database System
  4. Using GoldenGate for Replication

Prerequisites

To use GoldenGate, ensure that the following prerequisites are met:

  • Verify that the required IAM resources and OCI network are in place. For more information, see Getting Started with OCI Database with PostgreSQL.

  • Create a database system that can be used as the destination for the GoldenGate replication.

  • Create compute instances to host the PostgreSQL client that can connect to the source and destination database systems. When you create the instances:

    • Download the SSH key pair.

      Caution

      Anyone who has access to the private key can connect to the instance. Store the private key in a secure location.
    • Select the VCN and private subnet used when you created the database system.
    • Install a version of the PostgreSQL client that's compatible with the PostgreSQL version of the database system by following the instructions at https://www.postgresql.org/download/.

Preparing the Source Database

Source databases must meet user and configuration requirements in order for GoldenGate to extract and replicate data.

User Requirements

GoldenGate processes require a database user that can capture source data and deliver it to OCI Database with PostgreSQL. We recommend creating a dedicated PostgreSQL database user for both GoldenGate Extract and GoldenGate Replicat. For more information about database users and GoldenGate, see Database Privileges for Oracle GoldenGate for PostgreSQL.

Configuration Requirements

Any source database need to be configured to accommodate the GoldenGate extract process. The required parameters with their values for any source database follow:

  • listen_addresses: For remote connectivity of an Extract or Replicat, set "listen_addresses = * " to allow for remote database connectivity.
  • wal_level: Write-ahead logging on the source database must be set to logical, which adds information that's necessary to support transactional record decoding.
  • max_replication_slots: The number of maximum replication slots must be set to accommodate one open slot per GoldenGate Extract. In general, no more than one GoldenGate Extract is needed per database.
    Note

    If the source database is already using PostgreSQL native replication and all the available replication slots, increase the value to allow for the registration of a GoldenGate Extract.
  • max_wal_senders: Set the maximum write-ahead senders value to match the maximum replication slots value.
  • track_commit_timestamp: Optionally, commit timestamps can be enabled in the write-ahead log. If enabled at the same time that logical write-ahead logging is enabled, DML commit records from that point on are captured with the correct timestamp value. Otherwise, the first record captured by GoldenGate will have an incorrect commit timestamp.

To use Community PostgreSQL as a source database, the user can make configuration changes in the postgresql.conf file and restart the database to make the changes effective:

listen_addresses = *
wal_level = logical                      
max_replication_slots = 1  (min required for GG)         
max_wal_senders = 1  (min required for GG)                       
track_commit_timestamp = on
Amazon Aurora PostgreSQL as Source

In Amazon Aurora PostgreSQL, database settings are changed using parameter groups. Review the Amazon AWS documentation for information on how to edit database settings within a new parameter group and assign it to a database instance.

Ensure that the database configuration requirements are met by verifying settings in the parameter group assigned to the instance.

The wal_level setting for Amazon Aurora is configured with a parameter called rds.logical_replication. Set rds.logical_replication to 1 to use the database a source for GoldenGate Extract.

The database user which is being used for migration must have replication privileges. Grant privileges to the user in the source Amazon Aurora system using the following: 

grant replication to <migration-user>;
Amazon RDS for PostgreSQL as Source

In Amazon RDS for PostgreSQL, database settings are changed using parameter groups. Review the Amazon AWS documentation for information on how to edit database settings within a new parameter group and assign it to a database instance.

Ensure that the database configuration requirements are met by verifying settings in the parameter group assigned to the instance.

The wal_level setting for Amazon database services is configured with a parameter calledrds.logical_replication. Set rds.logical_replication to 1 to use the database a source for GoldenGate Extract.

The database user which is being used for migration must have replication privileges. Grant privileges to the user in the source Amazon RDS system using the following: 

grant replication to <migration-user>;
Azure Database for PostgreSQL as Source

In Azure Database for PostgreSQL, database settings are changed using server parameters for the database instance. Review the Azure Database for PostgreSQL documentation for information on how to edit database settings.

Ensure that the database configuration requirements are met by verifying settings on the source instance.

When configuring GoldenGate for a PostgreSQL Extract against an Azure Database for PostgreSQL database, wal_level must be enabled and set to LOGICAL.

The database user which is being used for migration must have replication privileges. Grant privileges to the user in the source system using the following: 

ALTER ROLE <migration-user> WITH REPLICATION;

Dumping the Source Database Schema

Test connectivity from the Compute instance to the source database system and then dump the schema of the source database using the following command:

/<path-to-binary>/pg_dump -U <user-to-take-dump> -h <IP-of-source-DB> -s -E 'UTF8' -d <source-DB-name> -f schema_only.sql
  • -U: User creating the dump
  • -h: Source database host address
  • -s: Dump only schema, no data
  • -E: Set the client encoding of the dump file to UTF-8
  • -d: Database to dump
  • -f: O/p file to dump database schema

This schema is used when Preparing the Target Database System. See Using PostgreSQL Utilties Such as pg_dump for more information on pg_dump and OCI Database with PostgreSQL.

Preparing the Target Database System

Ensure that the prerequisites for using GoldenGate to replicate data into OCI Database with PostgreSQL are met, the source database is prepared, and then restore the schema of the source database to the OCI Database with PostgreSQL target using the following command:

/<path-to-binary>/psql -U <user-to-load-dump> -d <target-DB-name> -h <Private_endpoint_of_target_database_system> -f schema_only.sql
  • -U: User restoring the dump
  • -h: Destination database host address
  • -d: Database to dump
  • -f: O/p file to dump database schema

See Using PostgreSQL Utilties Such as pg_dump for more information on psql and OCI Database with PostgreSQL.

Tip

For more information about using an instance to connect to a database system, see Connecting to a Database.

Using GoldenGate for Replication

Use the following steps to set up GoldenGate for use with OCI Database with PostgreSQL. Use the compartment that contains the destination OCI Database with PostgreSQL database system.

  1. Create a deployment and select PostgreSQL as the technology.
  2. Create a source connection based on the source database and test connectivity.

  3. Create a PostgreSQL server destination connection and test connectivity.

    Note

    For network connectivity, select Dedicated endpoint as the traffic routing method.
  4. Assign both the source and destination connections to the deployment.
  5. Add an Extract for PostgreSQL to the deployment to run against the source connection and extract, or capture, data.
    1. Enable supplemental, table-level logging for source database. Use the schema name created when you dumped the source database schema. For details, see the step Enable supplemental logging from Add an Extract for PostgreSQL: Before you begin.
    2. Add an Initial Load Extract (INI).
      1. Provide a Trail name, for example, xx. This file holds the changes happening on source database.

      2. On the Parameter File page, ensure that the following are included in the file:

        exttrail xx
INITIALLOADOPTIONS USESNAPSHOT
TABLE *.*;

        INITIALLOADOPTIONS must be added to ensure a consistent snapshot of the database after the initial load extract completes along, with the LSN number.

      3. Select Create and Run.
      4. Monitor the process until complete and then check the report file for the LSN number. Record the LSN number for future use.
  6. Add another Extract to the deployment. This time, choose Change Data Capture Extract (CDC) as the extract type.
    1. For Begin, select None.
    2. Provide a Trail Name, for example, yy.

    3. On the Parameter File page, ensure that the following are included in the file:

      exttrail yy
TABLE public.*;

      INITIALLOADOPTIONS must be added to ensure a consistent snapshot of the database after the initial load extract completes along, with the LSN number.

    4. Select Create. Don't run the extract yet.
    5. On the deployment page, from the Action menu of the new CDC, select Start with Option.
    6. For Start Point, select At CSN
    7. For CSN, enter the LSN number from the initial extract and select Start. Transactions on the source database will be captured and shown in the Statistics tab of the CDC Extract.
    Note

    So far, we've only been working with the source database. The start of the Initial Load Extract and CDC records changes in the source in the respective trail files on the deployment. The Replicat process delivers the data from these trail files to destination OCI Database with PostgreSQL database system.
  7. Add a Replicat for PostgreSQL to the deployment. This Replicat is for the Initial Load Extract (REINI).
    1. Create a checkpoint table. For details, see Add a Replicate for PostgreSQL: Before you begin.
    2. When creating the Replicat, provide the Trail Name from the INI, for example, xx.
    3. Provide the Checkpoint Table for the Replicat.

    4. On the Parameter File page, ensure that something such as the following is included in the file:

      MAP public.* TARGET public.*;
    5. Select Create and Run. Verify that data has started loading into the OCI Database with PostgreSQL database system.
  8. Add a Replicat for PostgreSQL to the deployment. This Replicat is for the Change Data Capture Extract (RECDC).
    1. When creating the Replicat, provide the Trail Name from the CDC, for example, yy.
    2. Provide the Checkpoint Table for the Replicat.

    3. On the Parameter File page, ensure that something such as the following is included in the file:

      MAP public.* TARGET public.*;
    4. Select Create. Don't run the RECDC until the REINI completes. The GoldenGate should have two Extract processes and two Replicat processes.
    5. On the deployment page, from the Action menu of the new RECDC, select Start.
  9. Check the status on the target OCI Database with PostgreSQL database system and match the record counts and values.