If you want HM to manage schema migration for you, no manual preparation is needed — select the Schema and data scope when creating your migration and HM handles the rest. If you prefer to manage the schema yourself, use the instructions below to export, prepare, and import it before creating the migration.
No separate schema migration is required. HM handles schema import automatically when you select the Schema and data scope later. Continue to the next step.
Schema handling for Postgres databases
You can use several different methods to prepare your schema, exclude unsupported constraints, and import it to the destination database. The following are guidelines for how to export and import your schemas for Postgres migrations using 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
- Using pgAdmin or psql
Export
For data migrations from Postgres, we recommend 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
-offlineMigrationoption, 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 only on the export of schemas.After you run Migration Toolkit, 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
After you run Migration Toolkit, and if you plan to map objects to different names in the destination, modify the schema SQL script to reflect the new names. After the SQL script is generated, edit the object names directly in the script (DDL) before applying it to the destination. Also make sure that the structure and data types of the renamed object in the script exactly match the source. If you manually update object names in the DDL scripts, also update the names in other objects that reference the updated objects. For example, if you rename a table, also update any views or constraints that reference the original table name.
Later, during data migration, select the newly assigned object names. See Mapping schema, tables and columns during a migration for more information.
Import
After you prepare the DDL and exclude foreign key, check, and exclusion constraints, connect to the destination database and import the SQL-formatted DDL file.
To start the import, execute the generated offline migration script.
Note
Apply the excluded foreign key, check, and exclusion constraints on the destination database after the migration completes successfully. Keep track of the excluded constraints so you can apply them when the time comes.