Backup methods, devices, computer equipment, and storage media for distributed databases

Abstract

This application relates to the field of database backup technology, specifically disclosing a method, apparatus, computer device, and storage medium for backup of a distributed database. This application pre-builds a Citus cluster to be written to on the backup end during database backup, solving the problem of missing metadata when performing logical backups of CN and DN nodes separately. Incremental data synchronization is performed through the starting point of incremental data synchronization, enabling eventual consistency of data from multiple nodes on the source end, thus improving the reliability of database backup. Synchronization relationships and sharding instructions can achieve backups between instances of unequal specifications, improving the flexibility of distributed database backup.

Description

Technical Field

[0001] This application relates to the field of database backup technology, and in particular to a method, apparatus, computer equipment, and storage medium for backing up a distributed database. Background Technology

[0002] Citus is a distributed middleware for the PostgreSQL database. It extends PostgreSQL capabilities as an extension without intruding on the PostgreSQL kernel code, enabling horizontal scaling of PostgreSQL. Citus mainly consists of a Coordinator Node (CN) and Worker Nodes (WN or DN). Each node is a single or a group of PostgreSQL instances forming a master-slave relationship at the underlying level. After using Citus to extend multiple underlying PostgreSQL instances into a distributed database, current backup techniques include logical backup and physical backup. However, the following problems exist: 1. Logical backup: Following the logical synchronization method of PostgreSQL, the CN and DN nodes are logically backed up separately, with each node's data handled by an independent PostgreSQL instance. Since the metadata of the Citus cluster is stored in a system schema called pg_catalog, which is maintained by PostgreSQL itself and is read-only by default, this metadata cannot be written through logical backup. Therefore, from the cluster's perspective, the backed-up instances will lack Citus metadata. 2. During logical backups, existing data can be extracted from the CN node, while real-time incremental data needs to be obtained by parsing the Write-Ahead Log (WAL) of the DN node. This raises the question of how to coordinate and maintain data consistency between the two during backups. 3. Physical backups, using tools like pg_basebackup to physically back up the CN and DN nodes separately, have the drawback of only allowing for backups of equal size. If the Citus cluster undergoes horizontal scaling after backup, the previously backed-up data cannot be directly used. Therefore, improving the flexibility and reliability of distributed database backups has become a pressing issue. Summary of the Invention

[0003] This application provides a method, apparatus, computer device, and storage medium for backing up distributed databases, so as to improve the flexibility and reliability of distributed database backup.

[0004] Firstly, this application provides a method for backing up a distributed database, the method comprising:

[0005] Build a Citus cluster to be written on the backup end based on a PostgreSQL instance;

[0006] Deploy a database synchronization agent based on the Citus cluster to be written;

[0007] Full data synchronization is performed based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent.

[0008] When full data synchronization is complete, incremental data synchronization is performed based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup.

[0009] Furthermore, before building the Citus cluster to be written on the backup end based on the PostgreSQL instance, the process also includes:

[0010] Based on the first custom script and the preset installation package, create at least one of the PostgreSQL instances;

[0011] Based on a second custom script, at least one of the PostgreSQL instances is connected.

[0012] Furthermore, the construction of the Citus cluster to be written on the backup end based on the PostgreSQL instance includes:

[0013] Based on the PostgreSQL instance, obtain the IP and port information of the DN node to be written to the Citus cluster;

[0014] Based on the first instruction and the IP and port information, socket information is specified for the DN node to be written to the Citus cluster, and the Citus cluster to be written is constructed.

[0015] Furthermore, before performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the process also includes:

[0016] Based on the node information of the Citus cluster to be backed up at the source end and the node information of the Citus cluster to be written at the backup end, the database synchronization agent is configured to obtain the synchronization relationship.

[0017] Furthermore, before performing incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup after full data synchronization is completed, the process further includes:

[0018] The database synchronization agent executes a second instruction to query and obtain the latest position of the current write-ahead log of the DN node of the Citus cluster to be backed up, and uses the latest position as the starting point.

[0019] Furthermore, before performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the process also includes:

[0020] The database synchronization agent executes a third instruction to query and obtain the table structure of the table to be synchronized.

