RDS PostgreSQL one-click major version upgrade technology decryption

Introduction to content of 160f7e920135eb: 1. PostgreSQL industry position 2. PostgreSQL version upgrade background 3. PostgreSQL version upgrade decryption 4. PostgreSQL version upgrade results

1. PostgreSQL industry position

(1) Industry location

Before discussing the position of PostgreSQL (hereinafter referred to as PG) in the entire database industry, let's take a look at the position of Alibaba Cloud database in the global database industry.

• Magic Quadrant Leader

*Gartner 2020, Alibaba Cloud database advances

Leader in the Magic Quadrant for Global Databases

• PG Best Product of the Year Award

*At the 2020 PG Asia Conference, the exclusive cluster MyBase for Alibaba Cloud database won the "PG Annual Best Product Award"

Next, we look at the position of PG database in the industry.

• global database ranking
  

*The best database obtained by PostgreSQL for 3 consecutive years is ranked TOP2 in the open source database ranking, and ranked TOP4 in the global popularity trend.

• widely used

*PG database is widely used in all walks of life, including: computer software, information technology and services,

Medical and health, financial services, higher education, communication services and other fields

(2) RDS PG VS self-built PG

After understanding the position of PG in the industry, let's take a look at the advantages of Alibaba Cloud RDS PG compared to self-built PG.

*As shown in the figure above, compared to self-built PG, the advantages of RDS PG are mainly reflected in

four aspects: reliability, security, intelligence and rich plug-in

1. Reliability

RDS PG provides Logical Slot Failover capability. In active/standby mode, Logical Slot can continue to provide users with data synchronization after HA switchover occurs. This solves the problem that self-built PG cannot achieve incremental data synchronization during HA switchover. problem.

Standby of RDS PG supports multiple upstream nodes. After HA is switched, the read-write separation function of read-only instances can still be maintained without affecting the data synchronization of read-only nodes.

One-click major version upgrade allows our users to upgrade to a higher version of PG with one-click productization, and enjoy the features and stability of the updated version of PG.

2. Security

Security is mainly divided into three aspects.

First of all, RDS PG provides cloud disk encryption. Users only need to provide a key, and RDS PG can use this user-defined key to encrypt data to disk.

Secondly, we released the SSL custom certificate function, providing custom certificates for the client and server, providing client and server anti-forgery, and improving database security.

Finally, RDS PG provides SGX full encryption, which is a hardware-based encryption technology that enables data to be encrypted on the entire link.

3. Intelligent

The entire product series of Alibaba Cloud RDS provides DAS services. Help users to provide diagnosis and optimization capabilities in the process of using the database, DAS can help users self-discovery, self-diagnosis, self-optimization, and self-decision solving user database problems.

4. Rich plug-ins

Ganos spatiotemporal engine plug-in RDS PG provides the storage, retrieval, query and analysis capabilities of spatiotemporal data.

The second plug-in is PASE efficient vector search plug-in .

The third plug-in is oss\_fdw , which realizes the scenario where data is separated from hot and cold. The cold data is stored on a cheaper OSS, and the data on OSS can be queried and analyzed on the RDS PG.

From the above, it can be found that compared with self-built PG, RDS PG has obvious advantages in reliability, security, intelligence, and plug-in richness.

2. PostgreSQL version upgrade background

The upgrade function of PostgreSQL originates from some problems encountered by users, and we also face many challenges during the upgrade.

1. Problems encountered

• old version: outdated and not maintained

Too low database version, stability challenges, such as:

1) PG 9.4, the version is too old 2) Low version, supply chain problems 3) The community is not maintained, no one is in charge

• high version: new features

Users have strong demands for higher versions and new features, such as:

1) Incremental sorting 2) Parallel index garbage collection 3) Index deduplicate capability 4) Partition table and aggregation performance improvement

2. Challenges faced

• Elasticity: Extreme elasticity

The local disk versions of PG 9.4 and PG 10.0 run on the physical machine form, resulting in relatively weak elasticity, such as:

1) Second-level snapshots 2) Elastic scalability 3) Larger storage space support 4) No performance loss in backup operations

• smooth cutover: small application perception

In the one-click major version upgrade process, how to make user applications as small as possible and smooth cutover is another huge challenge, such as:

1) Guarantee plug-in compatibility

2) Cutover, non-cutover mode 3) Rollback and verifiable capabilities 4) Zero application changes, small perception 5) One-click large version upgrade productization capabilities

In summary, we expect RDS PG to be able to productize one-click major version upgrades, smooth cutover, and provide verifiable and rollback capabilities.

Third, PostgreSQL version upgrade decryption

(1) Design principles

Based on the above thinking about the product, we mainly follow the following four principles in the process of designing RDS PG.

1. Verification rollback: verifiable and rollback -version rollback: large version rollback-DNS address: connection string rollback-verifiable: high version verifiable capability

2. Less restrictions: full coverage of the scene DDL restrictions-table structure restrictions-data type restrictions-full version coverage

3. One-click upgrade: one-click upgrade productization -refusal to upgrade manual-one-click productization capability-plug-in compatibility adaptation

4. Smooth cutover: Application non-stop service and zero downtime -Application non-stop service during the upgrade process-Fast upgrade process-Smooth cutover of connection addresses

