An intelligent logic processing method, device and medium based on a database

Abstract

An embodiment of the present application discloses an intelligent logic processing method, device, and medium based on a database, belonging to the technical field of databases, and solving the problems of replication delay and resource waste during database replication. The method includes receiving user input parameters and initializing a subscriber environment based on the input parameters; establishing a database connection based on a connection string between a publisher and a subscriber; dynamically adjusting the maximum replication slot number, maximum worker process data, and parameters controlling the write-ahead log record level in the database respectively corresponding to the publisher and the subscriber based on a 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; and in the case of passing the verification, copying the data corresponding to the publisher database to the corresponding position of the subscriber database based on the dynamically adjusted parameters, the preset identifier, and the database with the established connection relationship.

Description

Technical Field

[0001] This application relates to the field of database technology, and particularly 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 subscriber databases. PostgreSQL is a relational database management system that typically uses physical replication, but during database migration and schema upgrades, it needs to be manually converted 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 publishing and subscribing, 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, relying on database administrators to manually configure, resulting in replication delays 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 database administrators to manually configure, resulting in replication delays and resource waste.

[0005] Embodiments of this application adopt the following technical solutions:

[0006] 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 consistency verification of replication parameters for 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 when the verification passes, 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.

[0007] In the embodiment 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 realized, 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 verification 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 verification, problems of parameter mismatch are timely discovered and solved, avoiding data replication errors caused by inconsistent parameters.

[0008] In one 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; where 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 respectively corresponding to the publisher and the subscriber; in the case where the detection is passed, the initialization process is completed.

[0009] In one implementation manner of the present application, based on the combined proximal policy optimization algorithm and deep Q-network algorithm, the maximum replication slot quantity, maximum working process data, and the parameter controlling the write-ahead log record level in the database respectively corresponding to the publisher and the subscriber are dynamically adjusted, specifically including: determining the load status based on the preset load variables; where 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 quantity 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.

[0010] In an implementation manner of the present application, dynamic adjustment is performed through the optimized proximal policy optimization algorithm and the optimized deep Q-network algorithm, which specifically includes: sequentially obtaining the system views corresponding to PostgreSQL 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 parameters for controlling the write-ahead logging level in the logical replication parameters through the optimized deep Q-network algorithm.

[0011] 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, which 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; determining the log sequence numbers respectively 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.

[0012] 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.

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

[0014] In an 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, after the subscriber server recovery is completed, if an abnormality is detected in any step, sending a reminder to the user to recreate the physical standby database.

[0015] 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 log record 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, when the verification passes, 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.

[0016] 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 log record 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, when the verification passes, 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.

[0017] 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 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. Description of the Drawings

[0018] 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, without creative efforts, other drawings can be obtained based on these drawings. In the drawings:

[0019] Figure 1 It is a flowchart of an intelligent logic processing method based on a database provided by an embodiment of the present application;

[0020] Figure 2 It is a flowchart of a method for initializing a subscriber environment provided by an embodiment of the present application;

[0021] Figure 3 It is a schematic flowchart of a process for optimizing the configurations of a publisher and a subscriber provided by an embodiment of the present application;

[0022] Figure 4 It is a schematic flowchart of a process for consistency check provided by an embodiment of the present application;

[0023] Figure 5 It is a schematic flowchart of a process for logical replication cleanup and winding up provided by an embodiment of the present application;

[0024] 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.

[0025] Reference Signs:

[0026] 600: Intelligent logic processing device based on a database, 601: Processor, 602: Memory. Detailed Embodiments

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

[0028] In order to enable those skilled in the art to better understand the technical solutions in the present application, the following will clearly and completely describe the technical solutions in the embodiments of the present application with reference to the accompanying drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present 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 protection scope of the present application.

[0029] The technical solutions proposed in the embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0030] Figure 1 The flowchart of an intelligent logic processing method based on a database provided for the embodiments of the present application is as Figure 1 shown. The intelligent logic processing method based on a database includes the following steps:

[0031] Step 101: Receive user input parameters and perform initialization processing on the subscriber environment based on the input parameters.