[0021] Based on the table structure of the table to be synchronized, the sharding rules of the table to be synchronized are obtained, and the sharding instructions are generated based on the sharding rules.

[0022] Furthermore, the full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent includes:

[0023] Based on the database synchronization agent, read the full data from the CN node and DN node of the Citus cluster to be backed up, and write the full data into the CN node of the Citus cluster to be backed up.

[0024] Based on the sharding instructions and the CN nodes of the Citus cluster to be written, the Citus cluster to be written is sharded to complete the full data synchronization.

[0025] Secondly, this application also provides a backup device for a distributed database, the device comprising:

[0026] The Citus cluster build module to be written is used to build the Citus cluster to be written on the backup end based on the PostgreSQL instance;

[0027] The data synchronization agent deployment module is used to deploy a database synchronization agent based on the Citus cluster to be written;

[0028] The full data synchronization module is used to perform full data synchronization based on the Citus cluster to be backed up, the sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent.

[0029] The incremental data synchronization module is used to perform incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship when the full data synchronization is completed, so as to complete the database backup.

[0030] Thirdly, this application also provides a computer device, the computer device including a memory and a processor; the memory is used to store a computer program; the processor is used to execute the computer program and, when executing the computer program, implement the distributed database backup method as described above.

[0031] Fourthly, this application also provides a computer-readable storage medium storing a computer program that, when executed by a processor, causes the processor to implement the distributed database backup method described above.

[0032] This application discloses a method, apparatus, computer device, and storage medium for backing up a distributed database. It involves constructing a Citus cluster to be written on the backup end based on a PostgreSQL instance; deploying a database synchronization agent based on the Citus cluster to be written; performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent; and performing incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup. This application solves the problem of missing metadata when performing logical backups of CN and DN nodes separately by pre-constructing the Citus cluster to be written on the backup end. Incremental data synchronization based on the starting point of incremental data synchronization enables eventual consistency of data from multiple nodes on the source end, improving the reliability of the database backup. The synchronization relationship and sharding instructions enable backups between instances of unequal specifications, improving the flexibility of distributed database backup. Attached Figure Description

[0033] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0034] Figure 1 This is a schematic flowchart of a first embodiment of a distributed database backup method provided by the present application;

[0035] Figure 2 This is an implementation architecture diagram of a distributed database backup method provided in an embodiment of this application;

[0036] Figure 3 This is a schematic diagram of the aggregation link of a distributed database backup method provided in an embodiment of this application;

[0037] Figure 4 This is a schematic flowchart of a second embodiment of a distributed database backup method provided by the embodiments of this application;

[0038] Figure 5 This is a schematic flowchart of a third embodiment of a distributed database backup method provided by the embodiments of this application;

[0039] Figure 6 A schematic block diagram of a distributed database backup device provided for embodiments of this application;

[0040] Figure 7 A schematic block diagram of the structure of a computer device provided for an embodiment of this application. Detailed Implementation

[0041] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0042] The flowchart shown in the attached diagram is for illustrative purposes only and does not necessarily include all content and operations / steps, nor does it necessarily have to be performed in the order described. For example, some operations / steps can be broken down, combined, or partially merged, so the actual execution order may change depending on the actual situation.

[0043] It should be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of the application. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.

[0044] It should also be understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0045] This application provides a method, apparatus, computer device, and storage medium for backing up a distributed database. The method can be applied to a server to back up a distributed database, improving the flexibility and reliability of distributed database backup. The server can be a standalone server or a server cluster.

[0046] The following detailed description of some embodiments of this application is provided in conjunction with the accompanying drawings. Unless otherwise specified, the following embodiments and features can be combined with each other.

[0047] Please see Figure 1 , Figure 1 This is a schematic flowchart illustrating a distributed database backup method provided in an embodiment of this application. This distributed database backup method can be applied to servers to back up distributed databases, improving the flexibility and reliability of distributed database backups.

[0048] like Figure 1 As shown, the backup method for this distributed database specifically includes steps S101 to S104.

