A WAL log automatic incremental backup deletion method based on a PostgreSQL database system

Abstract

The application discloses a WAL log automatic incremental backup deletion method based on a PostgreSQL database system, and comprises the following steps: starting the PostgreSQL database to call a deletion script, performing a basic backup on a PostgreSQL data directory of the PostgreSQL database in the form of WAL log archiving backup, copying and saving the archived WAL log under a self-defined backup directory, executing the deletion script, and judging the deletion of the archived WAL log according to the capacity of the self-defined backup directory. The application can delete excessive WAL logs in the WAL log archiving automatic incremental backup process of the PostgreSQL database system and does not lose process data, reduces the situation that the storage capacity of the computer equipment increases too fast, guarantees the effect that no data is lost during the recovery backup, saves manpower, and improves the system working stability and efficiency.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of database, and particularly relates to a WAL log automatic incremental backup deletion method based on a PostgreSQL database system. BACKGROUND

[0002] A management platform (hereinafter referred to as a TMCS server) of a trusted software system uses a PostgreSQL database system as a database for storing data in the background.

[0003] The installation directory of the database PostgreSQL in the background of the TMCS server is preset as / usr / local / pgsql, so the database file storage directory of the PostgreSQL system is / usr / local / pgsql / data (hereinafter referred to as the PostgreSQL data directory), and the WAL log file storage directory of the PostgreSQL system itself is the / usr / local / pgsql / data / pg_wal / directory. It is necessary to configure the configuration file / usr / local / pgsql / data / postgresql.conf of the PostgreSQL database system in advance, and the configuration content includes starting the WAL log archiving, configuring the value of the "archive_command" field, etc. The PostgreSQL database system will automatically make a basic backup (namely a full backup) of the PostgreSQL data directory, and then perform automatic incremental backup in the form of the WAL log archiving strategy.

[0004] However, when the data directory / usr / local / pgsql / data of the database PostgreSQL in the background of the TMCS server performs automatic incremental backup in the form of the WAL log archiving strategy, the capacity of the self-defined backup directory such as the / usr / local / pgsql / tmcs_archivedir / tmcs_database_back directory will continuously grow and be very large, and the phenomenon that the capacity is 6-7 times larger than the PostgreSQL data directory / usr / local / pgsql / data may easily occur. When a large amount of alarm data is transmitted to the TMCS server, the backup directory capacity will grow quickly, and the storage space in the computer device may be quickly occupied. If the storage space in the computer device is occupied, the TMCS service and other part of the system service will not be able to normally run. SUMMARY

[0005] The application aims at overcoming the problem of too fast growth of backup directory capacity when the data directory of the PostgreSQL database is automatically incrementally backed up in the form of WAL log archiving strategy, and proposes a WAL log automatic incremental backup deletion method based on the PostgreSQL database system.

[0006] To achieve the above-mentioned purpose, the application adopts the following technical solutions.

[0007] A WAL log automatic incremental backup deletion method based on the PostgreSQL database system comprises the following steps:

[0008] The PostgreSQL database is started to call the deletion script each time, and the PostgreSQL data directory of the PostgreSQL database is once base backed up in the form of WAL log archiving backup, while the file of the last base backup success is stored, the file of the earlier base backup success is deleted, the WAL log of the archiving backup is copied and saved under the custom backup directory, the deletion script is executed, it is judged whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archiving backup, when the capacity of the custom backup directory is less than or equal to 2 times the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are retained, and the archiving incremental backup of the WAL log file is continuously performed; when the capacity of the custom backup directory is greater than 2 times the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are deleted, and the PostgreSQL database after change is once base backed up in the form of WAL log archiving backup.

[0009] Further, when the backup data of the TMCS server is restored, the whole is restored according to the base backup file of the latest backup and the incremental backup file of the WAL log archiving.

[0010] Further, the PostgreSQL database system automatically calls and executes the deletion script, and the PostgreSQL data directory of the PostgreSQL database is once base backed up in the form of WAL log archiving backup each time the deletion script is called.

[0011] Further, the deletion script is installed under the installation directory of the PostgreSQL database system.

[0012] Further, the value of the deletion field in the configuration file of the PostgreSQL database system is configured to point to the storage path of the deletion script, and the deletion field is automatically executed according to the configuration file.

