One-click migration assessment report for ApsaraDB RDS for PostgreSQL-ApsaraDB RDS(RDS)-阿里云帮助中心

Check rds empty

Check item

Check rds databases

Common error

error:postgres not empty, check if any table exists

Description

The destination ApsaraDB RDS for PostgreSQL instance is not empty.

Solution

Clear all databases in the ApsaraDB RDS for PostgreSQL instance, leaving only the template0, template1, and postgres databases. In the postgres database, remove all tables except ha_health_check.

Check source connectivity

Check item 1

Check ip connectable
Common error
```
error:XX.XX.XX.XX is unapproachable
```
Description

The IP address or DNS name of the server that hosts the self-managed PostgreSQL database is unreachable.
Solution
- For the one-click migration to the cloud of a self-managed PostgreSQL database on an ECS instance, configure the Private IP Address of the ECS instance. For information about how to obtain the IP address, see View IP addresses.
- For migrations from an on-premises self-managed PostgreSQL database, provide the IP address resolved from its DNS record.
Check item 2

Check port connectable
Common error
```
error:5432 is unapproachable
```
Description
- The self-managed PostgreSQL database is not configured to listen for remote connections.
- A firewall is configured for the self-managed PostgreSQL database, but no rule allows access to the database port.
Solution
- In the postgresql.conf file of your self-managed PostgreSQL database, set listen_addresses = '*'. For more information, see Configure the postgresql.conf file.
- Configure the firewall to allow traffic on port 5432, or disable the firewall before migration. For more information, see Configure the server firewall.
Check item 3

Check database connectable
Common error
```
error:cannot connect to source database by migratetest:123456
```
Description
- The username or password is incorrect.
- The pg_hba.conf configuration for the self-managed PostgreSQL database is incorrect.
Solution
- Verify that you can connect to the self-managed PostgreSQL database with the specified username and password. If the connection fails, update the password. The following example shows the command to update the password for the migratetest account:
```
ALTER USER migratetest WITH PASSWORD '123456';
```
- Modify the pg_hba.conf file of your self-managed PostgreSQL database. For the migratetest account, for example, add the following configuration:
```
host  all       migratetest  <ApsaraDB RDS for PostgreSQL VPC CIDR block>  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 user account lacks the REPLICATION privilege.
- The pg_hba.conf configuration for the self-managed PostgreSQL database is incorrect.
Solution
- Grant the REPLICATION privilege to the user account. The following example shows the command for the migratetest account:
```
ALTER ROLE migratetest REPLICATION;
```
- Modify the pg_hba.conf file of your self-managed PostgreSQL database. For example, add the following configuration for the migratetest account:
```
host  replication  migratetest  <ApsaraDB RDS for PostgreSQL VPC CIDR block>  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 user account lacks the CREATEROLE privilege.

Solution
Grant the CREATEROLE privilege to the user account. The following example shows the command for 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 user account lacks the pg_monitor privilege.

Note
This privilege is required to query system views like pg_stat_replication and pg_stat_wal_receiver for replication link information.

Solution
Grant the pg_monitor privilege to the user account. The following example shows the command for the migratetest account:
```
GRANT pg_monitor TO migratetest;
```

Check source version

Check item

Check major version consistent

Common error

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

Description

The major version of the self-managed PostgreSQL database does not match that of the destination ApsaraDB RDS for PostgreSQL instance.

Solution

Purchase an ApsaraDB RDS for PostgreSQL instance with a major version that matches your self-managed PostgreSQL database.

Check source glibc version

Check item

Check source glibc version compatible

Common error

warning:source glibc version is not compatible with rds pg

Description

The GLIBC version of the self-managed PostgreSQL database is incompatible with that of the ApsaraDB RDS for PostgreSQL instance.

Note

In GLIBC 2.28, the collation rules for some UTF-8 characters were changed. Version incompatibility can lead to different data sorting rules and unexpected query results.

Solution

Perform the following checks.

Check the table collation.

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;

Results:

If true is returned, the migration is not at risk. No further action is needed.
If false is returned, proceed to the next step.

Check the database collation.
```
SELECT datname,datcollate FROM pg_database where datcollate NOT IN ('C', 'POSIX');
```
Results:
- If the result is empty, the migration is not at risk. No further action is needed.
- If the result is not empty, proceed to the next step.

In all databases, check for indexes whose collation is 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');

Results:

If the result is empty, the migration is not at risk. No further action is needed.
If the result is not empty, the migration is at risk.

Check disk size

Check item

Check disk size enough

Common error

error:source_db_size > disk_size * 0.95

Description

The total size of the self-managed PostgreSQL databases exceeds 95% of the storage space on the destination ApsaraDB RDS for PostgreSQL instance.

Solution

Run the following command to check the total storage space used by your self-managed PostgreSQL database:
```
SELECT SUM(pg_database_size(pg_database.datname))/1024/1024 AS size FROM pg_database;
```
Note
The unit of the result is megabytes (MB).
Calculate the required storage space for the destination ApsaraDB RDS for PostgreSQL instance.