The starting point of these four design principles is that we hope to leave the complexity to ourselves and the simplicity to the users, so as to bring users the ultimate product experience.

(2) Scheme selection

Based on the above design principles, we have to choose an upgrade plan. For large PG version upgrade, the industry mainly has the following three options .

Option 1: Logical replication

• advantages:

Good compatibility and smooth cutover

• Disadvantages:

1) Publish and subscribe at the library level

2) The table must have PK/UK 3) Does not support DDL, large objects 4) Foreign keys and triggers are disabled 5) May cause WAL log accumulation

Solution 2: pg\_upgrade

• advantages:

1) Do not copy data, only update metadata

2) High efficiency, 2TB data, upgrade <10s

• Disadvantages:

1) Upgrade pre-check

2) Rollback verification strategy 3) Parameters and plug-in compatibility 4) High complexity, heavy workload, and great challenge

Solution 3: pg\_dump

• advantages:

1) Good compatibility

2) Simple implementation and small workload

• Disadvantages:

1) Only applicable for full migration

2) Low efficiency 3) Long application shutdown time

RDS PG finally chose the pg\_upgrade scheme with less restrictions, good compatibility, high efficiency, and smooth cutover.

(3) Upgrade pre-check

The user needs to perform an upgrade pre-check before the upgrade. The check process can let the user know whether the instance can be upgraded and what problems the upgrade will have, and then the user can make corresponding modifications or adaptations based on the wrong information to make the upgrade complete successfully. The upgrade pre-check process is as follows:

*Upgrade pre-check flow chart

First, the user goes to the front-end console, selects the version of the target instance according to the version of the source-end instance, and then submits the upgrade pre-check process, and our back-end will create an upgrade check report. Then initialize the high version data directory selected by the user, and then generate the high version parameter template.

Then execute pg\_upgrade--check, and finally upload the inspection report to the console. The user can view the report in the RDS console. The following is a typical upgrade pre-check report.

*Upgrade pre-check result

As you can see, the report includes a lot of check items, and the result of the upgrade is clear at a glance, helping users to shield the upgrade risk before upgrading.

(4) Formal upgrade

After the upgrade pre-check is completed and correct, it enters the formal upgrade process. The flowchart is shown below.

As shown in the figure above, each step of the flowchart contains two roles, which are the source instance before the user upgrade and the target instance after the upgrade.

Before the upgrade, the user connects to the source instance through DNS. When the user initiates a major version upgrade on the console, we will help the user create a master node of the target instance of the same version as the source instance in the background, and build a replication link. Wait for the replication link to be built, and after all the data is synchronized, wait for the user's switching time. When the time is up, we will do Readonly on the source instance.

The fourth step is to disconnect the source instance and the target instance, and promote the target instance to the main library after the disconnection.

The fifth step is to perform the pg\_upgrade operation to upgrade the metadata, so the efficiency is very high, and then the user's DNS address is switched to the target instance, and the user application can read and write at this time.

Step 6: Rebuild the standby database, using the second-level snapshot capability, you can quickly build the standby database, and finally smoothly upgrade the entire instance to a higher version.

The entire upgrade process has the following key points:

• application non-stop service

1) Non-stop service: the user application is readable throughout

2) Smoothness: Step 5 is realized by exchanging connection addresses, user applications do not need to modify the code

• verifiable and rollback

1) Verifiable: non-cutover mode, zero intervention of the source instance

2) Can be rolled back: Before step 5, roll back at zero cost, and the connection address can be rolled back at any time

• high efficiency

1) Fast speed: Step 5 pg\_upgrade2T data can be upgraded in 10 seconds

2) Fast rebuild: second-level snapshot, about 10 minutes to rebuild the standby database, regardless of the amount of data

• User impact

1) Steps 3-5, only minute RO time

Summary: Application non-stop service, zero downtime, RO only in minutes.

(5) Application non-stop service and zero downtime

The upgrade process achieves zero downtime without stopping the application, mainly through the following four points.

1. Clone the target instance target instance uses the class clone instance scheme, and the source instance is always available.

2. Verifiable and rollback non-cutover mode provides verification capabilities, and can be rolled back before the connection address is switched.

DNS address switching switches the user to connect the DNS address to the target instance to avoid application changes.

pg\_upgrade metadata upgrade pg\_upgrade only metadata upgrade, the time-consuming has nothing to do with the amount of data, measured 2TB data, less than 10 seconds.

Through the above four points, the big version upgrade can be completed smoothly with one click.

4. PostgreSQL version upgrade results

(1) Display of results

Cloud RDS PG major version upgrade has achieved remarkable results in 160f7e920144eb coverage, availability, efficiency, verifiable and rollback capability

(2) Industry comparison

*Alibaba Cloud RDS PG one-click major version upgrade leads the industry in productization, user experience, verifiable and rollback capabilities

Copyright Notice: content of this article is contributed spontaneously by Alibaba Cloud real-name registered users, and the copyright belongs to the original author. The Alibaba Cloud Developer Community does not own its copyright and does not assume corresponding legal responsibilities. For specific rules, please refer to the "Alibaba Cloud Developer Community User Service Agreement" and the "Alibaba Cloud Developer Community Intellectual Property Protection Guidelines". If you find suspected plagiarism in this community, fill in the infringement complaint form to report it. Once verified, the community will immediately delete the suspected infringing content.