[0013] A WAL log automatic incremental backup deletion device based on a PostgreSQL database system, comprising:

[0014] An information initial module, which starts a PostgreSQL database to call a deletion script, performs a basic backup on a PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, stores a file of a last basic backup success, and deletes a file of a basic backup success before the last basic backup;

[0015] An information judgment module, which is configured to acquire whether a capacity of a custom backup directory is greater than a capacity of a WAL log directory of the archive backup;

[0016] An information processing module, which is configured to, when the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retain WAL log files in the custom backup directory and in a WAL log directory of the PostgreSQL database system, and continue to perform an archive incremental backup of the WAL log files; and when the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, delete the WAL log files in the custom backup directory and in the WAL log directory of the PostgreSQL database system, and perform a basic backup on a changed PostgreSQL database in the form of the WAL log archive backup.

[0017] A database log processing system, comprising devices for performing each step of the method.

[0018] An electronic device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method when executing the computer program.

[0019] A computer readable storage medium, which stores a computer program, wherein the computer program is executable on a processor to implement the method.

[0020] A computer program product, comprising a computer program / instruction, wherein the computer program / instruction is executable on a processor to implement the method.

[0021] Compared with the prior art, the application has the following beneficial technical effects:

[0022] The WAL log automatic incremental backup deletion method based on the PostgreSQL database system provided by the application can delete excessive WAL logs in the WAL log archiving automatic incremental backup process of the PostgreSQL database system and does not lose process data by calling and executing a deletion script, the deletion script is executed to judge the deletion of the archived WAL logs according to the capacity of a self-defined backup directory, the deletion script can be used to further select and delete excessive archived WAL logs according to the capacity, the WAL logs are copied and saved in the self-defined backup directory in the form of a basic backup of the PostgreSQL data directory of the PostgreSQL database, the maintenance of the basic backup ensures that the deleted excessive archived WAL logs do not lose data when recovery is needed, the application reduces the situation that the storage capacity of the computer device increases too fast and ensures that no data is lost when the backup is recovered, manpower is saved, and the system working stability and efficiency are improved.

[0023] The WAL log automatic incremental backup deletion method based on the PostgreSQL database system provided by the application is helpful for the data of the TMCS server to be backed up at any time, does not occupy too much hard disk storage space, and can ensure that the data of the TMCS service is not lost due to the deletion of excessive WAL logs when the TMCS server needs to be recovered or migrated. BRIEF DESCRIPTION OF DRAWINGS

[0024] The accompanying drawings, which are included to provide a further understanding of the application and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application. The detailed description of the application serves to explain the application and is not intended to limit the application.

[0025] Figure 1 The flowchart of the WAL log automatic incremental backup deletion method based on the PostgreSQL database system.

[0026] Figure 2 The structural diagram of the WAL log automatic incremental backup deletion device based on the PostgreSQL database system.

[0027] Figure 3 The electronic device diagram of the WAL log automatic incremental backup deletion method based on the PostgreSQL database system.

[0028] Figure 4The execution flow chart of a WAL log automatic incremental backup deletion method based on a PostgreSQL database system in an embodiment of the present application. DETAILED DESCRIPTION

[0029] The present application will be described in detail below with reference to the accompanying drawings and embodiments. It should be noted that the embodiments in the present application and the features in the embodiments can be combined with each other without conflict.

[0030] The following detailed description is exemplary and is intended to provide further explanation of the present application. Unless otherwise defined, all technical terms used in the present application have the same meanings as commonly understood by one of ordinary skill in the art to which the present application belongs. The terms used in the present application are only intended to describe the specific embodiments of the present application, and are not intended to limit the exemplary embodiments according to the present application.

[0031] Embodiment one

[0032] Reference Figure 1 A WAL log automatic incremental backup deletion method based on a PostgreSQL database system includes the following steps:

[0033] The PostgreSQL database is started to call a deletion script, a PostgreSQL data directory of the PostgreSQL database is once backed up in the form of WAL log archiving backup, a file of a last successful basic backup is stored, a file of a successful basic backup before the last basic backup is deleted, the WAL log of the archiving backup is copied and saved in a custom backup directory, the deletion script is executed, it is judged whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archiving backup, when the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are retained, and the archiving incremental backup of the WAL log file is continued; when the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are deleted, and the PostgreSQL database after the change is once backed up in the form of WAL log archiving backup.

