PostgreSQL as Target

PostgreSQL can be the migration target for SQL Server, Oracle and PostgreSQL sources (since 0.7.2). A Netezza source cannot be migrated to PostgreSQL: the run stops at startup with exit code 10 (UNSUPPORTED_SOURCE_TARGET_PAIR).

When used as a target, MigratorXpress translates the source column types to PostgreSQL types, creates the target schema automatically (CREATE SCHEMA IF NOT EXISTS) and loads data with FastTransfer's PostgreSQL COPY loader (pgcopy).

Behavior with a PostgreSQL Target

• Lower-case names. Target schema, table and column names are created in lower case: a source table Sales.Orders lands in sales.orders. The names are recorded in lower case in the tracking database and passed in lower case to FastTransfer.
• Constraints. copy_pk and copy_ak are supported. copy_fk (and --fk_mode) is SQL Server-only: a task list containing copy_fk -- including --task_list all -- is rejected at startup with exit code 10 (COPY_FK_UNSUPPORTED_TARGET). Use an explicit list such as --task_list translate create transfer diff copy_pk copy_ak.
• No columnstore options. --cci_threshold and --aci_threshold are ignored (logged as such).
• Unsupported column types. SQL Server GEOGRAPHY / GEOMETRY columns (PostGIS is not handled) and PostgreSQL ARRAY / user-defined columns make the translate task fail for their table, which is then not created. Exclude these tables (--exclude_tables) and migrate them separately.
• Exact data. A PostgreSQL source is read with FastTransfer's pgcopy source mode, and the date/time and binary columns of a SQL Server source are read as text that PostgreSQL parses back exactly (since 0.7.3). The exactness of an Oracle to PostgreSQL transfer was not verified: run diff and spot-check date, timestamp and binary columns.
• Diff. Source and target columns are matched case-insensitively, so diff works with the lower-cased target columns (since 0.7.3).

Resuming older runs

Runs started with a version older than 0.7.2 against a PostgreSQL target used the source case for schema and table names. Do not --resume them; start a new run.

For the complete connection reference and troubleshooting, see the canonical PostgreSQL page.

Target-specific Permissions

-- Grant write access to the migration user
GRANT CREATE ON DATABASE target_db TO migratorxpress_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO migratorxpress_user;

-- If using a custom target schema:
CREATE SCHEMA IF NOT EXISTS target_schema;
GRANT ALL PRIVILEGES ON SCHEMA target_schema TO migratorxpress_user;

Example Auth Entry

{
  "postgres_target": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "migrator",
      "password": "********",
      "server": "pg-target.company.com",
      "port": 5432,
      "database": "target_db"
    }
  }
}

Reference it on the command line with --target_db_auth_id postgres_target.

See Also

• PostgreSQL -- full connection reference
• Database Connections -- auth file format and security