Intelligent logic processing method and device based on database and medium

Abstract

The embodiment of the invention discloses an intelligent logic processing method and equipment based on a database and a medium, belongs to the technical field of databases, and solves the problems of copying delay and resource waste during database copying. Comprising the steps of receiving user input parameters, and performing initialization processing on a subscriber environment based on the input parameters; performing database connection based on the connection character string between the publisher and the subscriber; based on the combined near-end strategy optimization algorithm and deep Q network algorithm, dynamically adjusting the maximum copy slot number and maximum work process data corresponding to the publisher and the subscriber respectively and parameters for controlling the pre-write log recording level in the database; based on a preset identifier, performing copy parameter consistency verification on the publisher and the subscriber; and under the condition that the verification is passed, copying data corresponding to the publisher database to a corresponding position corresponding to the subscriber database based on the dynamically adjusted parameters, the preset identifier and the database in which the connection relationship is established.

Description

Technical Field

[0001] This application relates to the field of database technologies, and in particular, to an intelligent logic processing method, device, and medium based on a database. Background Art

[0002] Logical replication is a database replication mechanism that allows changes to specific tables or the entire database to be transferred from a primary database (publisher) to one or more subscription databases (subscribers). PostgreSQL is a relational database management system that typically uses physical replication. However, during database migration and schema upgrades, it is necessary to manually convert to logical replication, which is cumbersome and error-prone.

[0003] Existing logical replication methods rely on the built-in logical replication mechanism of PostgreSQL to synchronize data through publication and subscription, support table-level data replication, and allow cross-database instance data synchronization. However, the above methods do not support seamless conversion and it is difficult to directly switch from physical replication to logical replication. It relies on manual configuration by database administrators, resulting in replication latency and resource waste. Summary of the Invention

[0004] Embodiments of this application provide an intelligent logic processing method, device, and medium based on a database to solve the following technical problems: The existing database replication mechanism relies on manual configuration by database administrators, resulting in replication latency and resource waste.

[0005] Embodiments of this application adopt the following technical solutions: Embodiments of this application provide an intelligent logic processing method based on a database. The method includes receiving user input parameters and initializing the subscriber environment based on the input parameters; establishing a database connection based on the connection string between the publisher and the subscriber; dynamically adjusting the maximum replication slot number, maximum worker process data, and parameters controlling the write-ahead log record level in the database for the publisher and the subscriber respectively based on the combined proximal policy optimization algorithm and deep Q-network algorithm; performing replication parameter consistency verification on the publisher and the subscriber based on a preset identifier; where the preset identifier is related to the publication name, replication slot name, and log sequence number; and in the case of passing the verification, copying the data corresponding to the publisher database to the corresponding location of the subscriber database based on the dynamically adjusted parameters, preset identifier, and the database with the established connection relationship.

[0006] In the embodiments of the present application, by smoothly converting the physical replication slot into a logical replication slot, database restart or data loss is avoided, seamless switching and interruption-free switching are achieved, and business continuity is ensured. Based on the combined proximal policy optimization algorithm and deep Q-network algorithm, the PostgreSQL logical replication parameters are dynamically adjusted to be intelligently optimized according to the instance load, reducing manual intervention and improving replication performance and system stability. According to the consistency check of the preset identifier with the key information such as the publication name, replication slot name, and log sequence number, the synchronization between the publisher and the subscriber during the data replication process is ensured. Through the check, problems of parameter mismatch are timely discovered and solved, avoiding data replication errors caused by inconsistent parameters.

[0007] In an implementation manner of the present application, user input parameters are received, and the subscriber environment is initialized based on the input parameters, specifically including: determining logical replication task information based on the user input parameters; wherein, the logical replication task information at least includes the target database, data directory, and publisher address; detecting the logical replication parameters, database components, subscriber data directory, and path based on the logical replication task information; and performing consistency detection on the system identifiers corresponding to the publisher and the subscriber respectively; in the case where the detection is passed, the initialization process is completed.

[0008] In an implementation manner of the present application, based on the combined proximal policy optimization algorithm and deep Q-network algorithm, the maximum replication slot number, maximum working process data, and the parameter controlling the write-ahead log record level in the database corresponding to the publisher and the subscriber respectively are dynamically adjusted, specifically including: determining the load status based on the preset load variables; wherein, the preset load variables at least include one of CPU usage rate, disk I / O load, space occupied by the write-ahead log, the number of currently used logical replication slots, replication latency of the subscriber, and network latency; determining the action space of the parameter controlling the write-ahead log record level corresponding to the deep Q-network algorithm, and determining the action spaces of the maximum replication slot number and the maximum working process data corresponding to the proximal policy optimization algorithm; constructing a reward function according to the current logical replication throughput, subscriber replication lag time, and the capacity of the write-ahead log; constructing a first loss function based on the load status and the reward function to optimize the deep Q-network algorithm; and constructing a second loss function based on the policy change rate and the advantage function to optimize the proximal policy optimization algorithm; through the optimized proximal policy optimization algorithm and the optimized deep Q-network algorithm, dynamic adjustment is performed.

