Understanding schema handling v1.2
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.
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, 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.