For example, if your self-managed PostgreSQL database uses 100 GB of storage space, you must set the storage space of the ApsaraDB RDS for PostgreSQL instance to at least 110 GB.
Scale up the storage space of the ApsaraDB RDS for PostgreSQL instance. For more information, see Change instance specifications.

Check wal keep size

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 too small and must be increased.

Solution

If your self-managed PostgreSQL database is version 13 or later, increase the value of the wal_keep_size parameter on your self-managed PostgreSQL database. This improves the success rate of full and incremental backups during migration.
If your self-managed PostgreSQL database is earlier than version 13, increase the value of the wal_keep_segments parameter on your self-managed PostgreSQL database. This improves the success rate of full and incremental backups during migration.

Note
For PostgreSQL versions earlier than 13, wal_keep_size is calculated as wal_keep_segments * wal_segment_size.

Check spec params

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 max_connections and max_prepared_transactions parameters of the self-managed PostgreSQL database are more than 100 times larger than the corresponding parameters of the destination ApsaraDB RDS for PostgreSQL instance. This can cause the destination instance to fail to start during replication link setup.

Solution

Decrease the values of the max_connections and max_prepared_transactions parameters on your self-managed PostgreSQL database.

Note

Changes to the max_connections and max_prepared_transactions parameters require a restart of the self-managed PostgreSQL instance to take effect.

Check rds user

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 aurora, replicator, and pgxxx accounts on the self-managed PostgreSQL database are reserved by ApsaraDB RDS for PostgreSQL. Do not use these three accounts as service accounts.

Solution

Do not use the accounts listed in the warning message as service accounts.

Check extensions

Check item 1

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

The self-managed PostgreSQL database has an extension that is not compatible with the destination ApsaraDB RDS for PostgreSQL instance.

Solution

Remove the incompatible extension from your self-managed PostgreSQL database.
Check item 2

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

An extension version on the self-managed PostgreSQL database is higher than the version available on ApsaraDB RDS for PostgreSQL.

Solution

Install a lower version of the extension on your self-managed PostgreSQL database to match the version on 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

An extension version on the self-managed PostgreSQL database is lower than the version on ApsaraDB RDS for PostgreSQL.

Solution

No action is required. The extension is automatically upgraded after migration.

Check Postgres system catalogs

Check item 1

Check whether the source instance contains languages that are not supported on the destination instance (pg_language).

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 supports only the C, internal, SQL, PL/pgSQL, PL/Tcl, and PL/Perl languages.

Solution

Remove the unsupported languages from the source instance.
Check item 2

Check whether the source instance contains Large Objects.

Common error
```
error: large object exist in databases [xxx, xxx].
```
Description

ApsaraDB RDS for PostgreSQL does not support Large Objects.

Solution

Remove all Large Objects from the source instance.
Check item 3

Check whether default privileges have been set on the source instance by using the ALTER DEFAULT PRIVILEGES ... GRANT ... statement. This check verifies whether any database on the source instance has a non-empty pg_catalog.pg_default_acl system table.

Common error
```
error: default acl settings exist in databases [xxx, xxx].
```
Description

If the ALTER DEFAULT PRIVILEGES ... GRANT ... statement was executed on a database in the source instance, the pg_catalog.pg_default_acl system table for that database will not be empty.

Solution

You must first run the ALTER DEFAULT PRIVILEGES ... REVOKE ... statement in the corresponding database of the source instance to ensure that the pg_default_acl system table is empty for all databases. After the migration is complete, you can run the ALTER DEFAULT PRIVILEGES ... GRANT ... statement again to restore the original default privilege settings.
Check item 4

Check whether the pg_parameter_acl and pg_db_role_setting system tables on the source instance are not empty.

Common error
```
warning: Invalid system tables: [pg_parameter_acl, pg_db_role_setting], these system tables should be empty.
```
Description

The pg_parameter_acl system table records user access privileges for kernel parameters. ApsaraDB RDS for PostgreSQL does not allow users to modify these privileges. The pg_db_role_setting system table records the default privilege settings for different users in each database.

Solution
1. On the source instance, run the following statements to clear the pg_parameter_acl and pg_db_role_setting system tables:
```
DELETE FROM pg_parameter_acl;
DELETE FROM pg_db_role_setting;
```
2. After the migration is complete, you must reconfigure the default privileges for different users in each database on the destination instance.

Check reserved role privileges

Check item

Check whether the source instance has granted reserved role privileges to any accounts that are not allowed on the destination 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

ApsaraDB RDS for PostgreSQL does not allow accounts to have privileges for reserved roles such as pg_write_server_files. If a statement such as GRANT pg_write_server_files TO "user"; was used on the source instance, you must revoke these privileges.

Solution

On the source instance, run the appropriate REVOKE statement to revoke the privileges, such as REVOKE pg_write_server_files FROM "user";.