[0009] In an implementation manner of the present application, dynamic adjustment is performed through the optimized Proximal Policy Optimization (PPO) algorithm and the optimized Deep Q-Network (DQN) algorithm, specifically including: obtaining the system views corresponding to PostgreSQL in sequence based on a preset interval time period; determining logical replication parameters based on the system views; dynamically adjusting the maximum replication slot number and the maximum worker process data in the logical replication parameters through the optimized Proximal Policy Optimization algorithm; and dynamically adjusting the parameter for controlling the Write-Ahead Logging (WAL) level in the logical replication parameters through the optimized Deep Q-Network algorithm.

[0010] In an implementation manner of the present application, replication parameter consistency verification is performed on the publisher and the subscriber based on a preset identifier, specifically including: creating a publication and a logical replication slot for the database corresponding to the publisher, and generating corresponding identifiers for the publication and the logical replication slot respectively; determining the log sequence numbers corresponding to each logical replication slot; writing a recovery configuration file in the subscriber data directory, and using the reference log sequence number in the historical record as the recovery target; starting the subscriber server, and when the subscriber server is restored to the position of the reference log sequence number, creating a subscription for the database corresponding to the subscriber based on the identifier; setting the initial replication progress of the subscription to the position corresponding to the reference log sequence number, and starting the subscription.

[0011] In an implementation manner of the present application, after copying the data corresponding to the publisher database to the corresponding position of the subscriber database, the method further includes: when the subscriber uses a physical replication slot, performing a deletion process on the physical replication slot; and deleting the failover replication slot corresponding to the subscriber; and changing the identifier corresponding to the subscriber so that the changed identifier is different from the identifier corresponding to the publisher; stopping the subscriber server to complete the logical replication setting.

[0012] In an implementation manner of the present application, after starting the subscriber server, the method further includes: detecting key metrics of the databases corresponding to the publisher and the subscriber respectively through a preset anomaly detection model to output key metric scores; where the key metrics include database resource usage data and database internal state parameters; classifying the key metric scores according to the metric type; sorting the key metric scores corresponding to different categories in chronological order to generate a metric data sequence; comparing the metric data within a preset time-series sliding window with the corresponding metric thresholds to determine the data proportion of the metric data within the preset time-series sliding window that is greater than the metric thresholds; when the data proportion is greater than a preset data proportion, determining that the currently detected database has an anomaly.

[0013] In one implementation of the present application, after receiving user input parameters, the method further includes: cleaning the created objects when an abnormality occurs in any step; and sending a reminder to the user to recreate the physical standby database if an abnormality is detected after the subscriber server recovery is completed.

[0014] An embodiment of the present application provides an intelligent logic processing device based on a database, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to: receive user input parameters, initialize the subscriber environment based on the input parameters; establish a database connection based on the connection string between the publisher and the subscriber; dynamically adjust the maximum replication slot number, maximum worker process data, and the parameter controlling the write-ahead logging level in the database respectively corresponding to the publisher and the subscriber based on the combined proximal policy optimization algorithm and deep Q-network algorithm; perform replication parameter consistency verification on the publisher and the subscriber based on a preset identifier; wherein the preset identifier is related to the publication name, replication slot name, and log sequence number; and copy the data corresponding to the publisher database to the corresponding location of the subscriber database based on the dynamically adjusted parameters, preset identifier, and the database with the established connection relationship when the verification passes.

[0015] A non-volatile computer storage medium provided by an embodiment of the present application stores computer-executable instructions, and the computer-executable instructions are set to: receive user input parameters, initialize the subscriber environment based on the input parameters; establish a database connection based on the connection string between the publisher and the subscriber; dynamically adjust the maximum replication slot number, maximum worker process data, and the parameter controlling the write-ahead logging level in the database respectively corresponding to the publisher and the subscriber based on the combined proximal policy optimization algorithm and deep Q-network algorithm; perform replication parameter consistency verification on the publisher and the subscriber based on a preset identifier; wherein the preset identifier is related to the publication name, replication slot name, and log sequence number; and copy the data corresponding to the publisher database to the corresponding location of the subscriber database based on the dynamically adjusted parameters, preset identifier, and the database with the established connection relationship when the verification passes.

[0016] The above at least one technical solution adopted in the embodiments of the present application can achieve the following beneficial effects: By smoothly converting the physical replication slot into a logical replication slot, the embodiments of the present application avoid database restart or data loss, realize seamless switching and interruption-free switching, and ensure business continuity. Based on the combined proximal policy optimization algorithm and deep Q-network algorithm, the PostgreSQL logical replication parameters are dynamically adjusted to be intelligently optimized according to the instance load, reducing manual intervention and improving replication performance and system stability. According to the consistency check of the preset identifier with the key information such as the publication name, replication slot name, and log sequence number, the synchronization between the publisher and the subscriber during the data replication process is ensured. Through the check, problems of parameter mismatch are timely discovered and solved, avoiding data replication errors caused by inconsistent parameters. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments recorded in the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts. In the drawings: Figure 1 It is a flowchart of an intelligent logic processing method based on a database provided by an embodiment of the present application; Figure 2 It is a flowchart of a method for initializing a subscriber environment provided by an embodiment of the present application; Figure 3 It is a schematic flowchart of optimizing the configurations of a publisher and a subscriber provided by an embodiment of the present application; Figure 4 It is a schematic flowchart of a consistency check provided by an embodiment of the present application; Figure 5 It is a schematic flowchart of logical replication cleaning and winding up provided by an embodiment of the present application; Figure 6 It is a schematic structural diagram of an intelligent logic processing device based on a database provided by an embodiment of the present application.

