ApsaraDB RDS:Introduction to the check report of a major engine version upgrade for an ApsaraDB RDS for PostgreSQL instance

Disk warning

Warning format: Total disk space: {*} GB; Used disk: {*} GB; Used inodes: {*}; bytes-per-nodes: {*}; Minimum disk space required for upgrade: {*} GB

Cause: The instance has too many objects; the current storage is insufficient for the upgrade.

Solution:

• Blue-green deployment mode: Set the storage capacity of the new instance to a value greater than or equal to the minimum disk space required for upgrade.

• Local upgrade mode: Expand the instance's storage capacity to a value greater than or equal to the minimum disk space required for upgrade before starting the upgrade. For more information, see Change the configuration.

Memory warning

Warning format: Current memory: {*} GB; Recommended memory: {*} GB; Minimum memory: {*} GB

Cause: The instance has too many objects; current memory is insufficient to minimize read-only time during the upgrade.

Solution:

• Blue-green deployment mode: Set the instance specification of the new instance so that its memory meets or exceeds the minimum memory requirement.

• Local upgrade mode: Upgrade the instance specification if the current memory is less than the recommended memory capacity. For more information, see Change the configuration.

Subscription warning

Warning format: The instance has replication slot subscribers. To prevent subscription data inconsistency, see the Alibaba Cloud documentation

Cause: The instance has replication slot subscribers. Run the following query to check:

SELECT * FROM pg_subscription;

Solution: Follow the steps in the Upgrade the database major version topic for handling replication slot subscribers.

Sequence warning (zero-downtime mode only)

Warning format: Total Sequence tables: {0}. Too many Sequence tables will extend the switchover time for logical replication major version upgrade.

Cause: The instance contains sequences. Run the following query to list them:

SELECT * FROM pg_sequences;

Solution: If the instance has a large number of sequences, clone the instance and run a test upgrade to measure the actual switchover time before upgrading production.

Account error

Error format: Invalid superuser account: {*}; Invalid account: {*}; Please see the Alibaba Cloud documentation

Cause: The instance has redundant superuser accounts or standard accounts with invalid encryption configurations.

Solution:

• Redundant superuser accounts: Submit a ticket to request deletion.

• Invalid standard accounts: Reset the passwords of the affected accounts.

Pre-check error

Error format: pg_upgrade pre-check task failed, need to check [pg_upgrade error log] and [pg_upgrade-related files and errors]

Cause: The pg_upgrade pre-check failed.

Solution: Review the pg_upgrade error log section of the check report to identify and resolve the specific issue.

pgcrypto extension error

Error format: The pg_crypto extension is installed in schema: pg_catalog in database: {*}, please see the Alibaba Cloud documentation

Cause: The pgcrypto extension creates functions in pg_catalog that only exist in later PostgreSQL versions. This causes the major engine version upgrade to fail.

Solution: In each affected database, drop the pgcrypto extension and recreate it in a schema other than pg_catalog.

Minor version error (zero-downtime mode only)

Error format: Zero-downtime major version upgrade requires minimum minor version 20250228

Cause: The instance's minor engine version is earlier than 20250228.

Solution: Upgrade the minor engine version to 20250228 or later.

loadable_libraries.txt: incompatible extensions

The loadable_libraries.txt file lists library files that cannot be loaded by the target PostgreSQL version. Each library typically corresponds to an extension.

Cause: One or more installed extensions are incompatible with the target major engine version.

Solution: Check the extensions listed in loadable_libraries.txt. For each extension, verify whether your application depends on it, then decide whether to drop it before the upgrade. For the list of extensions supported in each version, see Supported extensions.

The following sections describe the most common incompatible extensions.

tables_with_oids.txt: tables with OIDs

The tables_with_oids.txt file lists tables that were created with the WITH OIDS declaration. PostgreSQL 12 and later do not support WITH OIDS.

Cause: One or more tables use the WITH OIDS declaration, which is not supported in the target version.

Solution:

• Option 1 (recommended): Upgrade to PostgreSQL 11, which still supports WITH OIDS.

