Preparing and importing the schema

Before you use EDB Data Migration Service (EDB DMS) to configure a data migration that uses the cdcreader, cdcwriter or both, you must prepare and import your schema to the destination database.

Importing the schema to the destination database before the migration takes place is important as EDB DMS only migrates data. The destination database must have the schemas in place for the migration to populate them with data.

Some of your schema's constraints must be included before the data migration takes place, whereas others must be applied after the data migration is completed. This ensures you can migrate without performance degradation.

Schema integrity and performance considerations

The presence of destination database constraints, triggers, and WAL logging can impact the data migration performance. When possible, EDB recommends a two-step import of schema constraints.

Primary key and unique constraints

Primary key/Unique constraints are leveraged by EDB DMS to provide an exactly-once delivery when migrating data to the destination database. Therefore, Primary key/Unique constraints should be included in the schema import that you perform before the data migration begins. Other types of constraints should be excluded from the schema import.

For rows in tables that do not have Primary key/Unique constraints it is only possible to achieve at-least-once delivery. Deduplication can be performed during the data migration verification.

Foreign key, check, and exclusion constraints

EDB DMS is able to apply change events in parallel against destination database clusters. However, migrating some constraint types can negatively affect the performance of the migration. These type of constraints lead to unnecessary CPU and memory utilization in the context of an in-flight data migration from a consistent and referentially integral source database.

Accordingly, EDB recommends applying foreign key, check, and exclusion constraints on the destination database only after the migration has completed.

Preparing your schema

You can use several different methods to prepare your schema, exclude unsupported constraints and import it to the destination database. This section provides guidelines for how to export and import your schemas for Oracle or Postgres migrations using Migration Portal or Migration Toolkit.

Other valid routes for migrating DDL to import Postgres schemas include manually creating the schemas in the destination database, performing a pg_dump to obtain the schemas and pg_restore to restore them, or employing pgAdmin, or psql.

Oracle to EDB Postgres Advanced Server

This is the recommended way to handle schema export/import in Oracle to EDB Postgres Advanced Server migrations.

Export

Use EDB Migration Portal to assess Oracle database sources for schema compatibility before starting the data migration process.

EDB Migration Portal offers the ability to separate constraints from other destination DDL with the offline migration option.

Ensure you exclude foreign key, check, and exclusion constraints from the SQL-formatted DDL before importing the schema to the destination database.

Import

After you have prepared the DDL and excluded foreign key, check, and exclusion constraints, connect to the destination database and import the SQL-formatted DDL file.

Continue using Migration Portal to import the schema in offline or online mode.

Postgres to Postgres

This is the recommended way to handle schema export/import in Postgres to Postgres migrations. See Product compatibility for an overview or supported Postgres source and destination database versions.

Export

For data migrations from Postgres, EDB recommends using EDB Migration Toolkit to manage the schema. MTK's offline migration capability provides an easy way to extract a database's schema and separate constraints. Here are some guidelines:

• Use the -offlineMigration option, so Migration Toolkit can generate SQL scripts that you can apply on destination without having to configure connectivity.

• Use the ‑schemaOnly <schema_scope> option, so Migration Toolkit can generate scripts that focus on the export of schemas only.

• After you run Migration Toolkit, ensure you exclude (comment out) the following constraints from the generated SQL script before applying it on the destination database

  • foreign key constraints
  • check constraints
  • exclusion constraints

Import

After you have prepared the DDL and excluded foreign key, check, and exclusion constraints, connect to the destination database and import the SQL-formatted DDL file.

Execute the generated offline migration script to start the import.