[0034] WAL, short for Write-Ahead Logging, is a log system where changes to data files (such as tables and indexes) are first recorded in a log file before being written to the data files. This allows data to be restored to a consistent state before the failure even if the database fails. WAL logs can be used to restore a consistent database state in the event of a system crash or unexpected shutdown. They ensure transaction integrity, either fully committing or not committing at all. Using WAL reduces the number of direct disk writes because only the log file needs to be flushed to disk, not every data file modified by a transaction. Whenever the database performs a modification operation such as INSERT, UPDATE, or DELETE, the log of these operations is written to the WAL buffer. The WAL write process then writes these log records from the buffer to the WAL log file. When the WAL log file is full, it automatically switches to the next log file to continue writing.

[0035] PostgreSQL supports dynamically switching WAL log files to ensure log continuity and integrity. In PostgreSQL's master-slave replication architecture, WAL logs are used to achieve real-time data synchronization. The master sends the generated WAL logs to the slave, which maintains data consistency with the master by replaying these logs. Combined with archived WAL logs, PostgreSQL supports point-in-time recovery, allowing users to restore the database to a specific point in time.

[0036] PostgreSQL is a powerful and feature-rich object-relational database management system (ORDBMS). It employs a client / server model, where clients connect to the server over a network, send query requests, and receive query results. PostgreSQL's storage system consists of multiple sub-modules, including page management and buffer management sub-modules, responsible for accessing and manipulating physical data. PostgreSQL supports ACID transaction processing, ensuring data consistency and integrity.

[0037] Delete the script and perform the following operations:

[0038] If the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retain the WAL log files in the custom backup directory and the WAL log files in the PostgreSQL database system's WAL log directory, and continue with the incremental backup of the WAL log files.

[0039] When the capacity of the custom backup directory is determined to be greater than twice the capacity of the PostgreSQL data directory, the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system are deleted, and a basic backup of the changed PostgreSQL database is performed in the form of a WAL log archive backup.

[0040] The data directory refers to one or more folders or storage areas used to store data files, databases, application data, etc. The directory capacity here refers to the total amount of data that the data directory can store, i.e. the size of its storage space. For a simple directory structure, the total capacity can be calculated by directly adding up the sizes of all files and subdirectories under the directory. For a complex directory structure, a recursive algorithm is needed to traverse all subdirectories and files and add up their sizes. Various file management tools or scripting languages such as Python, Shell, etc. can also be used to write scripts to assist in calculating the directory capacity.

[0041] When restoring the backup data of the TMCS server, the latest backup of the basic backup file and the incremental backup file of the WAL log archive are used for overall recovery.

[0042] WAL (Write-Ahead Logging) log archive incremental backup is a common backup strategy in database systems such as PostgreSQL, which relies on WAL logs to record all changes in the database, allowing the database to be restored to any time point from a certain reference point. In PostgreSQL, incremental backup is usually implemented by retaining all WAL logs since the last full backup. Therefore, once WAL log archiving is configured, every new data change will be automatically recorded in the WAL log without the need to perform additional incremental backup commands. During incremental backup, all newly generated WAL logs are archived to the configured archive directory. These WAL logs need to be backed up to a secure storage location regularly to prevent data loss due to local storage failure. When the database needs to be restored, the latest full backup is first restored.

[0043] The TMCS (Technical Monitoring and Control System) server is an important part of the FRQ technical monitoring system. It is mainly used to implement system configuration parameters and software management (including software download), generate system logs and reports (system running status and current faults), fault diagnosis, and collect static data reflecting system activities. The TMCS server can manage system configuration parameters, including device settings and network configuration, to ensure that the system runs according to the predetermined parameters. It supports software management, including software download, update, and version control, to ensure the timeliness and security of system software. It can generate detailed system logs and reports to record system running status and fault information, providing a basis for troubleshooting and system optimization. By analyzing system logs and real-time data, it can diagnose system faults and provide corresponding processing suggestions. It collects static data of system activities, such as device status and communication records, to support system performance analysis and optimization. The TMCS server usually adopts the Client / Server architecture, where the server handles requests from the client, performs corresponding operations, and returns the results to the client. PostgreSQL supports multiple data types and indexing methods, providing efficient data storage and access capabilities for the TMCS server. For high-frequency access scenarios such as monitoring data, B-tree indexing or GiST indexing can be used to optimize query performance. Meanwhile, PostgreSQL supports partitioned table functions, which can split large tables into multiple small tables to improve query and maintenance efficiency. PostgreSQL provides various backup and recovery strategies, such as physical backup and logical backup. The TMCS server can perform data backup regularly according to actual needs, and quickly recover data in case of system failure or data loss, reducing the risk of business interruption.