• Option 2: Review the tables listed in tables_with_oids.txt and confirm your application does not depend on OID values. Then run the following statement for each affected table:

  ALTER TABLE {table_name} SET WITHOUT OIDS;

Foreign tables

Error format: Foreign tables in database: {0}. Total databases: {1}

Cause: The instance contains foreign tables, which logical replication cannot replicate.

Solution: Drop all foreign tables.

1. In each database, identify the foreign tables:

   SELECT COUNT(*) AS count, relkind
   FROM pg_class
   WHERE relkind IN ('f')
   GROUP BY relkind;

2. Drop the foreign tables:

   DROP FOREIGN TABLE [ IF EXISTS ] name [ CASCADE | RESTRICT ];

For more information, see the PostgreSQL documentation on DROP FOREIGN TABLE.

Tables without a primary key or unique key

Error format: Tables without primary key or unique key in database: {0}. Total databases: {1}

Cause: Logical replication requires each table to have a primary key, a unique key, or REPLICA IDENTITY FULL to identify rows during replication.

Solution: Add a primary key or unique key to each affected table, or set REPLICA IDENTITY FULL.

1. In each database, identify tables without a primary key or unique key:

   SELECT COUNT(*) AS count
   FROM information_schema.tables t
   LEFT JOIN information_schema.table_constraints tc
       ON t.table_name = tc.table_name
       AND t.table_schema = tc.table_schema
       AND (tc.constraint_type = 'PRIMARY KEY' OR tc.constraint_type = 'UNIQUE')
   JOIN pg_class c
       ON t.table_name = c.relname
   JOIN pg_namespace ns
       ON c.relnamespace = ns.oid
       AND ns.nspname = t.table_schema
   WHERE t.table_type = 'BASE TABLE'
       AND tc.constraint_type IS NULL
       AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
       AND c.relreplident != 'f';

2. Set REPLICA IDENTITY FULL on the table (if adding a key is not feasible):

   ALTER TABLE name REPLICA IDENTITY FULL;

3. Or add a primary key or unique key:

   -- Primary key
   ALTER TABLE name
       ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
   
   -- Unique key
   ALTER TABLE name
       ADD CONSTRAINT constraint_name UNIQUE (column_name);

For more information, see the PostgreSQL documentation on unique constraints.

Materialized views

Error format: Materialized views in database: {0}. Total databases: {1}

Cause: Logical replication cannot replicate materialized views.

Solution: Drop all materialized views.

1. In each database, identify the materialized views:

   SELECT COUNT(*) AS count, relkind
   FROM pg_class
   WHERE relkind IN ('m')
   GROUP BY relkind;

2. Drop the materialized views:

   DROP MATERIALIZED VIEW IF EXISTS name;

Large objects

Error format: Large objects in database: {0}. Total databases: {1}

Cause: Logical replication cannot replicate large objects stored in pg_largeobject.

Solution: Drop the large objects.

1. In each database, count the large objects:

   SELECT COUNT(*) AS count
   FROM pg_largeobject_metadata;

2. Drop each large object:

   SELECT lo_unlink(largeobject_oid);

Prohibited extensions

Error format: Logical replication prohibited extensions in database: {0}. Total databases: {1}

Cause: The following extensions are incompatible with logical replication and cannot be present during a zero-downtime upgrade: pg_partman, pg_cron, pg_active, and pglogical.

Solution: Temporarily drop the incompatible extensions.

1. In each database, check whether any prohibited extensions are installed:

   SELECT COUNT(*) AS count
   FROM pg_extension
   WHERE extname IN ('pg_partman', 'pg_cron', 'pg_active', 'pglogical');

2. Drop each prohibited extension:

   DROP EXTENSION extension_name;

Parameter requirements not met

Error format: Logical replication requires wal_level to be logical, max_wal_sender and max_replication_slots to be at least {0}, instance wal_level is: {1}, max_wal_sender is: {2}, max_replication_slots is: {3}

Cause: One or more of the following parameters do not meet the requirements for logical replication: wal_level, max_wal_sender, or max_replication_slots.

Solution: Update the parameter values. Some changes require a restart to take effect. For more information, see Set the parameters of an instance.