Migrate data to AlloyDB Omni

Select a documentation version:

You can migrate your data from PostgreSQL or Oracle to AlloyDB Omni. This page assumes that you're familiar with PostgreSQL and Oracle utilities for configuration and use.

Migrate from PostgreSQL to AlloyDB Omni

Migrating from PostgreSQL to AlloyDB Omni is facilitated by the fact that AlloyDB Omni is based on PostgreSQL.

To migrate data from PostgreSQL to AlloyDB Omni, follow these steps:

  1. Verify that any extensions in use on the source PostgreSQL database are supported on AlloyDB Omni. While some extensions are included with AlloyDB Omni, you need to install other extensions manually.

    To learn which extensions are included with AlloyDB Omni, see Supported database extensions.

  2. To migrate your data, use the tools in the following table, based on data migration complexity and downtime.

    Migration type Tool
    Basic migration with sufficient downtime Use pg_dump to back up the existing PostgreSQL database. Use pg_restore to restore the backup to the new AlloyDB Omni database.
    Complex migration with sufficient downtime Use pgloader to stream data from the source database to the destination database.
    Complex migration with minimal downtime Use pglogical to replicate data from the source database to the destination database.

  3. Install the extensions on the destination AlloyDB Omni system before you migrate anything from the source system.

Migrate from Oracle to AlloyDB Omni

Data migration from Oracle to AlloyDB Omni often involves converting Oracle PL/SQL code to PgSQL. The amount and complexity of the PL/SQL code often determines the effort required to migrate from Oracle to AlloyDB Omni. The more Oracle-specific features that are in use, the more likely it is that you need to perform some manual work for code conversion.

The following table lists open source and commercial tools to help you perform code conversions.

Use case Tool or document Description
Describes concepts and code or syntax differences between Oracle and AlloyDB Omni. Migrating Oracle® Database users and schemas to AlloyDB Omni --
You have sufficient downtime to move the volume of data in Oracle and convert all Oracle resident application logic to PostgreSQL. Use Ora2Pg for schema conversion, data movement, and code conversion. Ora2Pg is an open source tool that helps you convert from Oracle to PostgreSQL-based databases.
You have minimal downtime for the migration. To migrate the code and the schema, use the following options:
  • Open source option
    Ora2Pg for schema conversion, data movement, and code conversion
  • Third-party commercial option
    Ispirer for schema and code conversion
To migrate data, perform an initial load prior to switchover, then use change data capture and incremental apply to keep in sync until switchover.
  • Open source option
    Use Ora2Pg for one-time data movement if there is a large enough downtime window to migrate all the data at one time. This option doesn't provide change data capture or incremental apply.
  • Commercial options
    Ispirer can perform initial data movement, then batch-mode incremental apply.

    Striim can perform initial data movement, then continuous change data capture and incremental apply for minimal downtime. To support fallback, this option replicates from AlloyDB Omni back to Oracle.
  • Striim is a commercial product that helps you migrate data between database platforms. It performs initial data movement plus continuous replication, and it can provide data transformation during migration. For more information, see Striim.
You need to load data from various sources into PostgreSQL-based databases. Use pgloader. pgloader is an open source tool for loading data from various sources into PostgreSQL-based databases.
For more information, see pgloader's documentation.
You need to emulate Oracle packages and functions. Use the Orafce extension. For more information, see The "orafce" project.

This extension isn't included with AlloyDB Omni and must be installed manually. For more information, see Install Orafce for AlloyDB Omni.
You want to migrate Oracle database links to AlloyDB Omni. Use the oracle_fdw extension. This extension provides connectivity to Oracle databases from within AlloyDB Omni and lets you migrate Oracle DBLinks to AlloyDB Omni. This extension isn't included with AlloyDB Omni and must be installed manually.
For more information, see GitHub - laurenz/oracle_fdw: PostgreSQL Foreign Data Wrapper for Oracle.

What's next