[0018] REFERENCE SIGNS 600: Intelligent logic processing device based on a database, 601: Processor, 602: Memory. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0019] The embodiments of the present application provide an intelligent logic processing method, device, and medium based on a database.

[0020] To enable those skilled in the art to better understand the technical solutions in this application, the following will clearly and completely describe the technical solutions in the embodiments of this application in conjunction with the accompanying drawings in the embodiments of this application. Obviously, the described embodiments are only a part of the embodiments of this application, rather than all the embodiments. Based on the embodiments of this specification, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the scope of protection of this application.

[0021] The following will provide a detailed description of the technical solutions proposed in the embodiments of the present invention through the accompanying drawings.

[0022] Figure 1 The flowchart of an intelligent logic processing method based on a database provided for the embodiments of this application is as Figure 1 shown. The intelligent logic processing method based on a database includes the following steps: Step 101: Receive user input parameters and perform initialization processing on the subscriber environment based on the input parameters.

[0023] In an implementation manner of this application, based on the user input parameters, logical replication task information is determined; wherein, the logical replication task information includes at least a target database, a data directory, and a publisher address. Based on the logical replication task information, the logical replication parameters, database components, subscriber data directory, and path are detected. And, consistency detection is performed on the system identifiers corresponding to the publisher and the subscriber respectively. When the detection passes, the initialization processing is completed.

[0024] Specifically, Figure 2 The flowchart of a method for initializing a subscriber environment provided for the embodiments of this application is as Figure 2 shown: Step 201: Parse the user input parameters. Specifically, it includes database (target database), pgdata (data directory), and publisher - server (publisher address), etc. Specifically, database clarifies which target database the data is to be replicated to, and different business scenarios may have different target database requirements; pgdata is the data directory, which stipulates the storage location of the data on the disk, and reasonable data directory planning is conducive to data management and maintenance; publisher - server indicates the address of the publisher database server where the source data is located, which is the key information for establishing a communication connection with the publisher.

[0025] Step 202: Verify the required parameters, including the data directory, publisher connection information, etc.

[0026] Step 203: Set default values (such as port 50432).

[0027] Step 204: Perform environment verification to ensure the integrity and availability of database components (pg_ctl, pg_resetwal). Specifically, database components are the foundation for the normal operation of the database system. pg_ctl is a control tool for the PostgreSQL database, used for operations such as starting, stopping, and restarting the database service; pg_resetwal is used to reset the Write-Ahead Log (WAL). Ensuring the integrity and availability of these components can avoid various problems caused by missing or damaged components during logical replication, and ensure the stability and reliability of the database service.

[0028] Step 205: Check that the subscriber data directory exists and is empty, or is a valid data directory (including the PG_VERSION file). Specifically, the subscriber data directory is where the replicated data is stored. It has two acceptable states. One is that the directory exists and is empty, and new replicated data can be directly written without conflicting with existing data; the other is that the directory is a valid data directory, that is, it contains the PG_VERSION file. The PG_VERSION file records the version information of the database. By checking this file, it can be ensured that the data directory is an accurate PostgreSQL data directory, avoiding copying data to the wrong directory and ensuring data security and consistency.

[0029] Step 206: Standardize path processing to avoid initialization failures caused by incorrect pgdata directories.

[0030] Step 207: Verify whether the system identifiers of the publisher and subscriber are consistent. Specifically, when the system identifiers are consistent, it can ensure that the data sources and destinations of replication are correctly corresponding.

[0031] Step 102: Based on the connection string between the publisher and the subscriber, establish a database connection.

[0032] Figure 3 A flowchart of a process for optimizing the configuration of the publisher and subscriber provided by an embodiment of this application is as shown in Figure 3 Steps 301 and 302 shown below: Step 301: Parse the connection strings of the publisher and the subscriber.

[0033] Step 302: Establish database connections with the publisher and the subscriber.

[0034] Specifically, the connection string contains the information required to connect to the database, such as the database server address, port, username, password, etc. Parsing the connection string allows the system to accurately obtain this information and prepare for establishing a database connection later. Based on the connection information obtained through parsing, the system attempts to establish connections with the databases of the publisher and the subscriber. In the case of successful connection establishment, subsequent checks and operations are performed on the database.

[0035] Step 103: Dynamically adjust the maximum replication slot quantity, the maximum worker process data, and the parameter controlling the write-ahead log record level in the database corresponding to the publisher and the subscriber respectively based on the combined proximal policy optimization algorithm and deep Q-network algorithm.

