ApsaraDB RDS:Analyze the feasibility assessment report of DR deployment

Check rds empty (Check whether the destination instance is empty)

Check content

This check item checks whether non-empty databases exist on the destination instance to prevent data from being overwritten.

Common error

error:xxx not empty, check if any table exists

Error description

Non-empty databases exist on the destination instance.

Solution

Clear the non-empty databases on the destination instance, but the template0, template1, and postgres databases must be retained.

Check source connectivity (Check the network connectivity and synchronization account permissions)

• Check content 1

  This check item checks whether the IP address of the source instance can be accessed.

  Common error

  error:xxx.xxx.xxx.xxx is unapproachable

  Error description

  The IP address or domain name of the source instance cannot be accessed from the network environment of the destination instance.

  Solution

  Configure the network environment and check whether all network settings are configured.

• Check content 2

  This check item checks whether the port of the source instance can be connected.

  Common error

  error:5432 is unapproachable

  Error description

  The port of the source instance cannot be connected from the network environment of the destination instance.

  Solution

  • Add the listen_address = '*' setting in the postgresql.conf configuration file of the source instance. Then, restart the source instance.

  • Configure the firewall of the source instance and open specific ports.

• Check content 3

  This check item checks whether the PostgreSQL database service on the source instance can be connected.

  Common error

  error:cannot connect to source database by replicatoraccount:replicatorPassword

  Error description

  • The password of the synchronization account is invalid. The synchronization account is used for data synchronization between the source and destination instances.

  • The pg_hba.conf configuration file of the source instance is incorrectly configured.

  Solution

  • Check whether you can use the synchronization account to connect to the source instance. If you cannot use the synchronization account to connect to the source instance, you can change the password of the account. In this example, the synchronization account named replicatoraccount is used. Sample statement:

    ALTER ROLE replicatoraccount WITH PASSWORD 'replicatorPassword';

  • Add the setting to allow the synchronization account in the pg_hba.conf configuration file of the source instance. In this example, the synchronization account named replicatoraccount is used. Sample statement:

    host all replicatoraccount <VPC CIDR block of the destination instance> md5

• Check content 4

  This check item checks whether the synchronization account has the REPLICATION permission to perform operations such as data synchronization.

  Common error

  error:replicatoraccount has no replication privilege

  Error description

  • The synchronization account does not have the REPLICATION permission.

  • The pg_hba.conf configuration file of the source instance is incorrectly configured.

  Solution

  • Grant the REPLICATION permission to the synchronization account. In this example, the synchronization account named replicatoraccount is used. Sample statement:

    ALTER ROLE replicatoraccount REPLICATION;

  • Add the setting to allow the synchronization account to initiate replication connections in the pg_hba.conf configuration file of the source instance. In this example, the synchronization account named replicatoraccount is used. Sample statement:

    host replication replicatoraccount <VPC CIDR block of the destination instance> md5

• Check content 5

  This check item checks whether the synchronization account has the CREATEROLE permission.

  Common error

  error:replicatoraccount has no createrole privilege

  Error description

  The synchronization account does not have the CREATEROLE permission.

  Solution

  Grant the CREATEROLE permission to the synchronization account. In this example, the synchronization account named replicatoraccount is used. Sample statement:

  ALTER ROLE replicatoraccount CREATEROLE;

• Check content 6

  This check item checks whether the synchronization account has the pg_monitor permission to query system views, such as pg_stat_replication and pg_stat_wal_receive, to obtain the data synchronization status.

  Common error

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

  Error description

  The synchronization account does not have the pg_monitor permission.

  Solution

  Grant the pg_monitor permission to the synchronization account. In this example, the synchronization account named replicatoraccount is used. Sample statement:

  GRANT pg_monitor TO replicatoraccount

Check major version (Check whether the major engine versions are the same)

Check content

This check item checks whether the major engine versions of the source and destination instances are the same.

Common error

version mismatch, source version:15, current version:16

Error description

The major engine versions of the source and destination instances are different.

Solution

Make sure that the destination instance runs the same major engine version as the source instance.

Check glibc version (Check the glibc version compatibility)

Check content

This check item checks whether the glibc version of the source instance is compatible with the glibc version of the destination instance.

Common error

warning:source glibc version is not compatible with rds pg

Error description

The glibc version of the source instance is incompatible with the glibc version of the destination instance. In glibc version 2.28, the collation of specific UTF-8 characters is changed. If the glibc version of the source instance is incompatible with the glibc version of the destination instance, the collations of the source and destination instances may be different and the collation results may not meet the expected results.

Solution

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;

   One of the following results may be returned:

   • If true is returned, no further actions are required and no risks exist.

   • 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');

   One of the following results may be returned:

   • If the returned result is empty, no further actions are required and no risks exist.

   • If the returned result is not empty, proceed with the subsequent steps.

3. Check all databases to identify the 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');

   One of the following results may be returned:

   • If the returned result is empty, no further actions are required and no risks exist.

   • If a result is returned, risks may occur.

Check disk size (Check whether the storage capacity is sufficient)

Check content

