Introduction to cloud migration assessment reports - ApsaraDB RDS

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.

If the status of a cloud migration assessment report is failure, you can click View report in the operation column to view the details about the report. The report contains the following details:

Check rds empty (Check whether the databases in the ApsaraDB RDS for PostgreSQL instance are empty)
Check source connectivity (Check whether communication between the self-managed PostgreSQL instance and the ApsaraDB RDS for PostgreSQL instance is normal)
Check source version (Check the major engine version of the self-managed PostgreSQL instance)
Check source glibc version (Check the GNU C Library version of the self-managed PostgreSQL instance)
Check disk size (Check whether the available storage of the ApsaraDB RDS for PostgreSQL instance is sufficient)
Check wal keep size (Check the value of the wal_keep_size parameter)
Check spec params (Check the settings of specifications-related parameters)
Check rds user (Check whether the system accounts of the ApsaraDB RDS for PostgreSQL instance are used in the self-managed PostgreSQL instance)
Check extensions (Check the compatibility of plug-ins)
Check Postgres system catalogs (Check the system catalogs of the ApsaraDB for PostgreSQL instance)
Check reserved role permission (Check the permissions of reserved roles)

Note

For more information about a cloud migration to an ApsaraDB RDS for PostgreSQL instance, see Use the cloud migration feature for an ApsaraDB RDS for PostgreSQL instance.

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:
- Add the following content to the postgresql.conf file of the self-managed PostgreSQL instance: listen_addresses = '*'. For more information, see Configure the postgresql.conf file of a self-managed PostgreSQL instance.
- Configure the firewall to allow access to port 5432. Alternatively, disable the firewall before you start the cloud migration. For more information, see Configure the firewall of a server.
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
```
  Note
  For more information, see Update the pg_hba.conf file.
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
```
  Note
  For more information, see Update the pg_hba.conf file.
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:

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.
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.

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:

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.
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.
Change the specifications of the ApsaraDB RDS for PostgreSQL instance to expand the storage capacity. For more information, see Change instance specifications.

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.

Check Postgres system catalogs (Check the system catalogs of the ApsaraDB for PostgreSQL instance)

Check content 1:
Check whether the source instance uses languages (pg_language) that are not supported by the destination instance.
Common error:
```
error: disallowed language exist in databases [xxx, xxx], the languages allowed are [c, internal, sql, plpgsql, pltcl, plperl].
```
Description:
ApsaraDB RDS for PostgreSQL instances support only the following languages: C, internal, SQL, PL/pgSQL, PL/Tcl, and PL/Perl. Unsupported languages used in the source instance cannot be used in the destination instance.
Solution:
Delete the languages that are not supported by the destination instance from the source instance.
Check content 2:
Check whether the source instance contains large objects.
Common error:
```
error: large object exist in databases [xxx, xxx].
```
Description:
ApsaraDB RDS for PostgreSQL instances do not support large objects. Therefore, large objects in the source instance cannot be used in the destination instance.
Solution:
Delete the large objects in the source instance.
Check content 3:
Check whether the default permissions are granted to the source instance by executing the ALTER DEFAULT PRIVILEGES ... GRANT ... statement. This check is implemented by confirming whether the source instance contains databases whose system table pg_catalog.pg_default_acl is not empty.
Common error:
```
error: default acl settings exist in databases [xxx, xxx].
```
Description:
If the ALTER DEFAULT PRIVILEGES ... GRANT ... statement has been executed on a database of the source instance, the system table pg_catalog.pg_default_acl of the database is not empty.
Solution:
Execute the ALTER DEFAULT PRIVILEGES ... REVOKE ... statement on all databases of the source instance to ensure that the system table pg_catalog.pg_default_acl of all databases is empty. After the migration is complete, execute the ALTER DEFAULT PRIVILEGES ... GRANT ... statement on the databases to restore the default permissions.
Check content 4:
Check whether the system tables (pg_parameter_acl and pg_db_role_setting) are not empty.
Common error:
```
warning: Invalid system tables: [pg_parameter_acl, pg_db_role_setting], these system tables should be empty.
```
Description:
The system table pg_parameter_acl records the access permissions of users on kernel parameters. ApsaraDB RDS for PostgreSQL does not support the modification of these parameters. The system table pg_db_role_setting records the default permissions of users on different databases.
Solution:
1. Execute the following statements in the source instance to clear data in the pg_parameter_acl and pg_db_role_setting tables:
```
DELETE FROM pg_parameter_acl;
DELETE FROM pg_db_role_setting;
```
2. After the DR deployment is complete, configure the default permissions of the users on different databases in the destination instance.

Check reserved role permission (Check the permissions of reserved roles)

Check content:

Check whether the permissions of reserved roles that are not allowed by the destination instance are granted to accounts in the source instance.

Common error:

error: some disallowed reserved roles [pg_write_server_files] are granted to user. you should revoke these roles from their members

Description:

In ApsaraDB RDS for PostgreSQL, accounts are not allowed to be granted permissions of reserverd roles such as pg_write_server_files. If statements such as GRANT pg_write_server_files TO "user"; are executed to grant the permissions in the source instance, you must execute the REVOKE statement to revoke the permissions.

Solution:

Execute the REVOKE statement to revoke the permissions granted to the accounts in the source instance. Example: REVOKE pg_write_server_files FROM "user";