[0036] In an implementation manner of this application, based on preset load variables, determine the load status; where the preset load variables include at least one of CPU usage rate, disk I / O load, space occupied by the write-ahead log, the number of logical replication slots currently in use, the replication latency of the subscriber, and network latency. Determine the action space of the parameter controlling the write-ahead log record level corresponding to the deep Q-network algorithm, and determine the action spaces of the maximum replication slot quantity and the maximum worker process data corresponding to the proximal policy optimization algorithm. Construct a reward function based on the current logical replication throughput, the subscriber replication lag time, and the capacity of the write-ahead log. Construct a first loss function based on the load status and the reward function to optimize the deep Q-network algorithm. And construct a second loss function based on the policy change rate and the advantage function to optimize the proximal policy optimization algorithm. Perform dynamic adjustment through the optimized proximal policy optimization algorithm and the optimized deep Q-network algorithm.

[0037] Specifically, Figure 3 is a schematic flowchart of a process for optimizing the configurations of the publisher and the subscriber provided by an embodiment of this application, as shown in Figure 3 steps 303 to 306 therein: Step 303: Ensure that the publisher is not in the recovery mode (i.e., not a standby database). Specifically, the recovery mode usually means that the database is a standby database that is receiving data from the primary database to restore its own state. Logical replication requires the publisher to be the primary database that can actively provide data, so it is necessary to ensure that the publisher is not in the recovery mode.

[0038] Step 304: Check whether the configuration of the publisher supports logical replication.

[0039] Specifically, when wal_level = logical, max_replication_slots and max_wal_senders must have sufficient resources to support new logical replication. Specifically, wal_level (Write-Ahead Log level) is an important configuration parameter in the PostgreSQL database. When set to logical, the database records detailed enough log information. max_replication_slots represents the maximum number of replication slots allowed to be created in the database. If max_replication_slots is set too small, and there are already many existing replication tasks, or new logical replication tasks are about to come, then the publisher may not have enough replication slots to allocate to new tasks, resulting in the failure to start new logical replication. max_wal_senders specifies the maximum number of WAL (Write-Ahead Log) sender processes that the database can run simultaneously. In logical replication, the publisher uses WAL sender processes to send data change information to subscribers. When there is a new logical replication task, the publisher needs to start a WAL sender process to communicate with the subscriber and send data. If max_wal_senders is set insufficiently, and there are already many WAL sender processes running, or new logical replication tasks are about to come, then the publisher may not be able to start a new WAL sender process for new tasks, thus causing new logical replication to fail.

[0040] Step 305: Check whether the subscriber's configuration supports logical replication.

[0041] Specifically, max_replication_slots, max_logical_replication_workers, and max_worker_processes must have sufficient resources to support new logical replication. Specifically, max_replication_slots determines the maximum number of logical replication tasks that the subscriber can participate in simultaneously. max_logical_replication_workers specifies the maximum number of logical replication worker processes that the subscriber can run simultaneously. During logical replication, the subscriber needs these worker processes to receive, process, and apply data changes from the publisher. max_worker_processes defines the total number of worker processes that the database can run simultaneously.

[0042] Step 306: Ensure that the subscriber is in recovery mode (i.e., it is a standby database). Specifically, ensure that the subscriber is in recovery mode, that is, the subscriber is a standby database that can receive data from the publisher and synchronize it. Only when the subscriber is in recovery mode can data replication and synchronization be achieved.

[0043] Among them, the specific methods of step 304 and step 305 are as follows: Use the Deep Q-Network algorithm + Proximal Policy Optimization algorithm. The Proximal Policy Optimization algorithm is responsible for the dynamic adjustment of max_replication_slots and max_worker_processes, and the Deep Q-Network algorithm is responsible for the discrete selection with wal_level = logical.

[0044] 1. State space: To describe the load situation of the PostgreSQL database, define the state vector: S t = [CPU_usage, IO_load, WAL_size, active_replication_slots, sync_lag, network_latency]; The specific variables are as follows: CPU_usage represents the CPU usage rate (%) of the current PostgreSQL server; IO_load represents the disk I / O load (MB / s); WAL_size represents the space occupied by the WAL log (MB); active_replication_slots represents the number of currently used logical replication slots; sync_lag represents the replication latency of the subscriber (seconds); network_latency represents the network latency (ms).

[0045] For example, at a certain moment, the CPU usage rate of the PostgreSQL server is 70%, the disk I / O load is 50 MB / s, the space occupied by the WAL log is 200 MB, the number of currently used logical replication slots is 5, the replication latency of the subscriber is 2 seconds, and the network latency is 50 ms. Then the state vector St at this time = [70, 50, 200, 5, 2, 50].

[0046] 2. Action space: The Deep Q-Network algorithm selects wal_level: wal_level = minimal; wal_level = replica; wal_level = logical; Different wal_level values will affect the logging level and replication function of PostgreSQL, and the appropriate wal_level needs to be selected according to the load situation of the database.

