ApsaraDB RDS:Create a migration account on a self-managed PostgreSQL instance

Prerequisites

Ensure that you have:

• A self-managed PostgreSQL instance deployed on an Elastic Compute Service (ECS) instance or in a data center

• Administrative access to the self-managed PostgreSQL instance (a superuser or a role with CREATEROLE privilege)

• An ApsaraDB RDS for PostgreSQL instance as the migration target

Procedure

The migration account requires the CREATEROLE and REPLICATION privileges, and membership in the pg_monitor role.

If an account with these permissions already exists on the self-managed PostgreSQL instance, skip this procedure and proceed to Update the pg_hba.conf file of a self-managed PostgreSQL instance.

1. Connect to the self-managed PostgreSQL instance and run the following SQL statement to create the migration account:

   CREATE USER migratetest CREATEROLE REPLICATION LOGIN PASSWORD '123456';

   Note: Replace 123456 with a strong, unique password. Do not use the example password in production environments.

   Parameter	Description
   migratetest	Account name. Replace with your preferred name.
   CREATEROLE	Grants the ability to create and manage roles, required for the migration process.
   REPLICATION	Enables WAL (Write-Ahead Logging) streaming for data replication.
   LOGIN	Allows the account to establish connections.
   PASSWORD	Sets the login password for the account.

2. Grant the pg_monitor role to the migration account:

   GRANT pg_monitor TO migratetest;

   The pg_monitor role grants read access to monitoring views and functions, including replication progress tracking.

3. Verify that the account was created with the correct permissions:

   SELECT rolname, rolcreaterole, rolreplication FROM pg_roles WHERE rolname = 'migratetest';

   Expected output:

     rolname    | rolcreaterole | rolreplication
   -------------+---------------+----------------
    migratetest | t             | t

   To verify pg_monitor membership:

   SELECT pg_has_role('migratetest', 'pg_monitor', 'member');

   Expected output:

    pg_has_role
   -------------
    t

Next steps

Update the pg_hba.conf file of a self-managed PostgreSQL instance