[0044] The PostgreSQL database system automatically calls the deletion script, and each call to the deletion script performs a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup. The deletion script is installed in the installation directory of the PostgreSQL database system. The value of the deletion field in the configuration file of the PostgreSQL database system is configured to point to the storage path of the deletion script, and the deletion field is automatically executed according to the configuration file.

[0045] In order to facilitate understanding of this embodiment, the following steps in the above process are described in detail.

[0046] 1) Configure WAL archive

[0047] First, you need to enable WAL archiving in `postgresql.conf` by setting `archive_mode` to `on` and specifying `archive_command` to define how to process WAL files.

[0048] bash

[0049] archive_mode = on

[0050] archive_command = 'test ! -f / custom backup directory / %f&&cp %p / path / to / archive / %f'

[0051] ```

[0052] Here, ` / custom backup directory / ` should be the location where you want to store the WAL files.

[0053] 2) Implement incremental backup

[0054] Incremental backups can be achieved using the `pg_basebackup` tool in conjunction with WAL logs. After the initial base backup, each subsequent backup only needs to back up newly added WAL files. This can be accomplished using a script.

[0055] This script first checks if a basic backup already exists; if not, it performs a basic backup. Then, it synchronizes the new WAL file to a custom backup directory.

[0056] 3) Automated deletion of old WAL files

[0057] Copy the archived WAL logs to a custom backup directory. Execute a deletion script to determine if the capacity of the custom backup directory is greater than the capacity of the archived WAL log directory. If the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retain the WAL log files in both the custom backup directory and the PostgreSQL database system's WAL log directory, and continue with incremental archive backups of the WAL log files. If the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, delete both the WAL log files in the custom backup directory and the PostgreSQL database system's WAL log directory, and perform a basic backup of the changed PostgreSQL database according to the WAL log archive backup format.

[0058] Example 2

[0059] See Figure 2The embodiment of the application provides a WAL log automatic incremental backup deleting device based on a PostgreSQL database system, which comprises the following steps:

[0060] An information initial module is used for starting the PostgreSQL database to call a deleting script, performing a basic backup on a PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, storing a file of a last basic backup success, and deleting a file of a basic backup success before the last basic backup;

[0061] An information judging module is used for judging whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup;

[0062] An information processing module is used for judging that when the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system are retained, and the archive incremental backup of the WAL log files is continued; and judging that when the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system are deleted, and a basic backup is performed on the changed PostgreSQL database in the form of the WAL log archive backup.

[0063] Embodiment three

[0064] A database log processing system comprises devices used for performing various steps of the method.

[0065] The method comprises the following steps: starting a PostgreSQL database, calling a deletion script each time, performing a basic backup on a PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, storing a last basic backup success file, deleting an earlier basic backup success file, copying and saving the archive backup WAL log in a custom backup directory, executing the deletion script, judging whether the capacity of the custom backup directory is greater than the capacity of the archive backup WAL log directory, judging whether the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retaining the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and continuing to perform an archive incremental backup of the WAL log file; judging whether the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, deleting the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and performing a basic backup on the changed PostgreSQL database in the form of a WAL log archive backup.

[0066] The deletion script performs the following operations:

[0067] judging whether the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retaining the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and continuing to perform an archive incremental backup of the WAL log file.

[0068] judging whether the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, deleting the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and performing a basic backup on the changed PostgreSQL database in the form of a WAL log archive backup.

[0069] Embodiment four

[0070] Reference Figure 3An electronic device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the WAL log automatic incremental backup deletion method based on the PostgreSQL database system when executing the computer program. Wherein the method comprises the following steps: starting the PostgreSQL database each time the deletion script is called, performing a basic backup on the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, storing the last basic backup success file, deleting the earlier basic backup success file, copying and saving the WAL log archive backup in the custom backup directory, executing the deletion script, judging whether the capacity of the custom backup directory is greater than the capacity of the WAL log archive backup directory, when the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retaining the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and continuing to perform the archive incremental backup of the WAL log file; when the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, deleting the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system, and performing a basic backup on the changed PostgreSQL database in the form of WAL log archive backup.

[0071] The deletion script performs the following operations:

[0072] When the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are retained, and the archive incremental backup of the WAL log file is continued.

[0073] When the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, the WAL log file in the custom backup directory and the WAL log file in the WAL log directory of the PostgreSQL database system are deleted, and a basic backup is performed on the changed PostgreSQL database in the form of WAL log archive backup.

[0074] Embodiment five

[0075] A computer readable storage medium, the computer readable storage medium stores a computer program, the computer program is executed by a processor to realize the WAL log automatic incremental backup deletion method based on PostgreSQL database system. Wherein, the method comprises the following steps: starting PostgreSQL database each time to call the script, the PostgreSQL data directory of PostgreSQL database is stored according to the form of WAL log archive backup once a basic backup, at the same time, the file of last basic backup success is stored, the earlier basic backup success file is deleted, the WAL log of archive backup is copied and saved in the custom backup directory, the script is executed, whether the capacity of custom backup directory is greater than the capacity of WAL log archive, when the capacity of custom backup directory is less than or equal to 2 times the capacity of PostgreSQL data directory, the WAL log file in custom backup directory and the WAL log file in WAL log directory of PostgreSQL database system are reserved, and the archive incremental backup of WAL log file is continued; when the capacity of custom backup directory is greater than 2 times the capacity of PostgreSQL data directory, the WAL log file in custom backup directory and the WAL log file in WAL log directory of PostgreSQL database system are deleted, and the PostgreSQL database after change is stored according to the form of WAL log archive backup once a basic backup.

[0076] The script performs the following operations:

[0077] When the capacity of custom backup directory is less than or equal to 2 times the capacity of PostgreSQL data directory, the WAL log file in custom backup directory and the WAL log file in WAL log directory of PostgreSQL database system are reserved, and the archive incremental backup of WAL log file is continued.

[0078] When the capacity of custom backup directory is greater than 2 times the capacity of PostgreSQL data directory, the WAL log file in custom backup directory and the WAL log file in WAL log directory of PostgreSQL database system are deleted, and the PostgreSQL database after change is stored according to the form of WAL log archive backup once a basic backup.

[0079] Embodiment six

[0080] The computer program product comprises computer programs / instructions which, when executed by a processor, implement the WAL log automatic incremental backup deletion method based on the PostgreSQL database system. The method comprises the following steps: starting the PostgreSQL database to call the deletion script each time, performing a basic backup on the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, storing the last basic backup success file, deleting the earlier basic backup success file, copying and saving the archived WAL log in a custom backup directory, executing the deletion script, judging whether the capacity of the custom backup directory is greater than the capacity of the WAL log archive backup directory, judging whether the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retaining the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and continuing to perform the archive incremental backup of the WAL log files; judging whether the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, deleting the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and performing a basic backup on the changed PostgreSQL database in the form of WAL log archive backup.

[0081] The deletion script performs the following operations:

[0082] judging whether the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retaining the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and continuing to perform the archive incremental backup of the WAL log files.

[0083] judging whether the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, deleting the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and performing a basic backup on the changed PostgreSQL database in the form of WAL log archive backup.

[0084] Embodiment Seven

[0085] The embodiment provides a WAL log automatic incremental backup deletion method based on a PostgreSQL database system, is realized through a script (the script name is tmcs_archivecleanup_script.sh), is simple and easy to operate, is placed in the installation directory / usr / local / pgsql / of the PostgreSQL system, and the value of the "archive_command" field in the configuration file / usr / local / pgsql / data / postgresql.conf of the PostgreSQL database system needs to be configured to point to the path where the script tmcs_archivecleanup_script.sh is stored, such as "archive_command = ' / usr / local / pgsql / tmcs_archivecleanup_script.sh %f %p'", wherein archive_command is set to an archive command, is used to specify how to copy the WAL segment file to the archive storage when the WAL segment file is filled; %p represents the path of the WAL file, and %f represents the name of the WAL file.

[0086] The script tmcs_archivecleanup_script.sh scheme is that: each execution carries out the archive incremental backup operation of the WAL log file.

[0087] When the capacity of the backup directory is too large and greater than twice the capacity of the PostgreSQL data directory / usr / local / pgsql / data, the outdated WAL log archive files in the pg_wal directory and the self-defined directory are first cleaned, and then a basic backup operation of the PostgreSQL data directory / usr / local / pgsql / data is carried out;

[0088] When the capacity of the backup directory is less than or equal to twice the capacity of the PostgreSQL data directory / usr / local / pgsql / data, only the archive incremental backup operation of the WAL log file is carried out.

[0089] Referring to Figure 4 , the specific process is as follows:

[0090] The script tmcs_archivecleanup_script.sh is created to make the PostgreSQL database perform a basic backup, i.e. a full backup, in the form of a WAL log archive backup, for the first time after the database is deployed, and then automatically perform according to the configuration information in the configuration file / usr / local / pgsql / data / postgresql.conf, such as starting the WAL log archive, the value of the "archive_command" field, the method of automatically calling the script to perform the script, and the WAL log archive operation is performed each time.

[0091] After the method of the script tmcs_archivecleanup_script.sh is called to perform, it is judged that the capacity of the automatic backup directory / usr / local / pgsql / tmcs_archivedir / tmcs_database_back is less than or equal to twice the capacity of the PostgreSQL data directory / usr / local / pgsql / data, and the basic backup operation of the PostgreSQL data directory / usr / local / pgsql / data is not performed again, the WAL log files in the backup directory / usr / local / pgsql / tmcs_archivedir / tmcs_database_back and the WAL log files in the WAL log directory / usr / local / pgsql / data / pg_wal of the PostgreSQL system itself are not cleaned up, and only the incremental backup of the WAL log files is performed. When the data of the TMCS server needs to be restored, it can be ensured that the data is not lost.

[0092] The method of the script TMCS_archivecleanup_script.sh is called to execute, and when the capacity of the automatic backup directory / usr / local / pgsql / tmcs _archivedir / tmcs _database_back is greater than twice the capacity of the PostgreSQL data directory / usr / local / pgsql / data, the WAL log files in the backup directory / usr / local / pgsql / tmcs _archivedir / tmcs _database_back and the WAL log files in the WAL log directory / usr / local / pgsql / data / pg_wal / of the PostgreSQL system itself are cleaned up, and then the basic backup operation of the PostgreSQL data directory / usr / local / pgsql / data is performed again, that is, a full backup of the changed PostgreSQL database is performed in the form of WAL log archiving backup, which greatly reduces the capacity of the backup directory / usr / local / pgsql / tmcs _archivedir / tmcs _database_back and the capacity of the PostgreSQL data directory / usr / local / pgsql / data. When restoring the backup data of the TMCS server, the latest basic backup file, that is, the full backup file and the incremental backup file of the WAL log archiving are used for overall recovery, which can guarantee the overall recovery of the data of the TMCS server.

[0093] Those skilled in the art will appreciate that embodiments of the application can be provided as methods, systems, or computer program products. Accordingly, the application can be embodied in the form of complete hardware embodiments, complete software embodiments, or embodiments combining software and hardware aspects. Furthermore, the application can be embodied in the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk memory, CD-ROMs, optical memory, etc.) having computer usable program code embodied thereon.

[0094] The present application is described in reference to the flowcharts and / or block diagrams according to the embodiments of the present application. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, and the combination of the flows and / or blocks in the flowcharts and / or block diagrams can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing apparatus to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing apparatus produce a machine that implements the flowcharts and / or block diagrams. Figure 1one or more processes and / or blocks Figure 1 an apparatus for performing the functions specified in the flowchart or multiple flows and / or blocks.

[0095] These computer program instructions can also be stored in a computer readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer readable memory produce an article of manufacture including instructions which implement the flowchart or multiple flows and / or blocks. Figure 1 one or more processes and / or blocks Figure 1 an apparatus for performing the functions specified in the flowchart or multiple flows and / or blocks.

[0096] These computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the flowchart or multiple flows and / or blocks. Figure 1 one or more processes and / or blocks Figure 1 an apparatus for performing the functions specified in the flowchart or multiple flows and / or blocks.

[0097] Finally, it should be noted that the above examples are merely used to illustrate the technical solutions of the present application but not to limit the protection scope thereof, and although the present application has been described in detail with reference to the above examples, those of ordinary skill in the art should understand that the technical personnel in the art can make various changes, modifications or equivalent replacements to the specific embodiments of the present application after reading the present application, but these changes, modifications or equivalent replacements are all within the protection scope of the claims of the present application to be approved.

Claims

1. A method for automatic incremental backup and deletion of WAL logs based on a PostgreSQL database system, characterized in that, Includes the following steps: Start the PostgreSQL database and call the deletion script. Perform a basic backup of the PostgreSQL database's data directory in the form of WAL log archive backup. At the same time, store the files that were successfully backed up in the previous basic backup, delete the files that were successfully backed up in the previous basic backup, and copy and save the archived WAL logs in a custom backup directory. Execute the deletion script to determine if the capacity of the custom backup directory is greater than the capacity of the archived backup WAL log directory. If the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, retain the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and continue to perform incremental archived backups of the WAL log files. When it is determined that the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, delete the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and perform a basic backup of the changed PostgreSQL database in the form of WAL log archive backup. When restoring backup data of the Trusted Management Platform server TMCS database, the overall restoration is performed based on the latest basic backup file and the incremental backup file of the WAL log archive. The PostgreSQL database system automatically calls and executes the deletion script. Each time the deletion script is called, a basic backup of the PostgreSQL database's data directory is performed in the form of a WAL log archive backup. The basic backup is a full backup, and the incremental backup is achieved by retaining all WAL logs since the last full backup. During the incremental backup, all newly generated WAL logs are archived to the configured archive directory. When the database needs to be restored, the most recent full backup is restored first.

2. The method for automatic incremental backup and deletion of WAL logs based on a PostgreSQL database system according to claim 1, characterized in that, The deletion script is installed in the installation directory of the PostgreSQL database system.

3. The method for automatic incremental backup and deletion of WAL logs based on a PostgreSQL database system according to claim 1, characterized in that, In the configuration file of the PostgreSQL database system, the value of the delete field is configured to point to the storage path of the delete script, and the delete field is executed automatically according to the configuration file.

4. An automatic incremental backup and deletion device for WAL logs based on a PostgreSQL database system, characterized in that, include: The initial information module starts the PostgreSQL database and calls the deletion script. It performs a basic backup of the PostgreSQL database's data directory in the form of WAL log archive backup, and stores the files that were successfully backed up in the previous basic backup. It also deletes the files that were successfully backed up in the previous basic backup. The information judgment module is used to determine whether the capacity of the custom backup directory is greater than the capacity of the archived backup WAL log directory. The information processing module is used to determine that when the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory, it retains the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and continues to perform incremental backup of the WAL log files. When it is determined that the capacity of the custom backup directory is greater than twice the capacity of the PostgreSQL data directory, delete the WAL log files in the custom backup directory and the WAL log files in the WAL log directory of the PostgreSQL database system, and perform a basic backup of the changed PostgreSQL database in the form of WAL log archive backup. The backup and recovery module is used to perform overall recovery based on the latest basic backup file and the incremental backup file of the WAL log archive when restoring the backup data of the Trusted Management Platform server TMCS database; The module for calling and executing deletion scripts is used by the PostgreSQL database system to automatically call and execute deletion scripts. Each time the deletion script is called, a basic backup of the PostgreSQL data directory of the PostgreSQL database is performed in the form of WAL log archive backup. The basic backup is a full backup, and the incremental backup is achieved by retaining all WAL logs since the last full backup. During the incremental backup, all newly generated WAL logs are archived to the configured archive directory. When the database needs to be restored, the most recent full backup is restored first.

5. A database log processing system, characterized in that, Includes apparatus for performing the steps of a method for automatic incremental backup and deletion of WAL logs based on a PostgreSQL database system as described in any one of claims 1-3.

6. An electronic device, characterized in that, The system includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the automatic incremental backup and deletion method for WAL logs based on a PostgreSQL database system as described in any one of claims 1-3.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the automatic incremental backup and deletion method for WAL logs based on a PostgreSQL database system as described in any one of claims 1-3.

8. A computer program product comprising a computer program / instructions, characterized in that, When executed by a processor, the computer program / instruction implements the automatic incremental backup and deletion method for WAL logs based on a PostgreSQL database system as described in any one of claims 1-3.

Images