This topic describes the assessment report of a cloud migration from a self-managed PostgreSQL instance to an ApsaraDB RDS for PostgreSQL instance. This topic also describes the common errors in the report and the solutions to the errors.

Check rds empty (Check whether the databases in the ApsaraDB RDS for PostgreSQL instance are empty)

Check item:

Check rds databases

Common error:
error:postgres not empty, check if any table exists

Description:

Databases are created in the ApsaraDB RDS for PostgreSQL instance, and the databases contain data.

Solution:

Delete all databases except the template0, template1, and postgres databases from the ApsaraDB RDS for PostgreSQL instance. In addition, delete all tables except the ha_health_check table from the postgres database.

Check source connectivity (Check whether communication between the self-managed PostgreSQL instance and the ApsaraDB RDS for PostgreSQL instance is normal)

  • Check item 1:

    Check ip connectable

    Common error:
    error:XX.XX.XX.XX is unapproachable

    Description:

    The IP address of the server on which the self-managed PostgreSQL instance resides or the IP addresses of the DNS servers are inaccessible.

    Solution:
    • If the self-managed PostgreSQL instance resides on an Elastic Compute Service (ECS) instance, enter the private IP address of the ECS instance when you configure the source database information. For more information about how to obtain the private IP address of an ECS instance , see View IP addresses.
    • If the self-managed PostgreSQL instance resides in a data center, enter the IP addresses of the DNS servers of the host on which the self-managed PostgreSQL instance resides when you configure the source database information.
  • Check item 2:

    Check port connectable

    Common error:
    error:5432 is unapproachable

    Description:

    • The self-managed PostgreSQL instance is not configured to listen to remote connections.
    • The firewall that is configured for the self-managed PostgreSQL instance does not allow access to the port of the self-managed PostgreSQL instance.
    Solution:
  • Check item 3:

    Check database connectable

    Common error:
    error:cannot connect to source database by migratetest:123456

    Description:

    • The password that you entered is incorrect.
    • The configuration in the pg_hba.conf file of the self-managed PostgreSQL instance is incorrect.
    Solution:
    • Check whether you can connect to the self-managed PostgreSQL instance by using the username and password that you entered. If the connection fails, you can update the password. For example, if you use the migratetest account, run the following command to update the password of the migratetest account:
      ALTER USER migratetest WITH PASSWORD '123456';
    • Modify the pg_hba.conf file of the self-managed PostgreSQL instance. For example, if you use the migratetest account, add the following content to the file:
      host  all       migratetest  <The CIDR block of the VPC to which the ApsaraDB RDS for PostgreSQL instance belongs>  md5
  • Check item 4:

    Check account replication privilege

    Common error:
    error:migratetest has no replication privilege

    Description:

    • The account that you use does not have the REPLICATION permission.
    • The configuration in the pg_hba.conf file of the self-managed PostgreSQL instance is incorrect.

    Solution:

    • Grant the REPLICATION permission to the account that you use. For example, if you use the migratetest account, run the following command to grant the REPLICATION permission to the migratetest account:
      ALTER ROLE migratetest REPLICATION;
    • Modify the pg_hba.conf file of the self-managed PostgreSQL instance. For example, if you use the migratetest account, add the following content to the file:
      host  replication  migratetest  <The CIDR block of the VPC to which the ApsaraDB RDS for PostgreSQL instance belongs>  md5
  • Check item 5:

    Check account createrole privilege

    Common error:
    error:migratetest has no createrole privilege

    Description:

    The account that you use does not have the CREATEROLE permission.

    Solution:

    Grant the CREATEROLE permission to the account that you use. For example, if you use the migratetest account, run the following command to grant the CREATEROLE permission to the migratetest account:
    ALTER ROLE migratetest CREATEROLE;
  • Check item 6:

    Check account monitor privilege

    Common error:
    error:migratetest should be a member of pg_monitor to monitor replication status

    Description:

    The account that you use does not have the pg_monitor permission. pg_stat_wal_receiver
    Note The pg_monitor permission is used to query system views such as pg_stat_replication and pg_stat_wal_receiver and obtain the information about the replication link.

    Solution:

    Grant the pg_monitor permission to the account that you use. For example, if you use the migratetest account, run the following command to grant the pg_monitor permission to the migratetest account:
    GRANT pg_monitor TO migratetest;