[0049] S101. Build a Citus cluster to be written on the backup end based on a PostgreSQL instance.

[0050] Furthermore, before building the Citus cluster to be written on the backup end based on the PostgreSQL instance, the process further includes: creating at least one PostgreSQL instance based on a first custom script and a preset installation package; and connecting to at least one PostgreSQL instance based on a second custom script.

[0051] In one embodiment, such as Figure 2 As shown, Figure 2 This document presents an implementation architecture diagram of a distributed database backup method provided in an embodiment of this application. It consists of the following parts:

[0052] 1. Management and Control Platform: Includes modules such as agent management, topology management, backup scheduling, and statistical services. It receives status reports from database synchronization agents and has a front-end interface that allows interaction with users.

[0053] 2. Database synchronization agent: The program that actually performs data extraction, data management, and data storage, and periodically reports its status to the management platform and parses the issued instructions.

[0054] 3. Database cluster: The database cluster to be backed up.

[0055] In one embodiment, for example, the method of this patent is used in a disaster recovery platform to back up a 3-node Citus cluster, including one CN (Coordinator Node) and two DN (Worker Node) nodes. The IP address and port of the CN node are 172.16.0.10:5432, and the IP addresses and ports of the two DN nodes are 172.16.0.20:5432 and 172.16.0.30:5432, respectively.

[0056] In one embodiment, backup PostgreSQL instances are created by installing several PostgreSQL instances based on an open-source PostgreSQL binary installation package using a custom script executed on the server. The IP address and port of the installed CN node underlying PostgreSQL instance are 192.168.0.120:5432, and the IP addresses and ports of the two DN node underlying PostgreSQL instances are 192.168.0.130:5432 and 192.168.0.140:5432, respectively.

[0057] In one embodiment, a Citus cluster and metadata are created. This is achieved by executing a custom script on the server to connect to the database instance created in the preceding steps. The SQL statement `create database backup_db` is used; within `backup_db`, the SQL statement `create extension citus` is executed; then, the SQL statement `insert into pg_dist_authinfo values(0,'root','password=kd^tGd6672Ko@s')` is executed; authentication information for inter-node communication is added. On the CN node, the SQL statements `select * from citus_add_node('192.168.0.130',5432)` and `select * from citus_add_node('192.168.0.140',5432)` are executed; specifying the Socket information of the instance where the DN resides, the Citus cluster is constructed. Here, a Socket is a network communication socket (interface).

[0058] S102. Deploy a database synchronization agent based on the Citus cluster to be written.

[0059] In one embodiment, a database synchronization agent is deployed. An equal number of database synchronization agents are deployed as the number of Citus cluster nodes; the agent's function is to perform the actual data extraction and writing.

[0060] In a specific embodiment, three database synchronization agents are deployed. The agents are responsible for performing actual data extraction and writing, and each agent corresponds to a node in the Citus cluster.

[0061] S103. Perform full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent.

[0062] Furthermore, before performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the process further includes: configuring the database synchronization agent based on the node information of the Citus cluster to be backed up at the source end and the node information of the Citus cluster to be written at the backup end, and obtaining the synchronization relationship.

[0063] In one embodiment, such as Figure 3 As shown, Figure 3 This is a schematic diagram of the aggregation link of a distributed database backup method provided in an embodiment of this application. The database synchronization agent is configured such that the CN / DN nodes at the source end correspond to the CN nodes of the backup end cluster, and connection and synchronization assistance information is distributed.

[0064] In a specific embodiment, the database synchronization agent is configured based on the node information of the Citus cluster to be backed up at the source end (i.e., the IP and port information of the CN / DN nodes at the source end) and the node information of the Citus cluster to be written to at the backup end (i.e., the IP and port information of the CN nodes at the backup end). The CN / DN nodes at the source end all correspond to the CN nodes of the backup end cluster, and the synchronization relationship is distributed as follows:

[0065] 172.16.0.10:5432->192.168.0.120:5432,

[0066] 172.16.0.20:5432->192.168.0.120:5432,

[0067] 172.16.0.30:5432->192.168.0.120:5432.