[0047] The Proximal Policy Optimization algorithm adjusts the replication slots and worker processes: max_replication_slots: Adjust continuously within the range of [1, 20]; max_worker_processes: Adjust continuously within the range of [1, 32]; By adjusting these two parameters, the resources of the database can be reasonably allocated to improve the performance of logical replication.

[0048] 3. Reward function: To optimize the performance and stability of logical replication, define the reward function: R t =α(throughput)-β(sync_lag)-γ(WAL_overflow); Where: throughput represents the current logical replication throughput (MB / s), and the goal is to maximize it; sync_lag represents the replication lag time of the subscriber (seconds), and the goal is to minimize it; WAL_overflow represents that the WAL directory exceeds 80% of its capacity, giving a negative reward; α is the first hyperparameter, β is the second hyperparameter, and γ is the third hyperparameter, which can adjust the influence weight.

[0049] 4. Collect data and construct a dataset corresponding to state-reward. Specifically, the system continuously collects the load data of the PostgreSQL server and constructs a dataset corresponding to state-reward. These data will be used to train the Deep Q-Network algorithm and the Proximal Policy Optimization algorithm.

[0050] 5. Train the Deep Q-Network algorithm to select wal_level: Input the current PostgreSQL load state St.

[0051] The Deep Q-Network algorithm outputs the Q values corresponding to different wal_levels and selects the maximum value.

[0052] Loss function: L(θ)=(R t +γmaxQ(S t+1 ,a)-Q(S t ,a))2; Where, L(θ) is the loss function; R t is the reward function; S t is the current PostgreSQL load state; S t+1 is the next moment's PostgreSQL load state; α is the first hyperparameter, γ is the third hyperparameter; Training data: Use the WAL generation rate and replication throughput as the Q-value return to optimize the wal_level selection strategy.

[0053] 6. Training PPO for dynamic parameter adjustment: Policy network π(a|s): Input the PostgreSQL load S t , and output parameter adjustment actions (max_replication_slots, max_worker_processes).

[0054] Loss function: Adopt Clip Loss to avoid gradient explosion: ; Among them, is the policy change rate; is the advantage function; is a relatively small constant.

[0055] Training method: Environment interaction: This algorithm simulates different loads on the PostgreSQL server to train the policy network.

[0056] Reward feedback: Observe the decrease in the subscriber replication lag sync_lag and the increase in the current logical replication throughput throughput, and optimize the policy.

[0057] 7. After training is completed, the PostgreSQL parameters can be adjusted in real time: In an implementation manner of this application, based on a preset interval time period, the system views corresponding to PostgreSQL are obtained in sequence. Determine the logical replication parameters based on the system views. Dynamically adjust the maximum replication slot number and the maximum worker process data through the optimized proximal policy optimization algorithm. And, dynamically adjust the parameters for controlling the write-ahead logging level through the optimized deep Q-network algorithm.

[0058] Specifically, monitor the PostgreSQL load, collect pg_stat_replication data every 30 seconds. Use the deep Q-network algorithm to select wal_level to ensure that the replication mode adapts to the load; use the proximal policy optimization algorithm to adjust max_replication_slots and max_worker_processes, dynamically expand the replication ability under high load, and reduce resource occupancy under low load.

[0059] Step 104. Based on the preset identifier, perform replication parameter consistency verification on the publisher and the subscriber. Among them, the preset identifier is related to the publication name, the replication slot name, and the log sequence number.

[0060] In an implementation manner of the present application, a publication and logical replication slot is created for the database corresponding to the publisher, and corresponding identifiers are generated for the publication and logical replication slot respectively. The log sequence number corresponding to each logical replication slot is determined. A recovery configuration file is written in the subscriber data directory, and the reference log sequence number in the historical record is used as the recovery target. The subscriber server is started, and when the subscriber server is restored to the position of the reference log sequence number, a subscription is created for the database corresponding to the subscriber based on the identifier. The initial replication progress of the subscription is set to the position corresponding to the reference log sequence number, and the subscription is started.

[0061] Specifically, Figure 4 FIG. is a schematic flow chart of a consistency check provided by an embodiment of the present application. As Figure 4 shown, the consistency check includes the following steps: Step 401: Start the subscriber server and restrict access using temporary parameters.

[0062] Step 402: Ensure that the server starts successfully and can accept connections.

[0063] Step 403: Create a publication and a logical replication slot for each database on the publisher. If the publication name or replication slot name is not specified, a unique identifier is automatically generated.

[0064] Step 404: Record the LSN of each replication slot for subsequent setting of the starting point of the subscription.

[0065] Specifically, in logical replication, recording the LSN (log sequence number) of each replication slot is to determine from which position the subscriber starts to receive and apply the data changes of the publisher. When configuring on the subscriber side later, this LSN will be used to set the starting point of replication, so as to ensure the continuity and accuracy of data replication.

[0066] Step 405: Write a recovery configuration file in the subscriber data directory and set the recovery target to the previously recorded LSN. Specifically, by setting this recovery target, the subscriber can accurately start data replication and recovery operations from the specified position.

