Brief introduction:Contents: 1. PostgreSQL industry position 2. PostgreSQL version upgrade background 3. PostgreSQL version upgrade decryption 4. PostgreSQL version upgrade results
I. PostgreSQL industry position
(1) Industry position
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 AliCloud database in the global database industry.
- Magic Quadrant Leader
*Gartner 2020, AliCloud database advances
Global Database Magic Quadrant Leader
- PG Product of the Year Award
* In the 2020 PG Asia Conference, MyBase, the exclusive cluster of Ali Cloud database, won the “PG Best Product of the Year Award”
Next, let’s take a look at where the PG database is in the industry.
- Global Database Ranking
*PostgreSQL has been ranked Top 2 in Open Source Database for 3 consecutive years and Top 4 in Global Popality Trend
- Widely used in the
*PG database is widely used in all walks of life, including: computer software, information technology and services,
Medical and health care, financial services, higher education, communications services, etc
(2) RDS PG VS Self-built PG
After a general understanding of the position of PG in the industry, let’s look at the advantages of Ali Cloud RDS PG compared with self-built PG.
* As shown in the figure above, the advantages of RDS PGs over self-built PGs are mainly reflected in
Reliability, security, intelligence and rich plug-in four aspects
1. The reliability
RDS PG provides the ability of Logical Slot Failover. In the primary and standby mode, the Logical Slot can continue to provide data synchronization for users after the HA switch of the instance, which solves the problem that the self-built PG cannot achieve data incremental synchronization during HA switch.
RDS PG Standby supports multiple upstream nodes. After HA switch, read-only instance read-write separation can still be maintained without affecting read-only node data synchronization.
One-click major upgrade allows our customers to productively upgrade to a higher version of PG with one click and enjoy the features and stability of the updated version of PG.
2. The security
Security is divided into three main aspects.
First of all, RDS PG provides cloud disk encryption function. Users only need to provide a Key, and RDS PG can use this user-defined Key to encrypt the data.
Secondly, we released the SSL custom certificate function, providing custom certificates on both the client and the server, providing anti-counterfeiting on both the client and the server to improve database security.
Finally, RDS PG provides SGX full encryption, which is a hardware-based encryption technology that enables data to be encrypted over the full link.
Intelligent 3.
DAS service is provided for the entire product line of Aliyun RDS. To help users to provide diagnostic optimization capabilities in the process of using the database, DAS can help users self-discovery, self-diagnosis, self-optimization, and self-decision-making to solve user database problems.
4. Plug-ins
RDS PG’s Ganos Temporal Engine plug-in provides the ability to store, retrieve, query, and analyze spatiotemporal data.
The second plug-in is the PASE Efficient Vector Retrieval plug-in.
The third plug-in is OSS \ _FDW, which can realize the scenario of separation of hot and cold data. The cold data can be stored on the cheaper OSS, and the data on the OSS can be searched 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.
PostgreSQL version upgrade background
The PostgreSQL upgrade feature is a result of some of the problems users have had with it, and we have faced a number of challenges with the upgrade.
1. Problems encountered
- Old version: Out of date and not maintained
Too low database versioning, stability challenges, such as:
1) PG 9.4, version too old 2) low version, supply chain problems 3) community not maintained, no one to take care of
- High version: new feature
Strong user demand for advanced, new features, such as:
1) Incremental sorting 2) Parallel index garbage collection 3) Index DeDuplicate capability 4) Enhanced partitioning table and aggregation performance
2. Challenges
- Elasticity: Extreme elasticity
The PG 9.4 and PG 10.0 local versions are based on physics, resulting in relatively low resiliency. For example:
1) Second-level snapshots 2) Elastic scalability 3) More storage support 4) No performance loss for backup operation
- Smooth cutover: low application awareness
In the process of one-click major version upgrade, how to make the user application as small and smooth as possible is another huge challenge, such as:
1) Ensure plug-in compatibility
3) Rollback and Verifiability 4) Zero change application, small perception 5) One-click big version upgrade capability
In summary, we expect the RDS PG to be productibly upgraded to one-click major releases, smoothing cutovers, and providing verifiable, rollback capabilities.
3, 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, rollback – version rollback: major version rollback -DNS address: connection string rollback – verifiable: high version verifiable ability
2. Less restrictions: full coverage of scenes -DDL restrictions – table structure restrictions – data type restrictions – full coverage of versions
3. One-click upgrade: One-click upgrade productization – Refuse to upgrade manual – One-click productization ability – plugin compatibility and adaptation
4 smooth cutover: application non-stop service zero downtime – upgrade process application non-stop service – upgrade process speed – smooth cutover connection address
The starting point of these four design principles is that we want to keep the complexity to ourselves and the simplicity to the user, and bring the ultimate experience to the user.
(2) Program selection
Based on the above design principles, we have to choose the upgrade plan. For large version upgrade of PG, there are mainly three solutions in the industry as follows.
Solution 1: logical replication
- Advantages:
Good compatibility, smooth cutover
- Disadvantages:
1) Library level publish and subscribe
2) Table must have PK/UK3) DDL unsupported, large objects 4) Foreign keys and triggers disabled 5) May cause log stack to WAL
Option 2: PG \_upgrade
- Advantages:
1) Do not copy data, only metadata upgrades
2) High efficiency, 2TB data, upgrade < 10s
- Disadvantages:
1) Pre-check the upgrade
2) Rollback validation strategy 3) parameters, plug-in compatibility 4) high complexity, heavy workload, and big challenges
Scheme 3: pg\_dump
- Advantages:
1) Good compatibility
2) The implementation is simple and the workload is small
- Disadvantages:
1) Only applicable to full migration
2) Low efficiency. 3) Long downtime of the app
RDS PG finally chooses PG \_upgrade solution with fewer restrictions, good compatibility, high efficiency and smooth cutover.
(3) Pre-inspection of upgrade
The user needs to pre-check the instance before upgrading. The inspection process can let the user know whether the instance can be upgraded and what problems will exist in the upgrade. Then the user can make corresponding modifications or adaptions according to the wrong information, so that the upgrade can be completed smoothly. The pre-inspection process for upgrade is as follows:
* Upgrade pre-check flow chart
First, the user goes to the front-end console, selects the version of the target instance based on the version of the source instance, then submits the upgrade pre-check process, and our backend will create an upgrade check report. It then initializes the higher-version data directory selected by the user, and then generates the higher-version parameter template.
Then perform pg\_upgrade–check and finally upload the check report to the console, where the user can view the report from the RDS console. Here is a typical upgrade pre-check report.
* Upgrade pre-check results
As you can see, the report includes a lot of check items, and the results of upgrade can be clear at a glance to help users shield upgrade risks before upgrading.
(4) Formal upgrade
After the pre-check of the upgrade is completed and there is no error, the formal upgrade process is entered. The flow chart is shown below.
As shown in the figure above, each step of the flowchart contains two roles, the source instance before the user upgrades and the target instance after the user upgrades.
Prior to the upgrade, the user connects to the source instance via DNS. When the user initiates a major update in the console, we will help the user create a master node of the target instance with the same version as the source instance in the background, and set up the replication link. When the replication link is set up and all data synchronization is completed, the switching time for the user is waiting. When the time is up, we’ll do readOnly on the source instance.
The fourth step is to disconnect the source instance from the target instance and then promote the target instance to the master library.
The fifth step is to pg\_upgrade the metadata, which is very efficient, and then cut the user’s DNS address to the target instance, at which point the user application can read and write.
Step 6: Rebuild the standby database. Using the second-level snapshot capability, you can quickly build the standby database, and finally smoothly upgrade the whole instance to a higher version.
The whole upgrade process has the following key points:
- Application non-stop service
1) Non-stop service: the user’s application can be read throughout
2) Smoothness: The fifth step is achieved through connection address swapping, and the user application does not need to modify the code
- Verifiable and rollback
1) Verifiable: non-cutover mode, zero intervention of source instance
2) Rollback: Before the fifth step, zero cost rollback, connection address can be rolled back at any time
- High efficiency
1) Fast speed: The pg\_upgrade2T data at step 5 can be upgraded within 10 seconds
2) Fast re-build: Second-level snapshot, about 10 minutes to re-build the database, has nothing to do with the size of the data
- The user impact
1) Step 3-5: only minute RO time
Summary: application non-stop service, zero downtime, only minute level RO.
(5) application non-stop service zero downtime
The process of upgrading the application non-stop service zero downtime, mainly through the following four.
1. Cloning the target instance The target instance adopts the clone-like instance scheme, and the source instance is always available.
2. Verifiable and rollback Non-cutover mode provides the ability to verify and rollback the connection address before switching.
Switches the user to connect the DNS address to the target instance to avoid applying changes.
PG \_upgrade is only metadata upgrade, the time is independent of the size of the data, the measured 2TB data, less than 10 seconds.
Through the above four points, the final key smooth completion of major version upgrade.
IV. PostgreSQL version upgrade results
(I) Achievement display
* AliCloud RDS PG large version upgrade has achieved remarkable results in coverage, availability, efficiency, verifiable and rollback capabilities.
(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:The content of this article is contributed by Aliyun real-name registered users, and the copyright belongs to the original author. Aliyun developer community does not own the copyright and does not bear the corresponding legal liability. For specific rules, please refer to User Service Agreement of Alibaba Cloud Developer Community and Guidance on Intellectual Property Protection of Alibaba Cloud Developer Community. If you find any suspected plagiarism in the community, fill in the infringement complaint form to report, once verified, the community will immediately delete the suspected infringing content.