[0032] In one implementation manner of the present 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. Also, consistency detection is performed on the system identifiers respectively corresponding to the publisher and the subscriber. When the detection passes, the initialization processing is completed.

[0033] Specifically, Figure 2 The flowchart of a method for initializing a subscriber environment provided for the embodiments of the present application is as Figure 2 shown:

[0034] 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 copied to, and different business scenarios may have different target database requirements; pgdata is the data directory, which specifies the storage location of the data on the disk, and reasonable data directory planning is beneficial 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.

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

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

[0037] Step 204: Perform environment verification to ensure that the database components are complete and available (pg_ctl, pg_resetwal). Specifically, the 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.

[0038] 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 the security and consistency of the data.

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

[0040] Step 207: Verify whether the system identifiers of the publisher and subscriber are consistent. Specifically, when the system identifiers are consistent, it can be ensured that the data source and destination of the replication are correctly corresponding.

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

[0042] Figure 3 A schematic diagram of a process for optimizing the configuration of the publisher and subscriber provided by the embodiment of the present application is as shown in Figure 3 Steps 301 and 302 shown below:

[0043] Step 301: Parse the connection strings of the publisher and the subscriber.

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

[0045] 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 to the databases of the publisher and the subscriber. In the case of successful connection establishment, subsequent checks and operations are performed on the database.

[0046] Step 103: Dynamically adjust the maximum number of replication slots, the maximum worker process data, and the parameter controlling the write-ahead log record 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.

[0047] In an implementation manner of the present application, based on a preset load variable, the load status is determined; wherein, the preset load variable includes 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 number of replication slots 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. Based on the load status and the reward function, construct a first loss function to optimize the deep Q-network algorithm. And, based on the policy change rate and the advantage function, construct a second loss 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.

[0048] Specifically, Figure 3 is a schematic flow diagram of optimizing the configurations of the publisher and the subscriber provided by an embodiment of the present application, as shown in Figure 3 Steps 303 to 306 therein:

[0049] 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.

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

[0051] 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 inability 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 the new logical replication to fail.

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

[0053] 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 a subscriber can participate in simultaneously. max_logical_replication_workers specifies the maximum number of logical replication worker processes that a 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.

[0054] 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.

[0055] Among them, the specific methods of steps 304 and 305 are as follows:

[0056] 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.

[0057] 1. State space:

[0058] To describe the load situation of the PostgreSQL database, define the state vector:

[0059] S t = [CPU_usage, IO_load, WAL_size, active_replication_slots, sync_lag, network_latency];

[0060] The specific variables are as follows:

[0061] 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 (seconds) of the subscriber; network_latency represents the network latency (ms).

[0062] 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 is [70, 50, 200, 5, 2, 50].

[0063] 2. Action space:

[0064] The Deep Q-Network algorithm selects wal_level:

[0065] wal_level = minimal; wal_level = replica; wal_level = logical;

[0066] Different wal_level values affect the logging level and replication function of PostgreSQL. The appropriate wal_level needs to be selected according to the database load situation.

[0067] The proximal policy optimization algorithm adjusts replication slots and worker processes:

[0068] max_replication_slots: Adjust between [1, 20] (continuously adjustable);

[0069] max_worker_processes: Adjust between [1, 32] (continuously adjustable);

[0070] By adjusting these two parameters, the database resources can be reasonably allocated to improve the performance of logical replication.

[0071] 3. Reward function:

[0072] To optimize the performance and stability of logical replication, a reward function is defined:

[0073] R t =α(throughput)-β(sync_lag)-γ(WAL_overflow);