This check item checks whether the storage capacity of the destination instance meets the requirements.

Common error

error:source_db_size > disk_size * 0.8

Error description

The data volume of the source instance exceeds 80% of the storage capacity of the destination instance. The storage capacity of the destination instance is insufficient.

Solution

1. Execute the following statement to estimate the data volume in MB of the source instance: .

   SELECT sum(pg_database_size(datname))/1024/1024 AS size FROM pg_database;

2. Calculate the required storage capacity of the destination instance. If the storage capacity does not meet the requirement, expand the storage capacity. For more information, see Change instance specifications.

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

Check content

This check item checks whether the value of the wal_keep_size parameter is large enough to prevent the WAL logs that are required by the destination instance from being deleted.

Common error

wal_keep_size xxMB is too small. Try to set wal_keep_segments or wal_keep_size large enough ensure pg_basebackup success

Error description

The value of the wal_keep_size or wal_keep_segments parameter is excessively small.

Solution

• If the source instance runs PostgreSQL 13 or later, increase the value of the wal_keep_size parameter.

• If the source instance runs a major engine version earlier than PostgreSQL 13, increase the value of the wal_keep_segments parameter.

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

Check content

This check item checks whether the values of the specification-related parameters of the destination instance are greater than the values of the specification-related parameters of the source instance. The parameters include max_connections and max_prepared_transactions.

Common error

error:max_connections too large, value=xxx; max_prepared_transactions too large, value=xxx

Error description

To establish a primary/secondary replication relationship between the source instance and destination instances, the values of the specification-related parameters of the source instance cannot exceed the values of the specification-related parameters of the destination instance. The parameters include max_connections and max_prepared_transactions.

Solution

Decrease the values of the max_connections and max_prepared_transactions parameters of the source instance and then restart the source instance.

Check rds user (Check whether the source instance has the system accounts required by the destination instance)

Check content

This check item checks whether the source instance has the alicloud_rds_admin, aurora, and replicator accounts. These accounts are used as the system accounts of the destination instance.

Common error

error:xxx will be reused in rds

Error description

The alicloud_rds_admin, aurora, and replicator accounts of the source instance are used as the system accounts of the destination instance.

Solution

Delete the account that is described in the error message from the source instance.

Check extensions (Check the compatibility of extensions)

• Check content 1

  This check item checks whether the source instance has the extensions that are not supported by the destination instance.

  Common error

  error:Check source supported extensions xxx not supported

  Error description

  The source instance has the extensions that are not supported by the destination instance.

  Solution

  Delete the unsupported extensions from the source instance.

• Check content 2

  This check item checks whether the source instance has the extensions whose versions are later than the extension versions supported by the destination instance.

  Common error

  error:Check source extensions with higher version xxx

  Error description

  The source instance has the extensions whose versions are later than the extension versions supported by the destination instance.

  Solution

  Install the extension of an earlier version that is supported by the destination instance in the source instance. You can also delete unsupported extensions.

• Check content 3

  This check item checks whether the source instance has the extensions whose versions are earlier than the extension versions supported by the destination instance.

  Common error

  warning:Check source extensions with lower version xxx

  Error description

  The source instance has the extensions whose versions are earlier than the extension versions supported by the destination instance.

  Solution

  You do not need to resolve the error. The extension versions are automatically updated during the DR deployment.

Check logical replication slot (Check logical replication slots)

Check content

This check item checks whether logical replication slots exist on the source instance.

Common error

warning:The logical replication slots: xxx will be lost on target instance

Error description

The logical replication slots that exist on the source instance are not synchronized to the destination instance. After the destination instance is promoted to the primary instance, the downstream subscribers of logical replication cannot find the logical replication slots for consumption.

Solution

Take note that logical replication slots are not synchronized to the destination instance. In this case, risks may occur.

Check local cross db access (Check the cross-database access operations on the source instance)

Check content

This check item checks whether cross-database access operations are performed on the source instance by using extensions such as postgres_fdw or dblink.

Common error

warning:Foreign servers for local cross-database access exist

Error description

The local listening port of the PostgreSQL service on the destination instance is 5432, which is different from the local listening port of the source instance. After the destination instance is promoted to the primary instance, the cross-database access operations that can be performed on the source instance become invalid on the destination instance due to the port change.

Solution

You do not need to resolve the error. Take note that the local listening ports of the source and destination instances may be different. If cross-database access operations are performed on the source instance, you must reconfigure the port that is used for cross-database access after the destination instance is promoted to the primary instance.

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

• Check content 1

  This check item checks 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].

  Error 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

  This check item checks whether the source instance contains large objects.

  Common error

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

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

  Note

  Some RDS for PostgreSQL instances that are migrated from self-managed PostgreSQL databases may store large objects. This error may be reported when you perform feasibility assessment on these instances.

  Solution

  Delete the large objects in the source instance.

• Check content 3

  This check item checks 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 DR employment is complete, execute the ALTER DEFAULT PRIVILEGES ... GRANT ... statement on the databases to restore the default permissions.

• Check content 4

  This check item checks 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

This check item checks 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";