Upgrade the Major Version of the Database

Recent Pages

Upgrade the Major Version of the Database

Last updated: 2024-03-20 14:24:52
Overview and Advantages
There are new PostgreSQL versions continuously provided to customers. In new versions, more features, better performance, and higher stability and security are introduced. Therefore, it is recommended that you plan properly based on your business needs and perform upgrades to the major version of the database as soon as possible. TencentDB for PostgreSQL supports major version upgrades, and this tool has the following advantages:
Supports cross-version upgrades among PostgreSQL 9 to 15. Supports upgrade rehearsals.
The upgrade is performed on a read-only instance pulled from the original instance, with no impact to the original instance.
The upgrade process has only a momentary disconnect and a short read-only impact on the business.
The read time depends on the number of original instance objects, regardless of data scale.
After the original instance upgrade is completed, connection addresses, tags, monitoring, backup sets, and other information are fully retained.
Note:
Currently, major version upgrades are not supported for instances with transparent data encryption enabled.
Directions
Formal Major Version Upgrade
1. Log in to the PostgreSQL Console. In the instance list, click the Instance ID or Operation in the Management column to enter the instance detail page.
2. Click Version Upgrade to open the version upgrade operation page.
﻿
3. Click Upgrade Major Version to open the version upgrade page.
3.1 Target version
The current system will return all upgradable versions for you to choose, and the latest version number will be returned for each major version.
3.2 Upgrade time
There will be momentary disconnections and temporary read-only status during the upgrade process, therefore businesses need to evaluate the operational time window. You can choose to execute immediately, at a specific time, or within the maintenance time. For maintenance time settings, see Setting Instance Maintenance Time.
When the upgrade is completed but the specified or maintenance time window is not reached, the instance status is Waiting for Switch. You can navigate to Instance List > Operation > Switch Now on the console to complete the upgrade process.
3.3 Collecting statistics
Accurate statistics can ensure that PostgreSQL query planner processes queries in the most optimal way. Lack of statistics may lead to incorrect query planning, which could hamper performance and consume too much memory. This operation mainly involves running ANALYZE on the master instance and updating system statistical information after the upgrade. The duration of generating statistics depends on the size of the instance data.
3.4 Plugin upgrade setting
This operation is to execute ALTER EXTENSION UPDATE in the database where extensions were created after the upgrade. There are three options:
Upgrade extensions prior to upgrade completion: As it requires checking and upgrading the extension list of all databases to the corresponding version, the execution time is proportional to the number of databases and extensions. The overall execution time of the major version upgrade will be extended. Therefore, please perform evaluation before choosing this option.
Upgrade extensions after upgrade completion: Read/write is immediately resumed after upgrade. You need to analyze the effect on your business before the extension upgrade is completed.
Do not upgrade extension version: Read/write is immediately resumed after upgrade. You need to perform the extension upgrade by yourself, and analyze the impact on business before the extension upgrade is completed.
3.5 Whether to perform backup before the upgrade starts
During a major version upgrade, to ensure the recoverability of data, the system by default conducts two backups. The type of backup is Upgrade Backup:
A full backup will be performed before the upgrade starts, which will be done immediately before the upgrade. You can restore the database instance to the state of its previous version using this backup.
A full backup will be performed after the upgrade is completed. This backup is created immediately after new writes are allowed on the upgraded database instance.
Backup options before the upgrade:
No: Choose this option when there are existing backups that can restore the instance to its pre-upgrade state. Otherwise, this option is not recommended.
Yes: It is the default option. Charges may apply after backup commercialization. For details, see Backup Space Billing. You can choose to delete it after the upgrade verification is done. The backup file deletion policy is also managed by User Setting's backup retention rules. If you want to extend the backup retention period, see the following figure:
﻿
3.6 Task launch settings
Only perform check, and do not initiate task: A pre-upgrade check will be performed, including checking instance's running state, parameter setting validity, database connection, and so on. However, no task will be initiated. Users can use this operation to pre-check upgrade feasibility.
Perform check and initiate task: A pre-upgrade check will be performed, including instance's running state, parameter setting validity, database connection, and so on. If the check is passed, the task will be directly initiated.
﻿
4. Clicking Commit will create an upgrade task. The instance status will be set to Kernel Version Upgrading. You can check the task status and system logs in the task list.
﻿
﻿
Drill Upgrade
To ensure the success rate of the upgrade, a drilling upgrade can be performed first for the businesses. In a drilling upgrade, a new instance is cloned first based on the current instance's backup set, the upgrade is performed on the new instance, and then a new instance is created after the upgrade is complete. You can choose to use the instance directly or delete the instance. Instances generated through cloning will be billed normally in the pay-as-you-go billing mode. For specific prices, see Pricing. The specific operations are as follows:
1. Log in to the PostgreSQL Console. In the instance list, click the Instance ID or Operation in the Management column to enter the instance detail page.
2. Click Version Upgrade to open the version upgrade operation page.
3. Click Major Version Upgrade Drill to enter the rehearsal operation interface.
3.1 Cloned instance settings
For specific cloned instance settings, see Cloning Instance.
﻿
3.2 Upgrade settings
For upgrade settings, see Formal Major Version Upgrade.
Click Commit to initiate a drill task. If the original instance is postgres-3e3ug2nj, the system will produce a new instance named postgres-3e3ug2nj-pre-upgrade-20230809154431, and complete the subsequent upgrade.
How It Works
To help you understand the system, and manage the major version upgrade time, this article shows the operations performed by the system during major version upgrade, as shown in the figure below:
﻿
The above figure uses the upgrade from PostgreSQL 10 to PostgreSQL 14 as an example, and detailed description of the backend system operating steps are as follows:
1. Precheck
Check if the instance state is running.
Check the validity of instance parameters.
Check if the target upgrade version is valid.
2. Create a read-only instance
Create a read-only instance of the source instance, to minimize the impact of upgrades on the original instance. Subsequent upgrade operations will be performed on this read-only instance.
3. Initialize the new version database
Create a blank directory, and initialize a new instance with the target version. This new instance only has a single node.
4. Stop the high version node, and set source instance master node to read-only.
Stop the new version instance, and set the source instance to read-only.
5. Promote the read-only instance to the master instance, and stop the process.
Promote the read-only instance to the master instance, and stop this instance.
6. Perform the upgrade
Perform the upgrade, export and import the metadata, and process data.
7. Launch the new version process, switch routes, build new standby machines, and reclaim the old nodes.
Launch the upgraded new version instance, switch the routing information of the original instance, and set up a secondary node for the new instance. In the end, clean up the environment to finish the task.
Note:
After the system upgrade is completed, carry out relevant verification to ensure the smooth operation of the business as follows:
Please run a business load test on the database after the upgrade.
Verify extension compatibility.
Verify parameter compatibility.
System Limits
If the instance has associated read-only instances, you need to delete the read-only instances before initiating the upgrade.
If the instance is overused in disk space, a major version upgrade cannot be initiated.
FAQs
Is the Major Version Downgrade supported?
The major version downgrade is not supported. The current TencentDB for PostgreSQL is forward compatible with major versions, a higher version is recommended.
﻿
﻿