Error Upgrading PostgreSQL Flexible Server

Question

Hey
I'm running into a error upgrading my PostgreSQL Flexible Server from version 14 to 16
The deployment fails with a status conflict and returns this error:

{"code":"InternalServerError","message":"An unexpected error occured while processing the request. Tracking ID: 'a7e1203a-8240-4190-9e05-0359777cf693'"}

Has anyone seen this before or know how to resolve it?

Answer 1

Hi Marvin Khywah

It seems like you are getting error message while upgrading from PostgreSQL Flexible Server from V14 to V16

During the process of an in-place major version upgrade, Azure Database for PostgreSQL flexible server runs a pre-check procedure to identify any potential issues that might cause the upgrade to fail.

If the pre-check finds any incompatibilities, it creates a log event that shows that the upgrade pre-check failed, along with an error message.

If pre-check operations fail for an in-place major version upgrade, the upgrade fails with a detailed error message for all the following limitations:

• In-place major version upgrades don't support certain extensions, and there are some limitations to upgrading certain extensions. The following extensions are unsupported for all PostgreSQL versions: Timescaledb, pgaudit, dblink, orafce, pg_partman, postgres_fdw
• ervers configured with logical replication slots aren't supported.
• Servers using SSDv2 storage do not support Major Version Upgrades.
• In-place major version upgrades currently don't support read replicas. If you have a server that acts as a read replica, you need to delete the replica before you perform the upgrade on the primary server. After the upgrade, you can re-create the replica.

To resolve the 'InternalServerError' encountered during the upgrade of Azure Database for PostgreSQL Flexible Server from version 14 to 15 or 15 to 16, you can follow these steps to ensure all prerequisites and dependencies are correctly handled:

1. Check for Unsupported Extensions and Features

Ensure that there are no unsupported extensions or features that might be causing the upgrade to fail. For example, the 'dblink' extension is known to cause issues during upgrades.

• Action: Drop unsupported extensions using the following command:

DROP EXTENSION dblink CASCADE;

2. Review and Handle Logical Replication Slots

Logical replication slots and their dependent features like subscriptions and publications are not supported during major version upgrades.

• Action: Drop any logical replication slots and subscriptions before attempting the upgrade:

SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots;

3. Check Disk Space and File Permissions

Ensure that there is sufficient disk space and that file permissions are correctly set to avoid issues during the file copying process.

• Action: Verify disk space and adjust PostgreSQL's shared memory usage if necessary:

SELECT pg_size_pretty(pg_database_size('your_database_name'));

4. Review State Machine Operations and Logs

Investigate the state machine operations and logs to identify any specific failures or errors.

• Action: Use the following Kusto query to review logs:

OBvmagentsidecarpgsql
| where LogicalServerName == "your_server_name"
| where TIMESTAMP >= StartTime and TIMESTAMP <= EndTime
| project TIMESTAMP, MessageString

5. Perform Prechecks and Validate Inputs

Ensure that all prechecks are passed and that the input parameters for the upgrade are valid.

• Action: Validate the input parameters and perform prechecks:

SELECT * FROM pg_extension;

6. Handle Role Management Issues

Post-upgrade, ensure that roles have the necessary permissions, especially if upgrading to PostgreSQL 16, which introduces new permission requirements.

• Action: Grant the necessary permissions to roles:

GRANT azure_pg_admin TO your_role WITH ADMIN OPTION;

7. Retry the Upgrade with Adjustments

After addressing the above points, retry the upgrade process.

Please do let me know if you have any further concerns. I am happy to address your queries.