Preparing and importing the schema
Suggest editsBefore you use EDB Data Migration Service (EDB DMS) to configure a data migration, you must prepare and import your schema to the target database.
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 target 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 and UNIQUE constraints are leveraged by EDB DMS to provide an exactly-once delivery when migrating data to the target database. Therefore, PRIMARY KEY and 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 or UNIQUE constraints it is only possible to achieve at-least-once delivery. Deduplication can be performed during the data migration verification.
Note
NOT NULL constraints don't represent a significant performance impact for destination servers and can also be included in the schema import.
FOREIGN KEY, REFERENCES, CHECK, CASCADE and EXCLUDE 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.
EDB recommends applying the following constraints on the target database after you have signilized the end of the CDC stream by marking the migration as completed in the Console.
FOREIGN KEY / REFERENCES
CHECK
CASCADE
EXCLUDE
Preparing and importing your schema
Prerequisite
You created a schema in the target database.
Prepare your schema
Oracle to EDB Postgres Advanced Server migrations
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, REFERENCES, CHECK, CASCADE and EXCLUDE constraints from the DDL before importing the schema to the target database.
Other migrations
For data migrations to and 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.
Ensure you exclude FOREIGN KEY, REFERENCES, CHECK, CASCADE and EXCLUDE constraints from the DDL before importing the schema to the target database.
Tools such as pg_dump and pg_restore are another valid route for migrating DDL.
Import your schema to the target database
After you have prepared the DDL, and excluded FOREIGN KEY, REFERENCES, CHECK, CASCADE and EXCLUDE constraints, connect to the target database and import the SQL-formatted DDL file.
You can use pgAdmin, psql or a different tool to perform the import.
Could this page be better? Report a problem or suggest an addition!