[0067] Step 406: Ensure that the subscriber stops at the specified LSN position during the recovery process.

[0068] Specifically, only by stopping the recovery at the accurate LSN position can it be ensured that the data of the subscriber is consistent with the data of the publisher before the start of logical replication.

[0069] Step 407: Start the subscriber server and wait for it to complete the recovery and reach a consistent state.

[0070] Specifically, wait for the server to complete the recovery process and reach a state of data consistency, which means that the subscriber has obtained the necessary data change information from the publisher and updated the data to the state corresponding to the specified LSN position of the publisher. Only after reaching the consistent state can the subscriber start the normal logical replication operation.

[0071] Step 408: If a recovery timeout is set, terminate the recovery process after the timeout.

[0072] If the subscriber does not complete the recovery operation within the specified time, the system will automatically terminate the recovery process for further inspection and processing.

[0073] Step 409: Create subscriptions for each database on the subscriber, using the previously created publication and replication slot.

[0074] Specifically, after the subscriber server completes the recovery and reaches the consistent state, create subscriptions for each database that needs to perform logical replication. When creating subscriptions, use the publication and replication slot information previously created on the publisher, so that the corresponding relationship between the publisher and the subscriber can be established, clarifying which publication of which publisher the subscriber will obtain data from, and tracking the progress of data replication through the corresponding replication slot.

[0075] Step 410: Set the initial replication progress of the subscription to the specified LSN.

[0076] By setting the initial replication progress, the subscriber can accurately start receiving and applying subsequent data changes from the specified LSN position of the publisher, ensuring the continuity and consistency of data replication.

[0077] Step 411: Enable the subscription and start logical replication.

[0078] Step 105: Under the condition that the verification passes, based on the dynamically adjusted parameters, preset identifiers, and the database with established connection relationship, copy the data corresponding to the publisher database to the corresponding position of the subscriber database.

[0079] In an implementation manner of the present application, under the condition that the parameter consistency verification, data integrity verification, and connectivity verification pass, according to factors such as the system load and performance requirements, use the optimized deep Q-network algorithm and the optimized proximal policy optimization algorithm to dynamically adjust the relevant parameters. Based on the dynamically adjusted parameters, it can be ensured that the data replication process can proceed efficiently and stably under different system loads. After the verification passes, the parameters are dynamically adjusted, the preset identifiers are determined, and the database connection is established, copy the data corresponding to the publisher database to the corresponding position of the subscriber database.

[0080] In an implementation of the present application, when a subscriber uses a physical replication slot, the physical replication slot is deleted. Also, the failover replication slot corresponding to the subscriber is deleted. Also, the identifier corresponding to the subscriber is changed so that the changed identifier is different from the identifier corresponding to the publisher. The subscriber server is stopped to complete the logical replication setup.

[0081] Figure 5 A schematic diagram of the process for logical replication cleanup and conclusion provided by an embodiment of the present application, as Figure 5 shown in steps 501 to 503 therein, the logical replication cleanup and conclusion include the following steps: Step 501: If the subscriber previously used a physical replication slot, delete the replication slot.

[0082] Step 502: Delete the failover replication slot on the subscriber.

[0083] Step 503: Modify the system identifier of the subscriber to avoid conflicts with the publisher.

[0084] In an implementation of the present application, when an abnormality occurs in any step, the created objects are cleaned up. Also, after the subscriber server recovery is completed, if an abnormality is detected in any step, a reminder to recreate the physical standby database is sent to the user.

[0085] Figure 5 A schematic diagram of the process for logical replication cleanup and conclusion provided by an embodiment of the present application, as Figure 5 shown in steps 504 to 506 therein, the logical replication cleanup and conclusion include the following steps: Step 504: Stop the subscriber server to complete the setup of logical replication.

[0086] Step 505: If an error occurs in any step, the program attempts to clean up the created objects.

[0087] For example, attempt to clean up publications, replication slots, etc.

[0088] Step 506: If the recovery process has been completed but subsequent steps fail, the program will prompt the user that they must recreate the physical standby database.

[0089] In an implementation manner of the present application, by presetting an anomaly detection model, key indicators of the databases corresponding to the publisher and the subscriber respectively are detected to output key indicator scores; wherein, the key indicators include database resource usage data and database internal state parameters. According to the indicator types, the key indicator scores are classified. In chronological order, the key indicator scores corresponding to different categories are sorted to generate an indicator data sequence. The indicator data within the preset time-series sliding window is compared with the corresponding indicator threshold to determine the data ratio of the indicator data within the preset time-series sliding window that is greater than the indicator threshold. When the data ratio is greater than the preset data ratio, it is determined that the currently detected database has an anomaly.

