Performing a schema migration v4

After resolving errors in your schemas, you can migrate the schemas to an EDB Postgres Advanced Server cluster (i.e., instance).

The Migration Portal provides guided steps for migrating your schemas to one of the following target database options:

  • Existing on-premises EDB Postgres Advanced Server
  • New on-premises EDB Postgres Advanced Server
  • EDB Postgres Advanced Server in the cloud

The first two options above lead to the creation of Data Definition Language (DDL) SQL files which can be used to perform an offline and manual loading of the schemas into the target database using a client application such as the psql client, pgAdmin, or Toad Edge. The third option above provides the ability to perform an online migration of the schemas to the target database by directly connecting to the target database.

Migrating to an existing on-premises EDB Postgres Advanced Server host

You can migrate schemas to an existing on-premises EDB Postgres Advanced Server on Windows or Linux platforms.

  1. Select Existing on-premises EDB Postgres Postgres Advanced Server.

  2. Select one or more schemas to migrate to EDB Postgres Advanced Server. You can also expand a schema and select specific object types under each schema.

    Schema and objects are selected

    Note

    If your schemas aren't 100% compatible, a banner appears. Complete the Contact Us form as needed.

  3. Select Download SQL file, to download the assessed schema. The zip package consists of SQL files with DDLs for your selected schemas and objects. The DDLs are grouped in SQL files by project, schemas, or objects.

    File nameDescriptionExample
    <ProjectName> _ALL_OBJECTS.sqlA single consolidated file consisting of DDLs for all your selected schemas and objects.AllTestMigration_ALL_OBJECTS.sql will consist of DDLs for all objects of schemas OE, SH, IX, and PM, and only selected objects of schema HR.
    <SchemaName> .sqlOne or more files, each consisting of DDLs for selected objects in a particular schema.HR.sql will consist of DDLs for all the selected objects of the HR schema.
    <SchemaName> _ <ObjectType> .sqlOne or more files, each consisting of DDLs for a single selected object.HR_TABLE.sql will consist of DDLs only for TABLE object of the HR schema.
  4. Import the schema into your target database:

    1. Use the edb psql command line utility to connect to the EDB Postgres Advanced Server database from where the converted schema is to be migrated.

    2. Create a new database:

      CREATE DATABASE <database_name>;
    3. Connect to the new database using the following psql command:

      \connect <database_name>
    4. Import the required SQL files into the new database using the following psql command:

      \i <path_to_exported_schema_file>.sql
      Note

      You can alternatively use the pgAdmin client for the import.1. Import the schema into your target database:

The converted schemas migrate to the target server.

Migrating to a new on-premises EDB Postgres Advanced Server installation

You can install new EDB Postgres Advanced Server on premises on Windows or Linux platforms and migrate the schemas.

  1. Select New on-premises EDB Postgres Postgres Advanced Server.

  2. Select one or more schemas to migrate to EDB Postgres Advanced Server. You can also expand a schema and select specific object types under each schema.

    Schema and objects are selected

  3. Select your operating system.

  4. Select Download SQL file, to download the assessed schema. The zip package consists of SQL files with DDLs for your selected schemas and objects. The DDLs are grouped in SQL files by project, schemas, or objects.

    File nameDescriptionExample
    <ProjectName> _ALL_OBJECTS.sqlA single consolidated file consisting of DDLs for all your selected schemas and objects.AllTestMigration_ALL_OBJECTS.sql will consist of DDLs for all objects of schemas OE, SH, IX, and PM, and only selected objects of schema HR.
    <SchemaName> .sqlOne or more files, each consisting of DDLs for selected objects in a particular schema.HR.sql will consist of DDLs for all the selected objects of the HR schema.
    <SchemaName> _ <ObjectType> .sqlOne or more files, each consisting of DDLs for a single selected object.HR_TABLE.sql will consist of DDLs only for TABLE object of the HR schema.
  5. Import the schema into your target database:

    1. Use the edb psql command line utility to connect to the EDB Postgres Advanced Server database from where the converted schema is to be migrated.

    2. Create a new database:

      CREATE DATABASE <database_name>;
    3. Connect to the new database using the following psql command:

      \connect <database_name>
    4. Import the required SQL files into the new database using the following psql command:

      \i <path_to_exported_schema_file>.sql
      Note

      You can alternatively use the pgAdmin client for the import.

The converted schemas migrate to the target server.

Migrate to the cloud

Migrate schemas on EDB Postgres Advanced Server to the cloud.

  1. Select EDB Postgres Advanced Server on Cloud.

  2. Select one or more schemas to migrate to EDB Postgres Advanced Server. You can also expand a schema and select specific object types under each schema.

    Schema and objects are selected

  3. Select the cloud platform, for example, BigAnimal.

  4. To launch a new cluster, select Go to BigAnimal.

    Or, if you have an existing cluster running, select Next.

    Note

    See the BigAnimal page for more information.

  5. Enter the required connection details on the Connect page.

    Connecting to the cloud cluster

  6. To verify the connection details, select Test Connection.

    Note

    You can select Edit Connection to make changes to the connection details and retest the connection details.

  7. Once the connection is successful, select Next.

The converted schemas migrate to the target server.