Skip to main content

PostgreSQL as Migration Tracking

Since v0.6.32, the migration tracking database can be PostgreSQL in addition to SQL Server. The two backends share the same schema (table names, columns, foreign keys, the v_sourcetargetdiff view) and the same workflow (run history, per-table task status, row counts, constraint copy results, event logs, --resume). MigratorXpress auto-detects the backend from the ds_type of the auth entry referenced by --migration_db_auth_id -- no extra CLI flag is required.

For the full PostgreSQL connection reference (authentication modes, ODBC, troubleshooting), see the canonical PostgreSQL page.

Schema and Object Naming​

  • Tracking tables and the v_sourcetargetdiff view live in the public schema.
  • Types map to PG natives: timestamp, text, boolean, varchar(N), numeric(32,6).
  • The internal sequence seq_id_load is a standard CREATE SEQUENCE.
  • Idempotent DDL uses CREATE TABLE IF NOT EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS, CREATE OR REPLACE VIEW, and INSERT ... ON CONFLICT DO UPDATE.

Tracking-specific Permissions​

The tracking role needs full DDL/DML rights on the database hosting the tracking tables, because MigratorXpress creates tables, evolves the schema on upgrade, and writes rows continuously during a migration.

-- Full access to the migration tracking database
GRANT CREATE ON DATABASE migration_log TO migratorxpress_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO migratorxpress_user;
ALTER SCHEMA public OWNER TO migratorxpress_user;

If the role is not the schema owner, future schema upgrades (added columns, new tables) can fail with permission denied for schema public. Owning the schema (or granting ALL on it) avoids that.

Migration DB Modes​

The --migration_db_mode flag controls how MigratorXpress treats the tracking schema at startup. Behavior is identical on SQL Server and PostgreSQL.

ModeBehavior
preserve (default)Keep tracking data; required to use --resume
truncateClear data, keep schema
dropDrop and recreate the tracking schema

Example Auth Entry​

{
  "migration_tracking": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{MIG_USER}",
      "password": "$env{MIG_PASSWORD}",
      "server": "pg-host.company.com",
      "port": 5432,
      "database": "migration_log"
    }
  }
}

Reference it on the command line with --migration_db_auth_id migration_tracking. The rest of the CLI is unchanged.

Querying the Tracking Tables​

The schema is the same across both backends, so any dashboard query that uses portable SQL (no DATEDIFF, GETDATE(), TOP, + for string concat) works on either. Example: list tables with errors for a given run.

SELECT src_tablename, eventmessage, eventtimestamp
FROM eventlog
WHERE eventrunid = '20260511-...'
  AND eventstatus = 'failed'
ORDER BY eventtimestamp DESC;

For PG-specific date arithmetic and other reporting patterns, see Logging and Monitoring.

See Also​

Copyright © 2026 Architecture & Performance.