failed to upgrade postgresql flexible server

Question

Hello,

I have recently migrated our Azure Database for PostgreSQL single server (v 11.18) test environment database to Azure Database for PostgreSQL flexible server (v 11.21). I intentionally kept it at the same major version during the migration, but now I am trying to upgrade to a newer major version. We have tested version 13 locally for quite some time now, so I tried upgrading to this version but both this version and version 12 failed.

Timestamp: 2/19/2024, 10:34:44 AM GMT+1

Status code: Conflict Error: Internal Server Error - The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'

Correlation ID: a7ce5797-8181-4ee3-a4cb-df51d598495b

Region: West Europe I have tried this several times, using the upgrade button in the Azure portal. Same result every time.

Answer 1

Hi
METALDr •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error when trying to upgrade Azure Database for PostgreSQL Single to Flexible Server.

We are sorry about the inconvenience you are facing.

Could you please let us know if you have considered all limitations: If in-place major version upgrade pre-check operations fail, then the upgrade aborts with a detailed error message for all the below limitations. IIf in-place major version upgrade pre-check operations fail, then the upgrade aborts with a detailed error message for all the below limitations.

• In-place major version upgrade currently doesn't support read replicas, so if you have a read replica enabled server, you need to delete the replica before performing the upgrade on the primary server. After the upgrade, you can recreate the replica.
• Azure Database for PostgreSQL - Flexible Server requires the ability to send and receive traffic to destination ports 5432, and 6432 within VNET where Flexible Server is deployed, as well as to Azure storage for log archival. If you configure Network Security Groups (NSG) to restrict traffic to or from your Flexible Server within its deployed subnet, please make sure to allow traffic to destination ports 5432 and 6432 within the subnet and to Azure storage by using service tag Azure Storage as a destination.If network rules are not set up properly HA is not enabled automatically post a major version upgrade and you should manually enable HA. Please modify your NSG rules to allow traffic for the destination ports and storage as requested above and enable a high availability feature on the server.
• In-place major version upgrade doesn't support certain extensions and there are some limitations to upgrading certain extensions. The extensions Timescaledb, pgaudit, dblink, orafce, pg_partman, and postgres_fdw are unsupported for all PostgreSQL versions.
• When upgrading servers with PostGIS extension installed, set the search_path server parameter to explicitly include the schemas of the PostGIS extension, extensions that depend on PostGIS, and extensions that serve as dependencies for the below extensions. e.g postgis,postgis_raster,postgis_sfcgal,postgis_tiger_geocoder,postgis_topology,address_standardizer,address_standardizer_data_us,fuzzystrmatch (required for postgis_tiger_geocoder).
• Servers configured with logical replication slots aren't supported.

Steps needed to establish connectivity between your Single and Flexible Server

• If your single server is public access (case #1 and case #2 in the above table), there's nothing needed from your end. The single to flex migration tool automatically establishes connection between single and flexible server and the migration goes through.
• If your single server is in private access, then the only supported scenario is when your Flexible server is inside a VNet. If your flexible server is deployed in the same VNet as the private end point of your Single server, connections between single server and flexible server should automatically work provided there's no network security group(NSGs) blocking the connectivity between subnets. If flexible server is deployed in another VNet, peering should be established between the VNets for the connection to work between Single and Flexible server.

Single server supports PG version 9.6,10 and 11 while Flexible server supports PG version 11, 12, 13 and 14. Given the differences in supported versions, you might be moving across versions while migrating from single to flexible server. If that is the case, make sure your application works well with the version of flexible server you're trying to migrate to. If there are breaking changes, make sure to fix them on your application before migrating to flexible server. Use this link to check for any breaking changes while migrating to the target version.

• You can have only one active migration or validation to your Flexible server.
• The source and target server must be in the same Azure region. Cross region migrations are enabled only for servers in India, China and UAE as Flexible server might not be available in all regions within these geographies.
• The tool takes care of the migration of data and schema. It doesn't migrate managed service features such as server parameters, connection security details and firewall rules.
• The migration tool shows the number of tables copied from source to target server. You need to manually validate the data in target server post migration.
• The tool migrates only user databases. System databases like azure_sys, azure_maintenance or template databases such as template0, template1 will not be migrated.

The following limitations are applicable only for flexible servers on which the migration of users/roles functionality is enabled.

• Azure Active Directory users present on your source server are not migrated to the target server. To mitigate this limitation, manually create all Azure Active Directory users on your target server using this link before triggering a migration. If Azure Active Directory users aren't created on target server, migration fails with appropriate error message.
• If the target flexible server uses SCRAM-SHA-256 password encryption method, connection to flexible server using the users/roles on single server fails since the passwords are encrypted using md5 algorithm. To mitigate this limitation, choose the option MD5 for password_encryption server parameter on your flexible server.

Let us know if above helps. If not, please let us know more queries.

Thanks.

Answer 2

Hello @ShaktiSingh-MSFT ,

We weren't able to upgrade postgres flexible server v11 to v13 via the in place major version upgrade, but ended up migrating directly from single server v11 to flexible server v13, which did work.

I will go over our the prerequisites for the in place major version upgrade, just in case I missed something there.

In my original post I mentioned we had already successfully completed our migration from single to flexible server, so I won't go over the prerequisites for that.- In-place major version upgrade currently doesn't support read replicas, so if you have a read replica enabled server, you need to delete the replica before performing the upgrade on the primary server. After the upgrade, you can recreate the replica.

We didn't have any read only replicas enabled, the server had storage auto growth enabled, which should be incompatible with this anyway.- *Azure Database for PostgreSQL - Flexible Server requires the ability to send and receive traffic to destination ports 5432, and 6432 within VNET where Flexible Server is deployed, as well as to Azure storage for log archival. If you configure Network Security Groups (NSG) to restrict traffic to or from your Flexible Server within its deployed subnet, please make sure to allow traffic to destination ports 5432 and 6432 within the subnet and to Azure storage by using service tag Azure Storage as a destination.If network rules are not set up properly HA is not enabled automatically post a major version upgrade and you should manually enable HA. Please modify your NSG rules to allow traffic for the destination ports and storage as requested above and enable a high availability feature on the server.
*

This is where things were a bit unclear to me. The server's firewall was configured to allow all traffic from any Azure service but we didn't have any NSGs configured. We tried to add an NSG but this didn't make any difference. Do you have to have them in order to upgrade?

• In-place major version upgrade doesn't support certain extensions and there are some limitations to upgrading certain extensions. The extensions Timescaledb, pgaudit, dblink, orafce, pg_partman, and postgres_fdw are unsupported for all PostgreSQL versions. None of the those extensions were used on the database server.
• When upgrading servers with PostGIS extension installed, set the search_path server parameter to explicitly include the schemas of the PostGIS extension, extensions that depend on PostGIS, and extensions that serve as dependencies for the below extensions. e.g postgis,postgis_raster,postgis_sfcgal,postgis_tiger_geocoder,postgis_topology,address_standardizer,address_standardizer_data_us,fuzzystrmatch (required for postgis_tiger_geocoder).

The server didn't have PostGIS installed as far as I'm aware.

• Servers configured with logical replication slots aren't supported.
  

We also didn't have logical replication configured on the server.

So it is still unclear to me why we were unable to upgrade to version 13, but as I said earlier in the post we were able to simply migrate from single server v11 to another flexible server which was already configured on v13.

We also tried to migrate directly to a flexible server on v16 but this didn't work because it was complaining about the following extensions: pg_buffercache, pg_stat_statements, uuid-ossp. And I could only get it fixed for one of those, but I suspect this belongs in a different topic.