PostgreSQL

PostgreSQL can serve as a source, as a target, and as the migration tracking database in MigratorXpress. As a source, it supports Ctid-based parallel transfers for large tables. As a target, see PostgreSQL as Target for the target-specific behavior (lower-case names, no copy_fk).

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "postgres"
auth_mode	Yes	"classic" (the parameters below) or "connection_string" (see Authentication modes)
username	Yes	PostgreSQL username
password	Yes	PostgreSQL password
server	Yes	Hostname or IP address
port	Yes	Port number, as a JSON integer (usually 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

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

As a source, PostgreSQL provides its primary, unique and foreign keys (from pg_constraint) to the copy_pk, copy_ak and copy_fk tasks since 0.7.3, and the NOT NULL constraint of its columns is kept on the target.

Example: Connection String

{
  "postgres_cs": {
    "ds_type": "postgres",
    "auth_mode": "connection_string",
    "info": {
      "connection_string": "Host=pg-prod.company.com;Port=5432;Database=analytics;Username=migrator;Password=********;SSL Mode=Require"
    }
  }
}

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