Using tools to migrate Postgres schemas v1.3.2

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 -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 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.