PostgreSQL upgrade Background PostgreSQL upgrade Background PostgreSQL upgrade Decryption PostgreSQL upgrade results
PostgreSQL industry location
(I) Industry location
Before we discuss PostgreSQL’s position in the overall database industry, let’s take a look at ali Cloud database’s position in the global database industry.
- Magic Quadrant Leader
*Gartner 2020, Ali Cloud Database into the global database magic Quadrant leader
- PG Product of the Year award
*2020 PG Asia Conference, Aliyun database exclusive cluster MyBase won the “PG Product of the Year award”
Next, let’s take a look at where PG databases fit into the industry.
- Global Database Rankings
*PostgreSQL has been ranked TOP2 in open source for 3 consecutive years and TOP4 in global popularity trends
- Widely used in the
PG databases are used in a variety of industries, such as computer software, information technology and services, healthcare and health, financial services, higher education, communication services and more
(2) RDS PG VS self-built PG
After a general understanding of the position of PG in the industry, let’s take a look at the advantages of Aliyun RDS PG compared with self-built PG.
As shown in the figure above, compared with self-built PG, RDS PG has four main advantages: reliability, security, intelligence and rich plug-ins
1. The reliability
RDS PG provides Logical Slot Failover. In active/standby mode, Logical Slot Failover continues to synchronize data for users after an HA switchover occurs on instances. This solves the problem that self-built PGS cannot perform incremental data synchronization during an HA switchover.
The Standby RDS PG node supports multiple upstream nodes. After the HA switch, the read/write separation function of read-only instances is still maintained without affecting data synchronization of read-only nodes.
One-click major version upgrade enables our users to productize to a higher version of PG with one click and enjoy the features and stability of PG newer versions.
2. The security
There are three main aspects of security.
First, the RDS PG provides cloud disk encryption. Users only need to provide a Key, and the RDS PG can use the user-defined Key to encrypt data.
Second, we released the SSL custom certificate function, which provides customized certificates for clients and servers, provides client and server security, and improves database security.
Finally, RDS PG provides SGX full encryption, which is a hardware-based encryption technology that enables data to be encrypted across the full link.
Intelligent 3.
The entire product line of Ali Cloud RDS provides DAS services. DAS provides diagnostic and optimization capabilities for users in the process of using the database. DAS helps users self-discover, diagnose, optimize, and make decisions to solve problems in the database.
4. Enrich plug-ins
RDS PG’s Ganos space-time engine plug-in provides storage, retrieval, query, and analysis capabilities for spatio-temporal data.
The second plug-in is the PASE efficient vector retrieval plug-in.
The third plug-in is OSs_FDW, which implements hot and cold data separation and stores cold data on the lower-priced OSS. RDS PG enables query and analysis of data on OSS.
It can be found from the above that compared with self-built PG, RDS PG has obvious advantages in reliability, security, intelligence and plug-in richness.
2. PostgreSQL version Upgrade background
PostgreSQL has been upgraded due to a number of user issues, and we have faced many challenges in the upgrade process.
1. Problems encountered
- Old version: obsolete and not maintained
Too low a database version, stability challenges such as:
1) PG 9.4, version too old 2) low version, supply chain problems 3) community does not maintain, no one is at the bottom
- Advanced version: New feature
Strong user demand for higher releases and new features, such as:
1) Incremental sorting 2) garbage collection of parallel indexes 3) Index deDuplicate capability 4) Improved performance of partitioning tables and aggregation
2. Challenges
- Elasticity: Extreme elasticity
PG 9.4 and PG 10.0 will run on physical machines, resulting in relatively weak elasticity, such as:
1) Second-level snapshot 2) Elastic scaling 3) Larger storage space 4) No performance loss during backup operations
- Smooth cutover: Low application awareness
In the process of one-click large version upgrade, how to make the user application as small and smooth cutover as possible is another big challenge, such as:
1) Ensure compatibility of plug-ins
2) Cutover and non-cutover modes 3) rollback and verifiability 4) Zero application change and small perception 5) One-click upgrade capability of large version
In summary, we expect RDS PG to productize one-click large version upgrades, smooth cutover, and provide verifiable, rollback capabilities.
PostgreSQL upgrade decryption
(1) Design principles
Based on the above product thinking, we mainly follow the following four principles in the design process of RDS PG.
1. Verify rollback: verifiable and rollback – Version rollback: large version rollback -DNS address: connection string rollback – Verifiable: high version verifiable ability
2. Set the following restrictions: Scenario full coverage -DDL restriction – Table structure restriction – Data type restriction – Version full coverage
3. One-click Upgrade: One-click Upgrade – Reject upgrade manual – One-click Upgrade capability – Plug-in compatibility and adaptation
4. Smooth cutover: No application downtime – Upgrade process – Continuous application – Upgrade process speed – Smooth cutover of connection addresses
The starting point of these four design principles is that we want to keep the complexity for ourselves and the simplicity for the users, so as to bring the ultimate product experience to the users.
(2) Program selection
Based on the above design principles, we have to choose the upgrade plan. There are three major PG upgrade schemes in the industry.
Solution 1: Logical replication
- Advantages:
Good compatibility, smooth cutting
- Disadvantages:
1) Publish and subscribe at library level
2) Table must have PK/UK3) 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) Upgrade metadata instead of copying data
2) High efficiency, 2TB data, upgrade < 10s
- Disadvantages:
1) Pre-check the upgrade
2) Rollback verification policy 3) parameters and plug-in compatibility 4) High complexity, workload and challenge
Solution 3: PG_dump
- Advantages:
1) Good compatibility
2) Simple implementation and small workload
- Disadvantages:
1) Only full migration is applicable
2) Low efficiency 3) Long application downtime
RDS PG finally chooses the PG_upgrade scheme with fewer restrictions, good compatibility, high efficiency and smooth cutover.
(iii) Upgrade pre-check
Before upgrading an instance, users need to perform a pre-upgrade check. The check helps users know whether the instance can be upgraded and what problems may exist in the upgrade. Then, users can modify or adapt the instance based on the error information to complete the upgrade smoothly. The pre-check process is as follows:
* Upgrade pre-check flow chart
Firstly, the user goes to the front-end console, selects the version of the target instance according to the version of the source instance, and then submits the pre-check process of the upgrade. Our background will create an upgrade check report. Next, the higher-version data directory selected by the user is initialized and the higher-version parameter template is generated.
Then run pg_upgrade–check to upload the check report to the console. Users can view the report on the RDS console. The following is a typical upgrade pre-check report.
* Upgrade pre-check results
It can be seen that the report includes many check items, and the upgrade result is clear at a glance, which helps users shield upgrade risks before upgrading.
(4) Official upgrade
After the pre-check is complete, the official upgrade process starts. The flowchart is as follows.
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 upgrade.
Before the upgrade, users connect to the source instance through DNS. When the user initiates a large version upgrade on 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 a replication link. Wait until the replication link is set up and all data is synchronized. When the time is up, we will Readonly the source instance.
The fourth step is to disconnect the source instance from the target instance and promote the target instance to the master library.
The fifth step is to perform pg_upgrade to upgrade metadata, which is very efficient. Then, the DNS address of the user is cut to the target instance, and the user application can read and write.
Step 6 Re-build the standby database. With the second-level snapshot capability, the standby database can be quickly built and the whole instance can be smoothly upgraded to a higher version.
The upgrade process has the following key points:
- Application non-stop service
1) Non-stop service: users can read the entire application
2) Smoothness: The fifth step, through the connection address exchange to achieve, users do not need to modify the code
- Verifiable and rollback
1) Verifiable: non-cutover mode, source instance zero intervention
2) Rollback: Before step 5, the connection address can be rolled back at any time at zero cost
- High efficiency
1) Fast speed: step 5 pg_upgrade2T data can be upgraded within 10 seconds
2) Relap speed: second snapshot, about 10 minutes, regardless of the size of the data
- The user impact
1) Step 3-5, only minute level RO time
Summary: Application non-stop service, zero downtime, only minute RO.
(5) Application non-stop service zero downtime
During the upgrade process, the following four aspects are used to ensure the continuous service and zero downtime of the application.
1. Clone the target instance. The target instance uses the clone-like instance scheme, and the source instance is always available.
2. Verifiable and rollback In non-cutover mode, the authentication function can be rolled back before the connection address is switched.
3.DNS address switchover Switchover allows users to connect THE DNS address to the target instance to avoid application changes.
4. Pg_upgrade Metadata upgrade PG_upgrade is only metadata upgrade. The upgrade time has nothing to do with the data volume.
Through the above four points, the final one-click smooth completion of the major version upgrade.
4. PostgreSQL version upgrade
(1) Achievements display
* Ali Cloud RDS PG large version upgrade achieved significant results in coverage, availability, efficiency, verifiable rollback ability.
(2) Industry comparison
* Ali Cloud RDS PG one-click large version upgrade leads the industry in productization, user experience, verifiable rollback ability
The original link to this article is the original content of Ali Cloud, shall not be reproduced without permission.