What is PostgreSQL?
PostgreSQL is a powerful enterprise relational database management system built by Jingdong Cloud based on PostgreSQL version 10.6.
PostgreSQL has been described as “the most advanced open source database available in the world” and has been widely used in GIS scenarios over the past 20 years. For example: mobile applications, logistics applications, financial data analysis and other industries, gradually become the preferred business database.
The cloud database enables you to deploy, operate, manage, and extend PostgreSQL easily in the cloud, eliminating most of the complex management work and focusing on the development and construction of business processes.
First, product advantages
Cloud database PostgreSQL has the following advantages over traditional relational databases:
1. Richness
Support for multiple programming language stored procedures and functions, to support the mainstream development languages including C/C + + / Perl/Python/Java/JavaScript, PHP, etc., to support a wide range of data types including geometric type, type of network address, type a JSON. Users can customize data types based on service requirements, and flexibly configure and operate data types.
2. Flexibility
PostgreSQL provides graphical management tools and non-graphical command line query tools. Whether you want to use script control or console graphical tools, PostgreSQL can meet your needs. PostgreSQL also provides a management tool that integrates with other databases to connect and migrate data between multiple databases.
3. Professionalism
Supports standard SQL query statements, PostGIS spatial types, GIS geometric data types, range types, and IP types, and can meet the requirements of spatial query, spatial index, and spatial analysis based on geographical location.
Second, product architecture
Overall architecture of cloud database PostgreSQL:
Service: business logic layer, responsible for processing some business logic UC: user center Orde: order system Trade: transaction system Sentinel: Sentinel Service, monitoring instance survival and other responsibilities Manager: Container: computing services and running the cloud database PostgreSQL instance SDN: SDN network service OSS: JD cloud object storage service JMonitor: monitoring service
Iii. Product features
Cloud database PostgreSQL is a qualified data storage service, which mainly meets high service and data availability requirements.
1. High availability
Jingdong Cloud database PostgreSQL uses the data storage mode of the local site, so in order to ensure the high availability of the service, it is necessary to deploy the cloud database PostgreSQL service in a cluster.
PostgreSQL clusters can be one master, one master, two slaves, or one master with multiple slaves. PostgreSQL calls the slaves Hot Standby. Jingdong Yunyun database PostgreSQL adopts a master and slave mode, which ensures high availability of services and minimizes costs.
Like MySQL, PostgreSQL divides master/slave synchronization into full synchronization, semi-synchronization, and asynchronous. Considering performance and data consistency, PostgreSQL chooses semi-synchronization.
PostgreSQL 9.0 supports the following data synchronization modes (WAL log synchronization) :
Asynchronous stream replication: a transaction commits without waiting for the log to be sent to the standby. There is a delay in sending the data from the instance.
Synchronous stream replication: When a transaction is committed by the primary instance, it will not return until the log is sent to the standby to ensure zero data loss.
Jingdong Yunyun database PostgreSQL cluster uses one master and one slave architecture. If synchronous stream replication is used for data synchronization, data cannot be written to the PostgreSQL cluster when standby is unavailable. Therefore, to ensure the high availability of the cluster, jingdong Yunyun database PostgreSQL adopts the master/slave data synchronization mode: asynchronous stream replication.
PostgreSQL clusters are deployed across racks and physical machines. If cross-availability zones are selected, the primary and secondary instances of the cluster can be deployed in two different availability zones. In this deployment architecture, the probability of both primary and secondary instances in the cluster being unavailable is extremely low.
Automatic high availability – Take a look at how jingdong Yunyun database PostgreSQL implements automatic high availability:
Jingdong Cloud database PostgreSQL cluster contains two Master and Slave instances, namely Master and Slave in the figure above. The Master and Slave periodically report heartbeat information to the Sentinel service to report their survival status. If the Slave does not report heartbeat information for a long time, Sentinel considers that the Slave is no longer available. In this case, Sentinel triggers an automatic high availability command and tells Manager to create a new Slave instance, that is, the Slave shown in the figure above. The Slave is created based on the latest full backup. After the Slave is created successfully, the Slave and Master are set to synchronize incremental data from the Master.
2. High reliability
PostgreSQL provides an automatic backup service. Instances are backed up periodically every day. All WAL* logs generated during this period are synchronized to the OSS service (data in THE OSS is stored in three copies).
For automatic backup of cloud database PostgreSQL, we use the official pg_baseBackup* command line tool to periodically back up full data on the secondary instance and synchronize data to the OSS service of Jingdong cloud in real time, ensuring that the backup operation does not affect users’ operations on the primary instance.
In addition to regular full backup, we also synchronize incremental WAL logs to the JINGdong cloud OSS service in real time. Based on this mechanism, jd’s PostgreSQL database allows new instances to be created based on a point in time (any point in time within 7 days from the current time).
Automatic full backup – Take a look at how PostgreSQL implements automatic full backup:
Jingdong Yunyun database PostgreSQL supports user-defined execution time of full backup every day. In order not to affect normal services, the PostgreSQL full backup of jd Cloud database is based on Slave instances, and the backup files are uploaded to JD cloud OSS in real time.
4. Application scenarios
PostgreSQL is widely used in enterprise commercial database and GIS scenarios due to its powerful data type support and excellent function extension and compatibility. The following are some 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 transaction systems and financial systems, which involve capital flow and customer information management, etc., requiring that data cannot be lost and synchronized in real time. PostgreSQL provides high availability and reliability while ensuring consistent data. Flexible data type configuration enables complex logical service operations to be performed using simple programming statements.
2. Logistics industry application
The algorithm of express delivery and solicitation in logistics industry is related to the timeliness of express delivery or solicitation and directly related to the operation cost of logistics companies. How to dispatch according to the location of the Courier and how to dispatch the logistics route of goods from the terminal warehouse to the network are complex calculation based on the relationship of geographic information and geometric data. PostgreSOL supports GIS geometry types and PostGIS plug-ins to solve the problem of shortest paths based on location information.
3. Based on LBS applications
The development of mobile Internet is inseparable from the storage, query and calculation of geographic location information. Application scenarios include lBS-BASED applications such as large-scale games, the need to support the world map, O2O attachment distance calculation of merchants, order distribution and other issues, and the recommendation of merchants based on the geographical location of the user’s shopping mall. PostgreSQL supports SQL location query without writing complex code, which improves storage and computing efficiency based on LBS.
conclusion
PostgreSQL cloud database is a product developed by the Jingdong Cloud database team for more than one year. This product embodies the jingdong cloud database team’s years of experience in using PostgreSQL, providing a stable and reliable data storage service. The PostgreSQL cloud database is easy to deploy, manage, and expand. By default, PostgreSQL supports the active/standby hot backup architecture and provides a full range of solutions, including data backup, fault recovery, and monitoring, eliminating database o&M problems.
At the same time, jingdong cloud database PostgreSQL provides high-level security requirements for database services by using network isolation and whitelist to restrict access to external networks, helping you save most of the complex and tedious management work, and focusing more on the development and construction of business processes.
Click “Jingdong Cloud” for more details