[0068] Furthermore, the full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent includes: reading the full data in the CN nodes and DN nodes of the Citus cluster to be backed up based on the database synchronization agent, and writing the full data into the CN nodes of the Citus cluster to be written; and sharding the Citus cluster to be written based on the sharding instructions and the CN nodes of the Citus cluster to be written, to complete the full data synchronization.

[0069] In one embodiment, the database agent responsible for synchronizing the CN node performs full data synchronization, while all database agents responsible for incrementally synchronizing the DN node wait for the CN node database synchronization agent to complete the full synchronization signal.

[0070] S104. When full data synchronization is completed, incremental data synchronization is performed based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup.

[0071] Furthermore, before performing incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup when the full data synchronization is completed, the method further includes: executing a second instruction based on the database synchronization agent to query and obtain the latest position of the current write-ahead log of the DN node of the Citus cluster to be backed up, and using the latest position as the starting point.

[0072] In one embodiment, all database agents responsible for synchronizing DN nodes execute `select pg_current_wal_lsn()` to query and obtain the latest position of the current WAL (Write Ahead Log), preset the starting point of incremental synchronization to this position, and record it locally. DN1 records position 7 / 9CDB6260, and DN2 records position 1 / 1A3B6440.

[0073] In one embodiment, after the full synchronization of the CN node is completed, the CN node database synchronization agent continues to perform incremental synchronization while notifying all database agents responsible for synchronizing the DN node to read the preset position information stored locally in the previous steps. The database synchronization agent of the DN1 node starts incremental synchronization from the preset position 7 / 9CDB6260, and the database synchronization agent of the DN2 node starts incremental synchronization from the preset position 1 / 1A3B6440. After the incremental synchronization is caught up, the data of the backup instance can be regarded as a mirror of the source.

[0074] The above embodiments provide a method, apparatus, computer device, and storage medium for backing up a distributed database. A Citus cluster to be written is constructed on the backup end based on a PostgreSQL instance. A database synchronization agent is deployed based on the Citus cluster to be written. Full data synchronization is performed based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent. Upon completion of full data synchronization, incremental data synchronization is performed based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup. This application pre-constructs the Citus cluster to be written on the backup end during database backup, solving the problem of missing metadata when performing logical backups of CN nodes and DN nodes separately. Incremental data synchronization is performed based on the starting point of incremental data synchronization, enabling eventual consistency of data from multiple nodes on the source end, thus improving the reliability of database backup. The synchronization relationship and sharding instructions enable backups between instances of unequal specifications, improving the flexibility of distributed database backup.

[0075] Please see Figure 4 , Figure 4 This is a schematic flowchart illustrating a distributed database backup method provided in an embodiment of this application. This distributed database backup method can be applied to servers to back up distributed databases, improving the flexibility and reliability of distributed database backups.

[0076] like Figure 4 As shown, the backup method for this distributed database specifically includes steps S201 to S202.

[0077] S201. Obtain the IP and port information of the DN node to be written to the Citus cluster based on the PostgreSQL instance.

[0078] S202. Based on the first instruction and the IP and port information, specify socket information for the DN node to be written to the Citus cluster, and construct the Citus cluster to be written.

[0079] In one embodiment, a Citus cluster and metadata are created. A custom script is executed on the server to connect to the database instances created in the previous steps and create the database to be backed up. In each database, the `create extension citus` command is executed; then, the `pg_dist_authinfo` statement is executed to add authentication information for inter-node communication. On the CN node, the `citus_add_node` statement is executed to specify the socket information of the instance where the DN resides, thus building the Citus cluster.

[0080] In a specific embodiment, a custom script is executed on the server to connect to the database instance created in the previous steps. The SQL statement `create database backup_db` is used; within `backup_db`, `create extension citus` is executed; then, the SQL statement `insert into pg_dist_authinfo values(0,'root','password=kd^tGd6672Ko@s')` is executed; authentication information for inter-node communication is added. On the CN node, the SQL statements `select * from citus_add_node('192.168.0.130',5432)` and `select * from citus_add_node('192.168.0.140',5432)` are executed; specifying the Socket information of the instance where the DN resides, a Citus cluster is constructed. Here, a Socket is a network communication socket (interface).