[0090] Specifically, the preset anomaly detection model in the embodiments of the present application can identify various patterns and characteristics in the operation of the database. The role of this model is to analyze the relevant data of the database and determine whether the database is in a normal operating state. In the scenario of logical replication, the key indicators include database resource usage data and database internal state parameters. Database resource usage data, such as CPU usage rate, memory occupancy, disk I / O read and write rate, etc., reflects the consumption of system resources by the database during operation. And database internal state parameters, such as the number of transaction processes, the number of locks, the number of connections, etc., reflect the internal operating state of the database. By detecting these key indicators, the model can obtain a key indicator score, which can quantify the operating condition of the database in the current state.

[0091] Among them, the training method of the preset anomaly detection model is: using the databases corresponding to the historical publisher and subscriber respectively as input samples, and using the scores of the key indicators corresponding to the input samples as output samples to train the preset neural network model to obtain the preset anomaly detection model.

[0092] Furthermore, since there are multiple types of key indicators, classifying the key indicator scores according to the indicator types helps to more clearly analyze and manage these indicators. For example, all indicator scores related to the CPU are classified into one category, and those related to the memory are classified into another category, etc.

[0093] Further, sort the key indicator scores of different categories in chronological order to obtain an indicator data sequence reflecting the change of the database running state over time. Further, the preset time-series sliding window in the embodiment of the present application is a preset time range, which slides on the indicator data sequence. As time goes by, this window will continuously move forward to cover new time periods. For each key indicator, there is a preset indicator threshold. This threshold is determined according to factors such as the normal running range of the database and business requirements. By comparing the indicator data within the sliding window with the corresponding indicator threshold, it can be judged whether the indicator data exceeds the normal range during this time period. Through comparison, count the proportion of the number of indicator data greater than the indicator threshold in the total data number within the preset time-series sliding window. This proportion reflects the degree to which the indicator data deviates from the normal range during this time period.

[0094] Further, the preset data ratio in the embodiment of the present application is a preset standard value, which represents to what extent the indicator data exceeding the threshold can be considered that the database has an abnormality. When the calculated ratio of the indicator data greater than the indicator threshold within the sliding window exceeds this preset data ratio, it can be judged that the currently detected database has an abnormal situation.

[0095] Figure 6 It is a schematic structural diagram of an intelligent logic processing device based on a database provided by an embodiment of the present application. As Figure 6 shown, the intelligent logic processing device 600 based on the database includes: at least one processor 601; and a memory 602 communicatively connected to the at least one processor 601; wherein, the memory 602 stores instructions executable by the at least one processor 601, and the instructions are executed by the at least one processor 601 so that the at least one processor 601 can: receive user input parameters and perform initialization processing on the subscriber environment based on the input parameters; perform database connection based on the connection string between the publisher and the subscriber; dynamically adjust the maximum replication slot number, the maximum working process data, and the parameter controlling the write-ahead logging level in the database corresponding to the publisher and the subscriber respectively based on the combined proximal policy optimization algorithm and deep Q-network algorithm; perform replication parameter consistency verification on the publisher and the subscriber based on the preset identifier; wherein, the preset identifier is related to the publication name, the replication slot name, and the log sequence number; in the case of passing the verification, based on the dynamically adjusted parameters, the preset identifier, and the database with the established connection relationship, copy the data corresponding to the publisher database to the corresponding position of the subscriber database.

[0096] A non-volatile computer storage medium provided by an embodiment of the present application stores computer-executable instructions, and the computer-executable instructions are set to: receive user input parameters, and perform initialization processing on the subscriber environment based on the input parameters; perform database connection based on the connection string between the publisher and the subscriber; based on the combined proximal policy optimization algorithm and deep Q-network algorithm, dynamically adjust the maximum replication slot number, maximum worker process data, and parameters for controlling the write-ahead logging level in the database corresponding to the publisher and the subscriber respectively; perform replication parameter consistency verification on the publisher and the subscriber based on a preset identifier; wherein, the preset identifier is related to the publication name, replication slot name, and log sequence number; in the case of passing the verification, based on the dynamically adjusted parameters, preset identifier, and the database with the established connection relationship, copy the data corresponding to the publisher database to the corresponding position of the subscriber database.

[0097] The embodiments in the present application are all described in a progressive manner. For the same or similar parts among the embodiments, reference can be made to each other. Each embodiment focuses on the differences from other embodiments. In particular, for the embodiments of the device, equipment, and non-volatile computer storage medium, since they are basically similar to the method embodiments, the description is relatively simple, and the relevant parts can refer to the partial description of the method embodiments.

[0098] The above are only the embodiments of the present application and are not used to limit the present application. For those skilled in the art, various changes and modifications can be made to the embodiments of the present application. These modifications or substitutions do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application.

Claims

1. A database-based intelligent logic processing method, characterized in that: The method comprises: Receive user input parameters, and initialize the subscriber environment based on the input parameters; Based on the connection string between the publisher and the subscriber, a database connection is performed; Based on the combined proximal strategy optimization algorithm and deep Q network algorithm, dynamically adjust the maximum number of replication slots, the maximum work process data, and the parameters controlling the write-ahead logging level in the database corresponding to the publisher and the subscriber respectively; Based on a preset identifier, performing a replication parameter consistency check on the publisher and the subscriber; wherein the preset identifier is related to the publication name, the replication slot name, and the log sequence number; When the verification is passed, based on the dynamically adjusted parameters, the preset identifier and the database with the established connection relationship, the data corresponding to the publisher database is copied to the corresponding position corresponding to the subscriber database.