Check source version (Check the major engine version of the self-managed PostgreSQL instance)

Check item:

Check major version consistent

Common error:
error:version mismatch, source version:10, current version:13.0

Description:

The self-managed PostgreSQL instance and the ApsaraDB RDS for PostgreSQL instance run different major engine versions.

Solution:

Purchase an ApsaraDB RDS for PostgreSQL instance that runs the same major engine version as the self-managed PostgreSQL instance.

Check source glibc version (Check the GNU C Library version of the self-managed PostgreSQL instance)

Check item:

Check source glibc version compatible

Common error:
warning:source glibc version is not compatible with rds pg

Description:

The GNU C Library version of the self-managed PostgreSQL instance is incompatible with the GNU C Library version of the ApsaraDB RDS for PostgreSQL instance.
Note Version 2.28 of the GNU C Library uses a few different collations to sort character sets in UTF-8 encoding. If the GNU C Library version of the self-managed PostgreSQL instance is incompatible with the GNU C Library version of the ApsaraDB RDS for PostgreSQL instance, the character sets in the ApsaraDB RDS for PostgreSQL instance may be in an unexpected order after the cloud migration.

Solution:

Perform the following steps:
  1. Check the collations of tables.
    begin;
    create temp table testcollation(id varchar(20) collate "en_US.utf8") on commit drop;
    insert into testcollation values('-1'),('1');
    select id='1' from testcollation order by id limit 1;
    rollback;
    The following results can be returned:
    • If true is returned, no further actions are required. The cloud migration does not have risks.
    • If false is returned, proceed with the subsequent steps.
  2. Check the collations of databases.
    SELECT datname,datcollate FROM pg_database where datcollate NOT IN ('C', 'POSIX');
    The following results can be returned:
    • If no result is returned, no further actions are required. The cloud migration does not have risks.
    • If a result is returned, proceed with the subsequent steps.
  3. Check all databases to find indexes whose collations are not C or POSIX.
    WITH result AS (
        WITH defcoll AS (
            SELECT datcollate AS coll
            FROM pg_database
            WHERE datname = current_database()
        )
        SELECT indrelid::regclass::text relname, indexrelid::regclass::text indexname,
            CASE WHEN c.collname = 'default'
                THEN defcoll.coll
                ELSE c.collname
            END AS collation
        FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
            JOIN pg_collation c ON coll=c.oid
            CROSS JOIN defcoll
        WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX')
    )
    SELECT result.relname, result.indexname, result.collation FROM result WHERE result.collation NOT IN ('C', 'POSIX');
    The following results can be returned:
    • If no result is returned, no further actions are required. The cloud migration does not have risks.
    • If a result is returned, the cloud migration has risks.

Check disk size (Check whether the available storage of the ApsaraDB RDS for PostgreSQL instance is sufficient)

Check item:

Check disk size enough

Common error:
error:source_db_size > disk_size * 0.95

Description:

The used storage of the self-managed PostgreSQL instance is greater than 95% of the available storage of the ApsaraDB RDS for PostgreSQL instance. This means that the available storage of the ApsaraDB RDS for PostgreSQL instance is insufficient.

Solution:

  1. Run the following command to view the used storage of the self-managed PostgreSQL instance:
    SELECT SUM(pg_database_size(pg_database.datname))/1024/1024 AS size FROM pg_database;
    Note The used storage that is returned is measured in the unit of MB.
  2. Calculate the available storage that the ApsaraDB RDS for PostgreSQL instance must provide to ensure a successful cloud migration.

    For example, if the used storage of the self-managed PostgreSQL instance is 100 GB, the available storage of the ApsaraDB RDS for PostgreSQL instance must be at least 110 GB.

  3. Change the specifications of the ApsaraDB RDS for PostgreSQL instance to expand the storage capacity. For more information, see Change the specifications of an ApsaraDB RDS for PostgreSQL instance.

