Using tools to migrate Postgres schemas

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. This section provides 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, or employing pgAdmin, or psql.

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.