[0081] The distributed database backup method provided in the above embodiments improves the reliability of distributed database backup by directly creating a Citus cluster without business data on the backup end. Subsequent metadata changes generated by logical synchronization writing to the Citus cluster on the backup end can be maintained by Citus itself.

[0082] Please see Figure 5 , Figure 5 This is a schematic flowchart illustrating a distributed database backup method provided in an embodiment of this application. This distributed database backup method can be applied to servers to back up distributed databases, improving the flexibility and reliability of distributed database backups.

[0083] like Figure 5 As shown, before step S103, the backup method for this distributed database specifically includes steps S301 to S302.

[0084] S301. Execute a third instruction based on the database synchronization agent to query and obtain the table structure of the table to be synchronized.

[0085] S302. Based on the table structure of the table to be synchronized, obtain the sharding rules of the table to be synchronized and generate the sharding instruction based on the sharding rules.

[0086] In one embodiment, table structure migration: The database synchronization agent executes an SQL query on the source end to obtain the table structure to be synchronized, uses JDCB (Java Database Connectivity) as an intermediate bridge, creates the same table structure on the target end, then queries the sharding rules of the source table, assembles the sharding SQL according to the sharding rules, and sends the sharding instruction to the backup CN node.

[0087] In a specific embodiment, the database synchronization agent executes the SQL statement at the source end: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'p' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'p' THEN 'PARTITIONED TABLE' WHEN 'i' THEN 'INDEX' WHEN 'P' then 'PARTITIONED INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN'm' THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description ASREMARKS,"as TYPE_CAT,"as TYPE_SCHEM,"as TYPE_NAME,"AS SELF_REFERENCING_COL_NAME, "AS REF_GENERATION FROM pg_catalog.pg_namespace n,pg_catalog.pg_classc LEFT JOIN pg_catalog.pg_description d ON(c.oid=d.objoid AND d.objsubid=0and d.classoid='pg_class'::regclass)WHERE c.relnamespace=n.oid;.

[0088] Obtain the list of tables, and for a specific table, such as the partner table under schema public, execute the following SQL statement:

[0089] SELECT*FROM(SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnul l OR(t.typtype='d'AND t.typnotnul l)AS attnotnul l,a.atttypmod,a.attlen,t.typtypmod,a.attnum,nul l as attidentity,nul las attgenerated,pg_catalog.pg_get_expr(def.adbin,def.adrel id)AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON(c.relnamespace=n.oid)JOIN pg_catalog.pg_attribute a ON(a.attrelid=c.oid)JOIN pg_catalog.pg_type t ON(a.atttypid=t.oid)LEFT JOIN pg_catalog.pg_attrdef def ON(a.attrel id=def.adrel id AND a.attnum=def.adnum)LEFT JOIN pg_catalog.pg_description dsc ON(c.oid=dsc.objoid AND a.attnum=dsc.objsubid) LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoidANDdc.relname='pg_class')LEFT JOIN pg_catalog.pg_namespace dn ON(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')WHERE c.relkind in('r','p','v','f','m')and a.attnum>0AND NOTa.attisdropped AND n.nspname LIKE'publ ic'AND c.relname LIKE'partner')c;

[0090] The query retrieves the table structure to be synchronized. Using JDBC as an intermediary, the same table structure is created on the target side, and then the SQL statement is executed.

[0091] SELECT * FROM citus_tables;

[0092] Query the sharding rules of the source table, assemble the sharding SQL according to the sharding rules, and send the sharding SQL command to the backup CN node:

[0093] SELECT create_distributed_table('partner','id',shard_count=>4).

[0094] The distributed database backup method provided in the above embodiments obtains data from the source CN / DN nodes and writes it to the CN node of the backup instance. The sharding is performed internally by the backup Citus cluster, which can realize the backup of instances of different specifications and improve the flexibility of distributed database backup.

[0095] Please see Figure 6 , Figure 6 This is a schematic block diagram of a distributed database backup device provided in an embodiment of this application. The distributed database backup device is used to execute the aforementioned distributed database backup method. The distributed database backup device can be configured on a server.

[0096] like Figure 6 As shown, the backup device 400 for the distributed database includes:

[0097] The Citus cluster building module 401 is used to build the Citus cluster to be written on the backup end based on the PostgreSQL instance.

[0098] The data synchronization agent deployment module 402 is used to deploy a database synchronization agent based on the Citus cluster to be written;

[0099] The full data synchronization module 403 is used to perform full data synchronization based on the Citus cluster to be backed up, the sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent.

[0100] The incremental data synchronization module 404 is used to perform incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship when the full data synchronization is completed, so as to complete the database backup.

[0101] Furthermore, the distributed database backup device 400 further includes: a PostgreSQL instance creation module, the PostgreSQL instance creation module comprising:

[0102] A PostgreSQL instance creation unit is used to create at least one PostgreSQL instance based on a first custom script and a preset installation package.

[0103] A PostgreSQL instance connection unit is used to connect to at least one of the PostgreSQL instances based on a second custom script.

[0104] Furthermore, the Citus cluster building module 401 to be written includes:

[0105] The IP and port information acquisition unit is used to obtain the IP and port information of the DN node to be written to the Citus cluster based on the PostgreSQL instance;

[0106] The Citus cluster construction unit is used to specify socket information for the DN node of the Citus cluster to be written based on the first instruction and the IP and port information, and to construct the Citus cluster to be written.

[0107] Furthermore, the backup device 400 for the distributed database also includes:

[0108] The synchronization relationship acquisition module is used to configure the database synchronization agent based on the node information of the Citus cluster to be backed up at the source end and the node information of the Citus cluster to be written at the backup end, and to obtain the synchronization relationship.

[0109] Furthermore, the backup device 400 for the distributed database also includes:

[0110] The starting point acquisition module is used to execute a second instruction based on the database synchronization agent to query and obtain the latest position of the current write-ahead log of the DN node of the Citus cluster to be backed up, and use the latest position as the starting point.

[0111] Furthermore, the distributed database backup device 400 further includes: a sharding instruction acquisition module, the sharding instruction acquisition module comprising:

[0112] The table structure query and acquisition unit is used to execute a third instruction based on the database synchronization agent to query and obtain the table structure of the table to be synchronized;

[0113] The sharding instruction generation unit is used to obtain the sharding rules of the table to be synchronized based on the table structure of the table to be synchronized, and generate the sharding instruction based on the sharding rules.

[0114] Furthermore, the full data synchronization module 403 includes:

[0115] The full data writing unit is used to read the full data in the CN node and DN node of the Citus cluster to be backed up based on the database synchronization agent, and write the full data into the CN node of the Citus cluster to be written.

[0116] The database sharding unit is used to shard the Citus cluster to be written based on the sharding instructions and the CN nodes of the Citus cluster to be written, so as to complete the full data synchronization.

[0117] It should be noted that those skilled in the art will understand that, for the sake of convenience and brevity, the specific working processes of the above-described apparatus and modules can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here.

[0118] The aforementioned device can be implemented as a computer program, which can be used in, for example... Figure 7 It runs on the computer device shown.

[0119] Please see Figure 7 , Figure 7 This is a schematic block diagram illustrating the structure of a computer device according to an embodiment of this application. The computer device may be a server.

[0120] See Figure 7 The computer device includes a processor, memory, and network interface connected via a system bus, wherein the memory may include non-volatile storage media and internal memory.

[0121] Non-volatile storage media can store operating systems and computer programs. These computer programs include program instructions that, when executed, cause the processor to perform any distributed database backup method.

[0122] The processor provides computing and control capabilities, supporting the operation of the entire computer device.

[0123] Internal memory provides an environment for the execution of computer programs stored in non-volatile storage media. When these computer programs are executed by a processor, the processor can perform any method of backup for a distributed database.

[0124] This network interface is used for network communication, such as sending assigned tasks. Those skilled in the art will understand that... Figure 7The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0125] It should be understood that the processor can be a Central Processing Unit (CPU), but it can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. Among these, a general-purpose processor can be a microprocessor or any conventional processor.