Check wal keep size (Check the value of the wal_keep_size parameter)

Check item:

Check wal keep size large enough

Common error:
warning:wal_keep_size X MB is too small. Try to set wal_keep_segments or wal_keep_size large enough ensure pg_basebackup success

Description:

The value of the wal_keep_size or wal_keep_segments parameter is small.

Solution:

  • If the self-managed PostgreSQL instance runs PostgreSQL 13 or a later version, increase the value of the wal_keep_size parameter for the ApsaraDB RDS for PostgreSQL instance. This way, you can increase the success rate of the full backup and incremental backup during the cloud migration.
  • If the self-managed PostgreSQL instance runs a version earlier than PostgreSQL 13, increase the value of the wal_keep_segments parameter for the ApsaraDB RDS for PostgreSQL instance. This way, you can increase the success rate of the full backup and incremental backup during the cloud migration.
    Note If you use a PostgreSQL version that is earlier than PostgreSQL 13, the value of the wal_keep_size parameter is equal to the value of the wal_keep_segments parameter multiplied by the value of the wal_segment_size parameter.

Check spec params (Check the settings of specifications-related parameters)

Check item:

Check if spec params too large

Common error:
error:max_connections too large, value=XXX
error:max_prepared_transactions too large, value=XXX

Description:

The values of the max_connections and max_prepared_transactions parameters for the self-managed PostgreSQL instance are greater than 100 times the values of these parameters for the ApsaraDB RDS for PostgreSQL instance. In this case, the ApsaraDB RDS for PostgreSQL instance may fail to start during the establishment of a replication link.

Solution:

Decrease the values of the max_connections and max_prepared_transaction parameters for the self-managed PostgreSQL instance.
Note After you reconfigure the max_connections and max_prepared_transaction parameters for the self-managed PostgreSQL instance, you must restart the self-managed PostgreSQL instance.

Check rds user (Check whether the system accounts of the ApsaraDB RDS for PostgreSQL instance are used in the self-managed PostgreSQL instance)

Check item:

Check if rds system user is occupied

Common error:

warning:Check if rds system user is occupied ...XXX will be reused in rds

Description:

The system accounts aurora, replicator, and pgxxx of the ApsaraDB RDS for PostgreSQL instance are used in the self-managed PostgreSQL instance.

Solution:

Make sure that you do not use the preceding accounts in the self-managed PostgreSQL instance.

Check extensions (Check the compatibility of plug-ins)

  • Check item 1:

    Check source supported extensions

    Common error:
    error:Check source supported extensions XXX not supported

    Description:

    The plug-ins of the ApsaraDB RDS for PostgreSQL instance are incompatible with the plug-ins of the self-managed PostgreSQL instance.

    Solution:

    Delete the incompatible plug-ins from the self-managed PostgreSQL instance.

  • Check item 2:

    Check source extensions with higher version

    Common error:
    error:Check source extensions with higher version XXX

    Description:

    The versions of specific plug-ins in the self-managed PostgreSQL instance are later than the versions of these plug-ins in the ApsaraDB RDS for PostgreSQL instance.

    Solution:

    Reinstall these plug-ins in the self-managed PostgreSQL instance and make sure that the versions of these plug-ins in the self-managed PostgreSQL instance are the same as the versions of these plug-ins in the ApsaraDB RDS for PostgreSQL instance.

  • Check item 3:

    Check source extensions with lower version

    Common error:
    warning:Check source extensions with lower version XXX

    Description:

    The versions of specific plug-ins in the self-managed PostgreSQL instance are earlier than the versions of these plug-ins in the ApsaraDB RDS for PostgreSQL instance.

    Solution:

    No actions are required. The versions of the plug-ins are automatically updated after the cloud migration.