PostgreSQL

PostgreSQL can serve as both a source and a target database in MigratorXpress migrations. As a source, it supports Ctid-based parallel transfers for large tables.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "postgres"
auth_mode	Yes	"classic" or "odbc"
username	Yes	PostgreSQL username
password	Yes	PostgreSQL password
server	Yes	Hostname or IP address
port	Yes	Port number (default: 5432)
database	Yes	Database name

Required Permissions

As Source

-- Grant read access to the migration user
GRANT USAGE ON SCHEMA public TO migratorxpress_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migratorxpress_user;

-- For multiple schemas, repeat for each schema:
GRANT USAGE ON SCHEMA my_schema TO migratorxpress_user;
GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO migratorxpress_user;

As Target

-- 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: Classic Authentication

{
  "postgres_source": {
    "ds_type": "postgres",
    "auth_mode": "classic",
    "info": {
      "username": "$env{PG_USER}",
      "password": "$env{PG_PASSWORD}",
      "server": "pg-prod.company.com",
      "port": 5432,
      "database": "analytics"
    }
  }
}

Example: ODBC Mode

When ODBC drivers are available, you can use ODBC mode for additional connection options:

{
  "postgres_odbc": {
    "ds_type": "postgres",
    "auth_mode": "odbc",
    "info": {
      "connection_string": "Driver={PostgreSQL};Server=pg-prod.company.com;Port=5432;Database=analytics;Uid=$env{PG_USER};Pwd=$env{PG_PASSWORD};"
    }
  }
}

Troubleshooting

Connection refused: Check that PostgreSQL is running (systemctl status postgresql), verify pg_hba.conf allows connections from your IP, and check firewall rules (sudo ufw allow 5432/tcp).

Authentication failed: Verify credentials, check the password encryption method in pg_hba.conf (md5, scram-sha-256), and confirm the user exists:

SELECT * FROM pg_user WHERE usename = 'your_user';

See Also

• Database Connections Overview -- Auth file format and security best practices