[0126] In one embodiment, the processor is configured to run a computer program stored in memory to perform the following steps:

[0127] Build a Citus cluster to be written on the backup end based on a PostgreSQL instance;

[0128] Deploy a database synchronization agent based on the Citus cluster to be written;

[0129] Full data synchronization is performed based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent.

[0130] When full data synchronization is complete, incremental data synchronization is performed based on the starting point of incremental data synchronization and the synchronization relationship to complete the database backup.

[0131] In one embodiment, before implementing the construction of the Citus cluster to be written on the backup end based on the PostgreSQL instance, the processor is also configured to implement:

[0132] Based on the first custom script and the preset installation package, create at least one of the PostgreSQL instances;

[0133] Based on a second custom script, at least one of the PostgreSQL instances is connected.

[0134] In one embodiment, the processor, when implementing the construction of a Citus cluster to be written on the backup end based on a PostgreSQL instance, is used to:

[0135] Based on the PostgreSQL instance, obtain the IP and port information of the DN node to be written to the Citus cluster;

[0136] Based on the first instruction and the IP and port information, socket information is specified for the DN node to be written to the Citus cluster, and the Citus cluster to be written is constructed.

[0137] In one embodiment, before implementing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the processor is further configured to implement:

[0138] Based on the node information of the Citus cluster to be backed up at the source end and the node information of the Citus cluster to be written at the backup end, the database synchronization agent is configured to obtain the synchronization relationship.

[0139] In one embodiment, before the processor performs incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship after full data synchronization is completed to complete the database backup, it is also configured to:

[0140] The database synchronization agent executes a second instruction to query and obtain the latest position of the current write-ahead log of the DN node of the Citus cluster to be backed up, and uses the latest position as the starting point.

[0141] In one embodiment, before implementing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the processor is further configured to implement:

[0142] The database synchronization agent executes a third instruction to query and obtain the table structure of the table to be synchronized.

[0143] Based on the table structure of the table to be synchronized, the sharding rules of the table to be synchronized are obtained, and the sharding instructions are generated based on the sharding rules.

[0144] In one embodiment, when the processor performs full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, it is configured to:

[0145] Based on the database synchronization agent, read the full data from the CN node and DN node of the Citus cluster to be backed up, and write the full data into the CN node of the Citus cluster to be backed up.

[0146] Based on the sharding instructions and the CN nodes of the Citus cluster to be written, the Citus cluster to be written is sharded to complete the full data synchronization.

[0147] The embodiments of this application also provide a computer-readable storage medium storing a computer program, the computer program including program instructions, and the processor executing the program instructions to implement any of the distributed database backup methods provided in the embodiments of this application.

[0148] The computer-readable storage medium may be an internal storage unit of the computer device described in the foregoing embodiments, such as the hard disk or memory of the computer device. The computer-readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, SmartMedia Card (SMC), Secure Digital (SD) card, or Flash Card equipped on the computer device.

[0149] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in this application, and these modifications or substitutions should all be covered within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

Claims

1. A method for backing up a distributed database, characterized in that, include: A Citus cluster to be written is built on the backup side based on a PostgreSQL instance. The Citus cluster to be written includes a coordinating node (CN) and worker nodes (DN). The construction process includes: A PostgreSQL instance is created using a first custom script, and the instance is connected to a Citus cluster using a second custom script. The script executes `CREATE EXTENSION citus` and configures inter-node communication authentication information. Based on the Citus cluster to be written, a database synchronization agent is deployed. The agent corresponds one-to-one with the Citus cluster nodes and is used to perform data extraction and writing. Full data synchronization is performed based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent. The sharding instructions are generated by querying the table structure and sharding rules of the table to be synchronized through the database synchronization agent. Based on the sharding instructions and the CN nodes of the Citus cluster to be written, the Citus cluster to be written is sharded to complete the full data synchronization. Upon completion of full data synchronization, incremental data synchronization is performed based on the starting point of incremental data synchronization and the aforementioned synchronization relationship to complete the database backup. The starting point is determined through the following steps: Query the latest position of the current write-ahead log (WAL) of the DN node of the Citus cluster to be backed up, and record it as the starting point for incremental synchronization; The synchronization relationship is achieved by configuring a database synchronization agent, which maps the CN and DN nodes of the Citus cluster to be backed up to the CN nodes of the Citus cluster to be written.