[0074] Where: throughput represents the current logical replication throughput (MB / s), and the goal is to maximize it; sync_lag represents the subscriber replication lag time (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 weights.

[0075] 4. Collect data and construct a state-reward corresponding dataset. Specifically, the system continuously collects the load data of the PostgreSQL server to construct a state-reward corresponding dataset. These data will be used to train the Deep Q-Network algorithm and the proximal policy optimization algorithm.

[0076] 5. Train the Deep Q-Network algorithm to select wal_level:

[0077] Input the current PostgreSQL load state St.

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

[0079] Loss function: L(θ)=(R t +γmaxQ(S t+1 ,a)-Q(S t, a)) 2;

[0080] Among them, L(θ) is the loss function; R t is the reward function; S t is the current PostgreSQL load status; S t+1 is the next moment's PostgreSQL load status; α is the first hyperparameter, and γ is the third hyperparameter;

[0081] Training data: Using the WAL generation rate and replication throughput as Q-value rewards to optimize the wal_level selection strategy.

[0082] 6. Training PPO for parameter dynamic adjustment:

[0083] Policy network π(a|s): Input the PostgreSQL load S t , and output the parameter adjustment actions (max_replication_slots, max_worker_processes).

[0084] Loss function:

[0085] Adopt Clip Loss to avoid gradient explosion: ;

[0086] Among them, is the policy change rate; is the advantage function; is a relatively small constant.

[0087] Training method:

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

[0089] 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.

[0090] 7. After training is completed, the PostgreSQL parameters can be adjusted in real time:

[0091] In one 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.

[0092] Specifically, monitor the PostgreSQL load and collect pg_stat_replication data every 30 seconds. Use the Deep Q-Network algorithm to select the 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 to dynamically expand the replication capacity during high load and reduce resource occupancy during low load.

[0093] 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.

[0094] In an implementation manner of this application, create a publication and a logical replication slot for the database corresponding to the publisher, and generate corresponding identifiers for the publication and the logical replication slot respectively. Determine the log sequence number corresponding to each logical replication slot. Write a recovery configuration file in the subscriber data directory, and use the reference log sequence number in the history as the recovery target. Start the subscriber server, and when the subscriber server recovers to the position of the reference log sequence number, create a subscription for the database corresponding to the subscriber based on the identifier. Set the initial replication progress of the subscription to the position corresponding to the reference log sequence number, and start the subscription.

[0095] Specifically, Figure 4 is a schematic flow diagram of a consistency verification provided for an embodiment of this application, as Figure 4 shown, the consistency verification includes the following steps:

[0096] Step 401: Start the subscriber server and restrict access using temporary parameters.

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

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

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

[0100] 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.

[0101] Step 405: Write the 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 location.

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

[0103] Specifically, only by stopping the recovery at the accurate LSN position can we ensure that the subscriber's data is consistent with the publisher's data before the start of logical replication.

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

[0105] Specifically, wait for the server to complete the recovery process and reach a data-consistent state, 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 normal logical replication operations.

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

[0107] 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.

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

[0109] Specifically, after the subscriber server completes the recovery and reaches a 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 publisher's which publication the subscriber will obtain data from, and tracking the progress of data replication through the corresponding replication slot.

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

[0111] 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.

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

[0113] Step 105: When the verification passes, 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 location in the subscriber database.

[0114] In an implementation manner of the present application, when 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 identifier is determined, and the database connection is established, copy the data corresponding to the publisher database to the corresponding location in the subscriber database.

[0115] In an implementation manner of the present application, when the subscriber uses a physical replication slot, delete the physical replication slot. Also, delete the failover replication slot corresponding to the subscriber. Also, change 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 setting.

[0116] Figure 5 A schematic diagram of the process for cleaning up and finalizing logical replication provided by an embodiment of the present application, as Figure 5 shown in steps 501 to 503 below, the cleaning up and finalizing of logical replication includes the following steps:

[0117] Step 501: If the subscriber previously used a physical replication slot, delete the replication slot.

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

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

[0120] In an implementation manner of the present application, when an exception occurs in any step, clean up the created objects. Also, after the subscriber server resumes, if an exception occurs in any step, send a reminder to the user to recreate the physical standby database.

[0121] Figure 5 A schematic diagram of the process for cleaning up and finalizing logical replication provided by an embodiment of the present application, as Figure 5 shown in steps 504 to 506 below, the cleaning up and finalizing of logical replication includes the following steps:

[0122] Step 504: Stop the subscriber server to complete the setup of logical replication.

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

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

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

[0126] 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 are detected respectively 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 thresholds to determine the data ratio of the indicator data within the preset time-series sliding window that is greater than the indicator thresholds. When the data ratio is greater than the preset data ratio, it is determined that the currently detected database has an anomaly.

[0127] Specifically, the preset anomaly detection model in the embodiments of the present application can identify various patterns and characteristics during 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 conditions of the database in the current state.

[0128] Among them, the training method of the preset anomaly detection model is: using the databases corresponding to the historical publisher and subscriber 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.

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

[0130] 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, covering 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 number of data 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.

[0131] 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 as an abnormality in the database. 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.

[0132] 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 parameters 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 location of the subscriber database.

[0133] 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 location of the subscriber database.

[0134] 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.

[0135] 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, the embodiments of the present application can have various changes and modifications. 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. An intelligent logic processing method based on a database, characterized in that 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 quantity, 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; Performing 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; When the verification passes, copying the data corresponding to the publisher database to the corresponding location of the subscriber database based on the dynamically adjusted parameters, the preset identifier, and the database with the established connection relationship.

2. The intelligent logic processing method based on a database according to claim 1, wherein The receiving user input parameters and initializing the subscriber environment based on the input parameters specifically includes: 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 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; When the detection passes, completing the initialization process.

3. An intelligent logic processing method based on a database according to claim 1, characterized in that The dynamically adjusting the maximum replication slot quantity, 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 specifically includes: Determining the load status based on preset load variables; wherein, the preset load variables at least include one of CPU usage rate, disk I / O load, write-ahead log occupied space, currently used logical replication slot quantity, 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 quantity and the maximum worker 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; Performing dynamic adjustment through the optimized proximal policy optimization algorithm and the optimized deep Q-network algorithm.

4. An intelligent logic processing method based on a database according to claim 3, characterized in that The performing dynamic adjustment through the optimized proximal policy optimization algorithm and the optimized deep Q-network algorithm specifically includes: Sequentially obtaining the system views corresponding to PostgreSQL based on a preset interval time period; Determining logical replication parameters based on the system views; With the optimized proximal policy optimization algorithm, dynamically adjust the maximum replication slot number and the maximum worker process data in the logical replication parameters; And, with the optimized deep Q-network algorithm, dynamically adjust the parameters for controlling the write-ahead logging level in the logical replication parameters.

5. An intelligent logic processing method based on a database according to claim 1, characterized in that, The replication parameter consistency check for the publisher and the subscriber based on the preset identifier specifically includes: Create a publication and a logical replication slot for the database corresponding to the publisher, and generate 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 historical record as the recovery target; Start the subscriber server, and when the subscriber server resumes to the position of the reference log sequence number, create a subscription for the database corresponding to the subscriber based on the identifier; Set the initial replication progress of the subscription to the position corresponding to the reference log sequence number, and start the subscription.

6. An intelligent logic processing method based on a database according to claim 1, characterized in that, 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, delete the physical replication slot; And, delete the failover replication slot corresponding to the subscriber; And, change 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 setting.

7. An intelligent logic processing method based on a database according to claim 5, characterized in that, After starting the subscriber server, the method further includes: Detect key metrics for the databases corresponding to the publisher and the subscriber respectively through a preset anomaly detection model to output key metric scores; wherein, the key metrics include database resource usage data and database internal state parameters; Classify the key metric scores according to the metric type; Sort the key metric scores corresponding to different categories in chronological order to generate a metric data sequence; Compare the metric data within a preset time-series sliding window with the corresponding metric thresholds to determine the proportion of the metric data within the preset time-series sliding window that is greater than the metric threshold; When the data proportion is greater than the preset data proportion, determine that the currently detected database has an anomaly.

8. An intelligent logic processing method based on a database according to claim 5, characterized in that, After receiving the user input parameters, the method further includes: In the case of detecting an anomaly in any step, clean up the created objects; And, after the subscriber server resumes, if an anomaly is detected in any step, send a reminder to the user to recreate the physical standby database.

9. An intelligent logic processing device based on a database, characterized in that The device includes 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-8.

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

Images