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.