Fix PostgreSQL DR Feasibility Assessment Errors - ApsaraDB RDS

When a feasibility assessment task for disaster recovery (DR) deployment fails, click View Report in the Actions column to open the report. This page explains each check item, describes common errors, and provides steps to fix them.

In this document, source instance refers to the instance that requires disaster recovery, and destination instance refers to the DR instance — an ApsaraDB RDS for PostgreSQL instance used to implement DR.

Result states

Each check item produces one of two outcomes:

State	Meaning	Action required
Error	A blocking issue that prevents DR deployment from proceeding.	Fix the issue before retrying the feasibility assessment.
Warning	A non-blocking issue. DR deployment can proceed, but risks may occur.	Review the warning and decide whether to accept the risk.

Check items

The following table lists all check items in a feasibility assessment report. Warning-only items are marked with [Warning].

Check item	Description
Check rds empty	Destination instance must be empty to prevent data from being overwritten.
Check source connectivity	Verifies network connectivity and synchronization account privileges (6 sub-checks).
Check major version	Source and destination must run the same major PostgreSQL engine version.
Check glibc version [Warning]	glibc versions of source and destination must be compatible.
Check disk size	Source data volume cannot exceed 80% of the destination storage capacity.
Check wal keep size	`wal_keep_size` must be large enough to retain WAL logs needed by the destination.
Check spec param	`max_connections` and `max_prepared_transactions` on the source cannot exceed the destination values.
Check rds user	The `alicloud_rds_admin`, `aurora`, and `replicator` accounts must not exist on the source.
Check extensions	Extensions on the source must be compatible with the destination.
Check logical replication slot [Warning]	Logical replication slots on the source are not synced to the destination.
Check local cross db access [Warning]	Cross-database access via `postgres_fdw` or `dblink` may break after failover.
Check Postgres system catalogs	Verifies system catalog compatibility (4 sub-checks).
Check reserved role permission	Accounts on the source must not hold reserved roles that the destination disallows.

Solutions

Check rds empty

What is checked: Whether any non-empty databases exist on the destination instance.

Error:

error:xxx not empty, check if any table exists

Fix: Clear all non-empty databases on the destination instance. Retain template0, template1, and postgres.

Check source connectivity

This check runs six sub-checks against the source instance.

Sub-check 1: IP accessibility

Error:

error:xxx.xxx.xxx.xxx is unapproachable

Cause: The IP address or domain name of the source instance is unreachable from the destination instance's network environment.

Fix: Configure the network environment and verify that all network settings are correct.

Sub-check 2: Port connectivity

Error:

error:5432 is unapproachable

Cause: The source instance's port cannot be reached from the destination instance.

Fix:

Add the following setting to the postgresql.conf file of the source instance, then restart the instance:
```
listen_addresses = '*'
```
Configure the firewall to open the required port.

Sub-check 3: Database connection

Error:

error:cannot connect to source database by replicatoraccount:replicatorPassword

Causes:

The synchronization account password is invalid.
The pg_hba.conf file is incorrectly configured.

Fix:

Verify that you can connect to the source using the synchronization account. To reset the password:
```
ALTER ROLE replicatoraccount WITH PASSWORD 'replicatorPassword';
```
Add the following entry to pg_hba.conf to allow the synchronization account to connect:
```
host all replicatoraccount <VPC CIDR block of the destination instance> md5
```

Sub-check 4: REPLICATION privilege

Error:

error:replicatoraccount has no replication privilege

Causes:

The synchronization account does not have the REPLICATION privilege.
The pg_hba.conf file is incorrectly configured.

Fix:

Grant the REPLICATION privilege to the synchronization account:
```
ALTER ROLE replicatoraccount REPLICATION;
```

Add the following entry to pg_hba.conf to allow replication connections:

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

Sub-check 5: CREATEROLE privilege

Error:

error:replicatoraccount has no createrole privilege

Cause: The synchronization account does not have the CREATEROLE privilege.

Fix: Grant the CREATEROLE privilege to the synchronization account:

ALTER ROLE replicatoraccount CREATEROLE;

Sub-check 6: pg_monitor role

Error:

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

Cause: The synchronization account is not a member of the pg_monitor role, which is required to query system views such as pg_stat_replication and pg_stat_wal_receive.

Fix: Grant the pg_monitor role to the synchronization account:

GRANT pg_monitor TO replicatoraccount;

Check major version

What is checked: Whether the source and destination run the same major PostgreSQL engine version.

Error:

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

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

Check glibc version

What is checked: Whether the glibc version of the source instance is compatible with the destination. [Warning]

Warning

warning:source glibc version is not compatible with rds pg

Cause: In glibc version 2.28, the collation of specific UTF-8 characters changed. Incompatible glibc versions between source and destination may produce different collation results.

Fix: Run the following checks to assess the risk.

Check table collations:

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;

If true is returned, no action is needed.
If false is returned, continue to the next step.

Check database collations:
```
SELECT datname, datcollate FROM pg_database WHERE datcollate NOT IN ('C', 'POSIX');
```
- If no rows are returned, no action is needed.
- If rows are returned, continue to the next step.

Check all indexes with collations other than 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');