2. The backup method for a distributed database according to claim 1, characterized in that, Before building the Citus cluster to be written on the backup end based on the PostgreSQL instance, the process also includes: Based on the first custom script and the preset installation package, create at least one of the PostgreSQL instances; Based on a second custom script, at least one of the PostgreSQL instances is connected.

3. The backup method for a distributed database according to claim 1, characterized in that, The process of building a Citus cluster to be written on the backup end based on a PostgreSQL instance includes: Based on the PostgreSQL instance, obtain the IP and port information of the DN node to be written to the Citus cluster; Based on the first instruction and the IP and port information, socket information is specified for the DN node to be written to the Citus cluster, and the Citus cluster to be written is constructed.

4. The backup method for a distributed database according to claim 1, characterized in that, Before performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the following steps are also included: Based on the node information of the Citus cluster to be backed up on the source end and the node information of the Citus cluster to be written on the backup end, the database synchronization agent is configured to obtain the synchronization relationship.

5. The backup method for a distributed database according to claim 1, characterized in that, Before performing incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship after full data synchronization is completed, to complete the database backup, the process also includes: The database synchronization agent executes a second instruction to query and obtain the latest position of the current write-ahead log of the DN node of the Citus cluster to be backed up, and uses the latest position as the starting point.

6. The backup method for a distributed database according to claim 1, characterized in that, Before performing full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent, the following steps are also included: The database synchronization agent executes a third instruction to query and obtain the table structure of the table to be synchronized. Based on the table structure of the table to be synchronized, the sharding rules of the table to be synchronized are obtained, and the sharding instructions are generated based on the sharding rules.

7. The method for backing up a distributed database according to any one of claims 1 to 6, characterized in that, The full data synchronization based on the Citus cluster to be backed up, sharding instructions, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent includes: Based on the database synchronization agent, the full data in the CN node and DN node of the Citus cluster to be backed up is read and written to the CN node of the Citus cluster to be backed up.

8. A backup device for a distributed database, characterized in that, include: The Citus cluster to be written module is used to build the Citus cluster to be written on the backup end based on the PostgreSQL instance. The Citus cluster to be written includes a coordinating node (CN) and worker nodes (DN). The construction process includes: A PostgreSQL instance is created using a first custom script, and the instance is connected to a Citus cluster using a second custom script. The script executes `CREATE EXTENSION citus` and configures inter-node communication authentication information. The data synchronization agent deployment module is used to deploy a database synchronization agent based on the Citus cluster to be written. The agent corresponds one-to-one with the Citus cluster nodes and is used to perform data extraction and writing. The full data synchronization module is used to perform full data synchronization based on the Citus cluster to be backed up, the sharding instruction, the synchronization relationship between the Citus cluster to be backed up and the Citus cluster to be written, and the database synchronization agent. The sharding instruction is generated by querying the table structure and sharding rules of the table to be synchronized through the database synchronization agent. The full data synchronization module is also used to shard the Citus cluster to be written based on the sharding instructions and the CN nodes of the Citus cluster to be written, so as to complete the full data synchronization. The incremental data synchronization module is used to perform incremental data synchronization based on the starting point of incremental data synchronization and the synchronization relationship after the full data synchronization is completed, so as to complete the database backup. The starting point is determined through the following steps: Query the latest position of the current write-ahead log (WAL) of the DN node of the Citus cluster to be backed up, and record it as the starting point for incremental synchronization; The synchronization relationship is achieved by configuring a database synchronization agent, which maps the CN and DN nodes of the Citus cluster to be backed up to the CN nodes of the Citus cluster to be written.

9. A computer device, characterized in that, The computer device includes a memory and a processor; The memory is used to store computer programs; The processor is configured to execute the computer program and, in executing the computer program, implement the backup method for the distributed database as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, causes the processor to implement the backup method for a distributed database as described in any one of claims 1 to 7.

Images