2. The method of intelligent logic processing based on a database according to claim 1, characterized in that: The receiving of user input parameters and initializing the subscriber environment based on the input parameters specifically includes: Based on the user input parameters, determine the logical replication task information; wherein the logical replication task information at least includes a target database, a data directory, and a publisher address; Based on the logical replication task information, the logical replication parameters, database components, subscriber data directories and paths are detected; And, performing consistency detection on the system identifiers respectively corresponding to the publisher and the subscriber; If the detection is passed, the initialization process is completed.

3. The method of intelligent logic processing based on a database according to claim 1, characterized in that: The method dynamically adjusts the maximum number of replication slots, the maximum work process data, and the parameters for controlling the write-ahead logging level in the database corresponding to the publisher and the subscriber respectively based on the combined proximal strategy optimization algorithm and the deep Q network algorithm, specifically including: Determine the load status based on preset load variables; wherein the preset load variables include at least one of CPU usage, disk I / O load, control of write-ahead log space occupied, number of currently used logical replication slots, replication delay of subscribers, and network delay; Determine the action space of the parameters controlling the level of write-ahead logging corresponding to the deep Q network algorithm, and determine the action space of the maximum number of replication slots and the action space of the maximum number of worker process data corresponding to the proximal policy optimization algorithm; Construct a reward function based on the current logical replication throughput, subscriber replication lag, and the capacity of the write-ahead log; Based on the load state and the reward function, construct a first loss function to optimize the deep Q network algorithm; and, constructing a second loss function based on the strategy change rate and the advantage function to optimize the proximal strategy optimization algorithm; Dynamic adjustment is performed through the optimized proximal strategy optimization algorithm and the optimized deep Q network algorithm.

4. The intelligent logic processing method based on a database according to claim 3, characterized in that: The dynamic adjustment is performed by using the optimized proximal strategy optimization algorithm and the optimized deep Q network algorithm, specifically including: Based on the preset interval time period, obtain the corresponding system views of PostgreSQL in sequence; Determining logical replication parameters based on the system view; Dynamically adjust the maximum number of replication slots and the maximum work process data in the logical replication parameters through the optimized proximal strategy optimization algorithm; And, through the optimized deep Q network algorithm, the parameters controlling the write-ahead logging level in the logical replication parameters are dynamically adjusted.

5. The intelligent logic processing method based on a database according to claim 1, characterized in that: The performing replication parameter consistency check on the publisher and the subscriber based on the preset identifier specifically includes: Creating a publication and a logical replication slot for the database corresponding to the publisher, and generating corresponding identifiers for the publication and the logical replication slot respectively; Determine the log sequence number corresponding to each of the logical replication slots; Write a recovery configuration file in the subscriber data directory and use the reference log sequence number in the history as the recovery target; Starting the subscriber server, and creating a subscription for the database corresponding to the subscriber based on the identifier when the subscriber server is restored to the reference log sequence number position; The initial replication progress of the subscription is set to the position corresponding to the reference log sequence number, and the subscription is started.

6. The method of intelligent logic processing based on a database according to claim 1, characterized in that: After copying the data corresponding to the publisher database to the corresponding location corresponding to the subscriber database, the method further includes: In the case where the subscriber uses a physical replication slot, deleting the physical replication slot; And, deleting the failover replication slot corresponding to the subscriber; and, changing the identifier corresponding to the subscriber so that the changed identifier is different from the identifier corresponding to the publisher; Stop the subscriber server to complete the logical replication setup.

7. The method of intelligent logic processing based on a database according to claim 5, characterized in that: After starting the subscriber server, the method further includes: By presetting anomaly detection models, key indicator detection is performed on the databases corresponding to the publisher and the subscriber, respectively, to output key indicator scores; wherein the key indicators include database resource usage data and database internal state parameters; Classify the key indicator scores according to the indicator type; Sorting the key indicator scores corresponding to different categories in chronological order to generate an indicator data sequence; Comparing the indicator data in the preset time series sliding window with the corresponding indicator threshold value to determine the data proportion of the indicator data in the preset time series sliding window that is greater than the indicator threshold value; When the data ratio is greater than a preset data ratio, it is determined that an abnormality exists in the currently detected database.

8. The intelligent logic processing method based on a database according to claim 5, characterized in that: After receiving the user input parameter, the method further includes: If an exception is detected in any step, the created objects are cleaned up; And, after the subscriber server recovery is completed, if any abnormality is detected in any step, a reminder to re-create the physical standby database is sent to the user.

9. An intelligent logic processing device based on a database, characterized in that: The device comprises a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to execute the method according to any one of claims 1 to 8.

10. A non-volatile computer storage medium storing computer executable instructions, characterized in that: The computer executable instructions can execute the method according to any one of claims 1 to 8.

Images