If no rows are returned, no risks exist.
If rows are returned, collation-related risks may occur after DR deployment.

Check disk size

What is checked: Whether the destination storage capacity is sufficient.

Error:

error:source_db_size > disk_size * 0.8

Cause: The source data volume exceeds 80% of the destination storage capacity.

Fix:

Estimate the source data volume in MB:

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

If the destination storage capacity is insufficient, expand it. For more information, see Change instance specifications.

Check wal keep size

What is checked: Whether wal_keep_size is large enough to retain the write-ahead log (WAL) files needed by the destination during initial sync.

Error:

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

Cause: The value of wal_keep_size (or wal_keep_segments for older versions) is too small.

If the value is 0, an error is reported.
If the value is below the threshold, a warning is reported.

Fix:

For PostgreSQL 13 or later: increase the value of wal_keep_size.
For versions earlier than PostgreSQL 13: increase the value of wal_keep_segments.

Check spec param

What is checked: Whether max_connections and max_prepared_transactions on the source are within the limits set by the destination.

Error:

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

Cause: To establish a primary/secondary replication relationship, the source values of max_connections and max_prepared_transactions cannot exceed those of the destination.

Fix: Decrease the values of max_connections and max_prepared_transactions on the source instance, then restart it.

Check rds user

What is checked: Whether the alicloud_rds_admin, aurora, or replicator accounts exist on the source instance. These are reserved as system accounts on the destination.

If the source instance is an ApsaraDB RDS for PostgreSQL instance, skip this check.

Error:

error:xxx will be reused in rds

Fix: Delete the conflicting account identified in the error message from the source instance.

Check extensions

Sub-check 1: Unsupported extensions

Error:

error:Check source supported extensions xxx not supported

Cause: The source has extensions that the destination does not support.

Fix: Delete the unsupported extensions from the source instance.

Sub-check 2: Extensions with a higher version

Error:

error:Check source extensions with higher version xxx

Cause: The source has extensions with versions later than those supported by the destination.

Fix: Install an earlier version of the extension that the destination supports, or delete the extension.

Sub-check 3: Extensions with a lower version

Warning

warning:Check source extensions with lower version xxx

Cause: The source has extensions with versions earlier than those supported by the destination.

Fix: No action needed. Extension versions are updated automatically during DR deployment.

Check logical replication slot

What is checked: Whether logical replication slots exist on the source instance. [Warning]

Warning

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

Cause: Logical replication slots are not synced to the destination. After the destination is promoted to primary, downstream logical replication subscribers cannot find their slots.

Fix: No fix is available. Take note that logical replication slots are not synchronized to the destination instance. In this case, risks may occur.

Check local cross db access

What is checked: Whether cross-database access via postgres_fdw or dblink is configured on the source instance. [Warning]

Warning

warning:Foreign servers for local cross-database access exist

Cause: The destination instance's local PostgreSQL service listens on port 5432, which may differ from the source. After the destination is promoted to primary, existing cross-database access configurations become invalid.

Fix: No action needed before DR deployment. After the destination is promoted to primary, reconfigure the port used for cross-database access.

Check Postgres system catalogs

Sub-check 1: Unsupported languages

Error:

error: disallowed language exist in databases [xxx, xxx], the languages allowed are [c, internal, sql, plpgsql, pltcl, plperl].

Cause: ApsaraDB RDS for PostgreSQL supports only C, internal, SQL, PL/pgSQL, PL/Tcl, and PL/Perl. Languages outside this set cannot be used on the destination.

Fix: Delete the unsupported languages from the source instance.

Sub-check 2: Large objects

Error:

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

Cause: ApsaraDB RDS for PostgreSQL does not support large objects.

Instances migrated from self-managed PostgreSQL databases may contain large objects. This error is common for such instances.

Fix: Delete all large objects from the source instance.

Sub-check 3: Default ACL settings

Error:

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

Cause: The ALTER DEFAULT PRIVILEGES ... GRANT ... statement has been run on one or more databases, leaving pg_catalog.pg_default_acl non-empty.

Fix:

On all databases of the source instance, run ALTER DEFAULT PRIVILEGES ... REVOKE ... to clear pg_catalog.pg_default_acl.
After DR deployment is complete, run ALTER DEFAULT PRIVILEGES ... GRANT ... on the destination to restore the default permissions.

Sub-check 4: Non-empty system tables

Warning

warning: Invalid system tables: [pg_parameter_acl, pg_db_role_setting], these system tables should be empty.

Cause:

pg_parameter_acl records user access permissions on kernel parameters. ApsaraDB RDS for PostgreSQL does not support modifying these parameters.
pg_db_role_setting records the default permissions of users on different databases.

Fix:

Clear both tables on the source instance:

DELETE FROM pg_parameter_acl;
DELETE FROM pg_db_role_setting;

After DR deployment is complete, configure the default permissions of the users on different databases in the destination instance.

Check reserved role permission

What is checked: Whether any accounts on the source hold reserved roles that the destination does not allow.

Error:

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

Cause: In ApsaraDB RDS for PostgreSQL, accounts cannot hold certain reserved roles such as pg_write_server_files.

Fix: Revoke the disallowed reserved roles from the affected accounts. Example:

REVOKE pg_write_server_files FROM "user";