Shulou(Shulou.com)06/01 Report--

What is PostgreSQL?

Cloud database PostgreSQL is a powerful enterprise-level relational database management system built by JD.com Cloud based on open source PostgreSQL version 10.6.

PostgreSQL is known as "the most advanced open source database available in the world", which has been widely used in GIS scenarios during the rapid development of the past 20 years. For example, mobile applications, logistics applications, financial data analysis and other industries have gradually become the first choice of business-grade databases for enterprises.

Cloud database can help you easily deploy, operate, manage and expand PostgreSQL in the cloud, eliminating most of the complex and tedious management work, and focusing more on the development and construction of business processes.

I. Product advantages

Compared with traditional relational databases, cloud database PostgreSQL has the following advantages:

1. Richness

Support a variety of programming languages to write stored procedures and functions, support mainstream development languages including C/C++/Perl/Python/Java/JavaScript/PHP, etc., support a wide range of data types, including geometric types, network address types, JSON types and so on. It also allows users to customize data types according to business needs, and can be flexibly configured and operated.

2. Flexibility

Provide graphical interface management tools and non-interface command line query tools, whether you want to control through the script, or through the console graphical tool operation, PostgreSQL can meet your needs. At the same time, PostgreSQL also provides a management tool that can integrate other databases, which can realize data connection and migration between multiple databases.

3. Professionalism

Support standard SQL query statements, support PostGIS spatial types, support GIS geometric data types, range types, IP types, can meet the geographic location-based spatial query, spatial index, spatial analysis.

II. Product architecture

Overall architecture diagram of cloud database PostgreSQL:

Service: business logic layer, responsible for handling some business logic

UC: user Center

Orde: order system

Trade: trading system

Sentinel: Sentinel service, monitoring instance survival and other duties

Manager: manage services, perform specific duties such as creating instances, deleting instances, etc.

Container: computing service, running cloud database PostgreSQL instance

SDN: SDN network service

OSS: Jingdong Cloud object storage service

JMonitor: monitoring service

III. Product characteristics

As a qualified data storage service, the core of cloud database PostgreSQL meets the high availability of service and data.

1. High availability

JD.com cloud database PostgreSQL uses the local data storage method, so to ensure the high availability of the service, the cloud database PostgreSQL service must be deployed in a cluster way.

The clustering mode of PostgreSQL can be one master and one slave, one master and two slaves, or one master and multiple slaves. PostgreSQL calls slaves: Hot Standby. JD.com Cloud Database PostgreSQL adopts "one master, one slave" mode, which ensures high availability of services while minimizing costs.

Like MySQL, PostgreSQL divides the master-slave synchronization mode into full synchronous, semi-synchronous and asynchronous, and chooses semi-synchronous mode considering the two factors of performance and data consistency.

Starting from version 9.0 of PostgreSQL, the master-slave data synchronization method (synchronizing WAL logs) supports the following two ways:

Asynchronous flow replication: after the transaction is committed, it can be returned without waiting for the log to be delivered to standby, and there is a certain delay from the instance data.

Synchronous flow replication: when the master instance transaction is committed, it will not return until the log is delivered to standby. Zero data loss can be achieved.

As the JD.com cloud database PostgreSQL cluster uses an one-master-one-slave architecture, if the data synchronization method uses synchronous stream replication, then when standby is not available, it will not be able to write data to the PostgreSQL cluster normally. Therefore, in order to ensure the high availability of the cluster, the master-slave data synchronization method adopted by JD.com Cloud Database PostgreSQL is asynchronous flow replication.

At the same time, JD.com Cloud Database PostgreSQL cluster is deployed across racks and across physical machines. If you select cross-availability zone when creating, the master and slave instances of the cluster can be deployed in two unused availability zones respectively. Under such a deployment architecture, there is a very low probability that the master and slave instances in the cluster will be unavailable at the same time.

Auto High availability-take a look at how JD.com Cloud Database PostgreSQL achieves automatic high availability:

JD.com 's cloud database PostgreSQL cluster includes two master and slave instances, namely Master and Slave in the figure above. Master and Slave will regularly report the heartbeat information to the Sentinel service to report their survival. When Slave does not report heartbeat information for a long time, Sentinel will think that Slave is no longer available, and Sentinel will trigger an automatic high-availability command to tell Manager that a new slave instance, that is, the Slave in the above figure, is needed. Slave is created based on a recent full backup, and when created successfully, a master-slave relationship with Master is set to synchronize incremental data from Master.

2. High reliability

JD.com Cloud Database PostgreSQL provides automatic backup service, and the instance is backed up regularly once a day. At the same time, all WAL* logs generated during this period are synchronized to Jingdong Cloud OSS service (the data in OSS is stored in three copies).

For the automatic backup of cloud database PostgreSQL, we use the official pg_baseBackup* command line tool to regularly backup all data on the slave instance and synchronize the data to the Jingdong Cloud OSS service in real time, ensuring that the backup operation will not affect the user's operation on the master instance.

In addition to regular full backups, we also synchronize the incremental data WAL logs to Jingdong Cloud OSS service in real time. Based on this mechanism, JD.com Cloud Database PostgreSQL allows you to create new instances based on the time point (any time point within 7 days from the current time).

Automatic full backup-take a look at how JD.com Cloud Database PostgreSQL achieves automatic full backup:

JD.com Cloud Database PostgreSQL supports customizing the execution time of daily full backups. In the process of full backup, in order not to affect normal business use, the full backup of JD.com cloud database PostgreSQL is based on Slave instances, and the backup files are uploaded to Jingdong Cloud OSS in real time.

IV. Application scenarios

Because of its strong data type support and excellent function expansion and compatibility, PostgreSQL is widely used in enterprise commercial databases and GIS scenarios. Here are a few simple scenarios.

1. Enterprise-level commercial database

Commercial databases require high availability and reliability, and their business logic is often complex and changeable, especially in the financial industry, such as trading system, financial system, which involves capital flow, customer information management, etc., which requires that data can not be lost and synchronized in real time. PostgreSQL can provide high availability and high reliability on the premise of consistent data, and flexible data type configuration can realize the business operation of complex logic through simple programming statements.

2. Application in logistics industry

The distribution and delivery algorithm of express delivery in the logistics industry are related to the limitation of express delivery or delivery, and are directly related to the operating cost of logistics companies. The consignment scenario such as consignment and delivery occurs at the same time how to schedule according to the location of the courier, and the logistics scheduling path problem of goods from the terminal warehouse to the network is based on the complex geometrical data relationship of geographic information. PostgreSOL supports GIS geometry types and PostGIS plug-ins to solve the shortest path problem based on location information.

3. Based on LBS application

The development of mobile Internet is inseparable from the storage, query and calculation of geographic location information. Application scenarios such as LBS-based applications such as large-scale games, need to support world maps, O2O accessories merchant distance calculation, order delivery and other issues, based on the user's mall location merchant recommendation and so on. PostgreSQL supports SQL running location query, which can be realized without writing complex code, which improves the efficiency of storage and calculation based on LBS.

Conclusion

Cloud database PostgreSQL is a product developed by Jingdong Cloud Database team for more than one year. This product combines many years of experience in using PostgreSQL by Jingdong Cloud Database team, and provides a stable and reliable data storage service.

Cloud database PostgreSQL is easy to deploy, manage and expand. By default, it supports master-slave hot backup architecture, and provides a full range of solutions such as data backup, fault recovery, monitoring, etc., to completely solve the problems of database operation and maintenance.

At the same time, JD.com Cloud Database PostgreSQL provides high-level security requirements for database services by using network isolation and whitelist to restrict public network access, helping you save most of the complex and tedious management work, and focus more on the development and construction of business processes.

Click "Jingdongyun" for more details

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.