PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development that has earned it a powerful reputation for reliability, feature robustness, and performance. PostgreSQL Global Development Group periodically releases new minor and major versions containing new features, performance improvements, and security enhancements. Azure Database for PostgreSQL Flexible server currently supports PostgreSQL versions 11, 12,13,14 and 15 (preview) and Postgres community releases a new major version about once a year and Flexible Server onboards recent version once they are released by the community.
Note: The feature was originally previewed on Feb-2023, and is generally available starting June-2023.
Major Version Vs Minor Version
The PostgreSQL version numbering system underwent a change starting with version 10. The first digit of the version number now represents a major version change, such as from 10 to 11. Meanwhile, the second digit signifies a minor version change, such as from 14.2 to 14.4. This change was made to provide a clearer and more predictable release schedule for database users. Minor version upgrades include changes that are backward-compatible with existing applications. Major version upgrades are more complicated as they can include breaking changes and new features that may not be backward-compatible with existing applications. Azure Database for PostgreSQL Flexible service updates the minor versions of the database during the customer's maintenance window, but major version upgrades require a separate process. Upgrading to a higher version of PostgreSQL often involves data migration, which can be a complex and time-consuming process that requires additional resources and results in business downtime.
Starting today major version upgrade for flexible server is generally available which makes it easier for customers to upgrade their servers to a higher version. This feature eliminates the need for data migration, which traditionally involves setting up new servers and storage resulting in added costs, effort, and downtime. In-place upgrade process is faster, minimizes downtime, and keeps the existing server name and other settings intact, providing customers with greater ease in managing their infrastructure. Additionally, there is no need to change application connection strings, further simplifying the upgrade process for customers.
In-Place Major Version Upgrade Benefits:
- Saves manual effort: The upgrade process eliminates the need for data migration, reducing the amount of manual effort required.
- Reduced cost: Since data migration is not required, customers can save on costs associated with provisioning new servers or storage.
- Shorter downtime: The upgrade process is not a size-of-data operation, resulting in shorter downtime for the business.
- No application changes required: The upgrade process retains the existing server name, eliminating the need for changes to the application connection strings.
Process.
In-place major version upgrade for Azure Database for PostgreSQL Flexible server can be initiated through either the portal or the CLI. Here is a high-level breakdown of this process:
Pre-check: Before the upgrade begins, the flexible server performs a pre-check to identify any potential issues that might cause the upgrade to fail. If any incompatibilities are found, an error log is generated, and the upgrade is stopped.
Backup: If the pre-check is successful, the flexible server stops the service and automatically takes a backup of the database. This backup can be used to restore the database instance to its previous version if there is an upgrade error.
Run-Upgrade: The upgrade process is initiated using the pg_upgrade utility, which is designed specifically for upgrading PostgreSQL databases to a higher major version. Upon successful completion of the upgrade, the flexible server starts the service again, retaining the same server name but with an upgraded version of PostgreSQL.
How to Perform In-Place Major Version Upgrade
- You can perform in-place major version upgrade using Azure portal or CLI (command-line interface). Click the Upgrade button on the Overview blade.
2. You'll see an option to select the major version of your choice, you have an option to skip versions to directly upgrade to higher versions. Choose the version and click Upgrade.
In the below screenshot I am skipping versions 12,13 and directly going from 11->14
- Once the upgrade is successful, you can expand the Deployment details tab and access Operation details to see more information about the upgrade process like duration, provisioning state etc. You can click on the Go to resource tab to validate your upgrade. You notice that server name remained unchanged and PostgreSQL version upgraded to desired higher version with the latest minor version.
You can also run the upgrade using Azure CLI using the command below.
az postgres flexible-server upgrade -g resource_group -n server_name -v desired_pg_version
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.
-
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 and postgres_fdw are unsupported for all PostgreSQL versions.
-
Please ensure that the PostGIS extensions, installed within a specific schema, are included in your search_path server parameter. It is necessary to update this server parameter to encompass those schemas before proceeding with major version upgrade.
-
Servers configured with logical replication slots aren't supported.
Summary
In this post, I explained the in-place major version upgrade process for Azure database for PostgreSQL flexible server and its benefits. To learn more about this feature including support for read replicas, extension and limitations please refer Major version upgrade documentation.
I hope that this post provides you with a better understanding of in-place major version upgrade in Azure database for PostgreSQL and helps make your life as a database owner easier and more productive. We are always eager to get your feedback, please reach out via email to Ask Azure DB for PostgreSQL.
Microsoft
