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

Abstract

The invention discloses a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system, which comprises the following steps: starting a PostgreSQL database to call a deletion script, carrying out primary basic backup on a PostgreSQL data directory of the PostgreSQL database according to a WAL log archiving backup form, copying and storing the archived and backed-up WAL log in a self-defined backup directory, and deleting the WAL log in the self-defined backup directory. And executing the deletion script, and judging to delete the archived and backed-up WAL log according to the capacity of the self-defined backup directory. According to the method, the deletion script is called and executed, so that excessive WAL logs can be deleted in the WAL log archiving and automatic incremental backup process of the PostgreSQL database system without losing process data, the situation that the storage capacity of computer equipment is increased too fast is reduced, the effect of not losing any data during backup recovery is ensured, manpower is saved, and the efficiency is improved. And the working stability and efficiency of the system are improved.

Description

Technical Field

[0001] The invention belongs to the technical field of databases, and in particular relates to a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system. Background Art

[0002] The management platform of the trusted software system (hereinafter referred to as the TMCS server) uses the PostgreSQL database system as the database for backend data storage.

[0003] The installation directory of the PostgreSQL database of the TMCS server backend is preset to / 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 / usr / local / pgsql / data / pg_wal / directory. You need to first configure the configuration file / usr / local / pgsql / data / postgresql.conf of the PostgreSQL database system, including enabling WAL log archiving and configuring the value of the "archive_command" field. The PostgreSQL database system will automatically make a basic backup (i.e., full backup) of the PostgreSQL data directory, and then perform automatic incremental backup in the form of WAL log archiving strategy.

[0004] However, when the data directory / usr / local / pgsql / data of the PostgreSQL database in the background of the TMCS server is automatically backed up in the form of WAL log archiving strategy, the capacity of the custom backup directory such as / usr / local / pgsql / tmcs_archivedir / tmcs _database_back will continue to grow and become very large, and it is easy to be 6 or 7 times larger than the capacity of the PostgreSQL data directory / usr / local / pgsql / data. When a large amount of alarm data is transmitted to the TMCS server, the capacity of the backup directory will grow rapidly, and there is a possibility that the storage space in the computer device will be quickly filled. If the storage space in the computer device is filled, the TMCS service and some other system services will not be able to operate normally. Summary of the invention

[0005] The purpose of the present invention is to overcome the problem that the backup directory capacity grows too fast when the data directory of the database PostgreSQL is automatically incrementally backed up in the form of a WAL log archiving strategy, and proposes a WAL log automatic incremental backup deletion method based on the PostgreSQL database system.

[0006] In order to achieve the above object, the present invention adopts the following technical scheme: A method for automatically deleting WAL log incremental backup based on a PostgreSQL database system comprises the following steps: Each time the deletion script is called when the PostgreSQL database is started, a basic backup is performed on the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, and the files of the previous successful basic backup are stored, and the files of the earlier successful basic backup are deleted. The WAL log copy of the archive backup is saved in the custom backup directory, and the deletion script is executed to determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup. When it is determined that 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 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 continues; when it is determined that the capacity of the custom backup directory is greater than 2 times 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 WAL log archive backup.

[0007] Furthermore, when restoring the backup data of the TMCS server, overall restoration is performed according to the latest basic backup file and the incremental backup file archived by the WAL log.

[0008] Furthermore, the PostgreSQL database system automatically calls and executes the deletion script, and each time the deletion script is called, a basic backup is performed on the PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup.

[0009] Furthermore, a removal script is installed in the installation directory of the PostgreSQL database system.

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

[0011] A WAL log automatic incremental backup and deletion device based on a PostgreSQL database system, comprising: The information initialization module starts the PostgreSQL database and calls the deletion script. It performs a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, stores the files of the last successful basic backup, and deletes the files of the successful basic backup before the last basic backup. An information judgment module, wherein the information acquisition module is used to determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup; The information processing module is used to 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 archive incremental backup of the WAL log files when it is determined that the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory; 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.

[0012] A database log processing system includes a device for executing each step of a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0013] An electronic device comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the method for automatically deleting WAL logs based on a PostgreSQL database system is implemented.

[0014] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the computer program implements a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0015] A computer program product includes a computer program / instruction. When the computer program / instruction is executed by a processor, the computer program / instruction implements a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0016] Compared with the prior art, the present invention has the following beneficial technical effects: The invention provides a WAL log automatic incremental backup deletion method based on a PostgreSQL database system. The invention calls and executes a deletion script to delete excessive WAL logs in the WAL log archiving automatic incremental backup process of the PostgreSQL database system without losing process data. The deletion script is executed to judge and delete the archived backup WAL log according to the capacity of a custom backup directory. The deletion script can be used to further select and delete the excessive archived backup WAL log by judging the capacity. In addition, a basic backup is performed on the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archiving backup, and a copy of the archived backup WAL log is saved in the custom backup directory. With the maintenance of the basic backup, the deleted excessive archived backup WAL log does not lose data when it needs to be restored. This not only reduces the situation that the storage capacity of the computer device increases too fast, but also ensures the effect of not losing any data when restoring the backup, thus saving manpower and improving the working stability and efficiency of the system.

[0017] The invention proposes a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system, which helps to back up the data of a TMCS server at all times and does not occupy too much hard disk storage space. When the TMCS server needs to be restored or migrated, it can also ensure that the data of the TMCS service will not be lost due to deleting too many WAL logs. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings in the specification are used to provide further understanding of the present invention and constitute a part of the present invention. The schematic embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations on the present invention.

[0019] Figure 1 The present invention is a flow chart of a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0020] Figure 2 The present invention is a structural diagram of a WAL log automatic incremental backup and deletion device based on a PostgreSQL database system.

[0021] Figure 3 The present invention is an electronic device diagram of a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0022] Figure 4 The present invention is a flowchart of an automatic incremental backup and deletion method of a WAL log based on a PostgreSQL database system in an embodiment of the present invention. DETAILED DESCRIPTION

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

[0024] The following detailed description is an exemplary description, which is intended to provide further detailed description of the present invention. Unless otherwise specified, all technical terms used in the present invention have the same meaning as those generally understood by those skilled in the art to which the present application belongs. The terms used in the present invention are only for describing specific embodiments, and are not intended to limit exemplary embodiments according to the present invention.

[0025] Embodiment 1 See also Figure 1 , a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system, comprising the following steps: Start the PostgreSQL database and call the deletion script. Perform a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup. Meanwhile, store the files of the last successful basic backup, delete the files of the successful basic backup before the last basic backup, save the WAL log copy of the archive backup in the custom backup directory, execute the deletion script, determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup, and when it is determined that the capacity of the custom backup directory is less than or equal to 2 times 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 the archive incremental backup of the WAL log files; when it is determined that the capacity of the custom backup directory is greater than 2 times 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.

[0026] WAL log, full name Write-Ahead Logging, is a pre-write log. Its core idea is that changes to data files (such as tables and indexes) must be recorded in log files first and then written to data files. In this way, even if the database fails, the data can be restored to the consistency state before the failure by replaying the WAL log. When the database system crashes or stops unexpectedly, the WAL log can be used to restore the consistency state of the database. Through the WAL log, the integrity of the transaction is ensured, either fully committed or not committed at all. Using WAL can reduce the number of direct disk writes, because only the log file needs to be refreshed to the disk, without refreshing each data file modified by the transaction. Whenever the database performs modification operations, such as INSERT, UPDATE, and DELETE, the logs of these operations are written to the WAL buffer. Subsequently, the WAL writing process writes these log records from the buffer to the WAL log file. When the WAL log file is full, it will automatically switch to the next log file to continue writing.

[0027] PostgreSQL supports dynamic switching of WAL log files to ensure the continuity and integrity of logs. In the master-slave replication architecture of PostgreSQL, WAL logs are used to achieve real-time data synchronization. The master database sends the generated WAL logs to the slave database, which replays these logs to maintain data consistency with the master database. Combined with archived WAL logs, PostgreSQL supports point-in-time recovery, allowing users to restore the database to a specific point in the past.

[0028] The PostgreSQL database system is a powerful and feature-rich object-relational database management system (ORDBMS). PostgreSQL uses a client / server model. The client connects to the server through the network, sends query requests and receives query results. The PostgreSQL storage system consists of multiple sub-modules, including the page management sub-module and the buffer management sub-module, which are responsible for accessing and operating physical data. PostgreSQL supports ACID transaction processing to ensure data consistency and integrity.

[0029] The removal script does the following: When it is determined that 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 archived incremental backup of the WAL log files continues.

[0030] When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0031] A data directory refers to one or more folders or storage areas used to store data files, databases, application data, etc. Directory capacity here refers to the total amount of data that a data directory can store, that is, 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. You can also use scripts written in various file management tools or scripting languages, such as Python, Shell, etc., to assist in calculating directory capacity.

[0032] When restoring the backup data of the TMCS server, perform overall restoration according to the latest basic backup file and the incremental backup file of the WAL log archive.

[0033] Incremental backup with WAL (Write-Ahead Logging) log archiving is a common backup strategy in database systems such as PostgreSQL. It relies on WAL logs to record all changes in the database, allowing the database to be restored from a certain reference point to any point in time. In PostgreSQL, incremental backups are usually implemented by retaining all WAL logs since the last full backup. Therefore, once WAL log archiving is configured, each new data change is automatically recorded in the WAL log without executing additional incremental backup commands. During incremental backups, all newly generated WAL logs are archived to the configured archive directory. These WAL logs need to be backed up regularly to a safe storage location to prevent data loss due to local storage failures. When you need to restore a database, restore the most recent full backup first.

[0034] 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 operation status and current faults), fault diagnosis, and static data collection reflecting system activities. The TMCS server can manage system configuration parameters, including device settings, network configuration, etc., 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 operation 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 give corresponding processing suggestions. It collects static data of system activities, such as device status, communication records, etc., to provide support for system performance analysis and optimization. The TMCS server usually adopts a Client / Server architecture, in which the server is responsible for processing requests from the client, performing corresponding operations, and returning 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, you can use B-tree indexes or GiST indexes to optimize query performance. At the same time, PostgreSQL also supports partitioned tables, which can split large tables into multiple small tables to improve query and maintenance efficiency. PostgreSQL provides a variety of backup and recovery strategies, such as physical backup and logical backup. The TMCS server can perform regular data backups based on actual needs, and quickly restore data in the event of system failure or data loss to reduce the risk of business interruption.

[0035] The PostgreSQL database system automatically calls and executes the deletion script. 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 deletion script is installed in the installation directory of the PostgreSQL database system. In the configuration file of the PostgreSQL database system, the value of the deletion field is configured to point to the storage path of the deletion script, and the deletion field is automatically executed according to the configuration file.

[0036] To facilitate understanding of this embodiment, the following steps in the above process are described in detail.

[0037] 1). Configure WAL archiving First, you need to enable WAL archiving in postgresql.conf, set archive_mode to on, and specify archive_command to define how to handle WAL files.

[0038] ```bash archive_mode = on archive_command = 'test ! -f / custom backup directory / %f&&cp %p / path / to / archive / %f' ``` The / custom-backup-directory / here should be where you want to store the WAL files.

[0039] 2). Implement incremental backup Incremental backups can be done by using the pg_basebackup tool in conjunction with WAL logs. After the first base backup, only the newly added WAL files need to be backed up each time. This can be done with a script.

[0040] This script first checks if a base backup already exists, and if not, performs a base backup. It then syncs new WAL files to a custom backup directory.

[0041] 3). Automatically delete old WAL files The WAL log copy of the archived backup is saved in the custom backup directory, and the deletion script is executed to determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archived backup. When it is determined that 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 archived incremental backup of the WAL log files continues; when it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0042] Embodiment 2 See also Figure 2 The embodiment of the present application provides a WAL log automatic incremental backup and deletion device based on a PostgreSQL database system, comprising: The information initialization module starts the PostgreSQL database and calls the deletion script. It performs a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, stores the files of the last successful basic backup, and deletes the files of the successful basic backup before the last basic backup. An information judgment module, wherein the information acquisition module is used to determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup; The information processing module is used to 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 archive incremental backup of the WAL log files when it is determined that the capacity of the custom backup directory is less than or equal to twice the capacity of the PostgreSQL data directory; 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.

[0043] Embodiment 3 A database log processing system comprises a device for executing each step of a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system.

[0044] The method comprises the following steps: starting the PostgreSQL database and calling the deletion script each time, performing a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, storing the files of the previous successful basic backup, deleting the files of the earlier successful basic backup, saving the WAL log copy of the archive backup in a custom backup directory, executing the deletion script, determining whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup, and when determining that the capacity of the custom backup directory is less than or equal to 2 times 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 an archive incremental backup of the WAL log files; when determining that the capacity of the custom backup directory is greater than 2 times 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 of the changed PostgreSQL database in the form of a WAL log archive backup.

[0045] The removal script does the following: When it is determined that 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 archived incremental backup of the WAL log files continues.

[0046] When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0047] Embodiment 4 See also Figure 3, 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 automatic incremental backup and deletion method of the WAL log based on the PostgreSQL database system when executing the computer program. The method comprises the following steps: starting the PostgreSQL database and calling the deletion script each time, performing a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, storing the files of the previous successful basic backup, deleting the files of the earlier successful basic backup, saving the WAL log copy of the archive backup 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 directory of the archive backup, and when judging that the capacity of the custom backup directory is less than or equal to 2 times 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; when judging that the capacity of the custom backup directory is greater than 2 times 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 of the changed PostgreSQL database in the form of WAL log archive backup.

[0048] The removal script does the following: When it is determined that 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 archived incremental backup of the WAL log files continues.

[0049] When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0050] Embodiment 5 A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method for automatically deleting WAL logs based on a PostgreSQL database system is implemented. The method includes the following steps: starting the PostgreSQL database and calling a deletion script each time, performing a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, storing the files of the previous successful basic backup, deleting the files of the earlier successful basic backup, saving the archived backup WAL log copy 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 archived backup WAL log directory, and when judging that the capacity of the custom backup directory is less than or equal to 2 times 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 archived incremental backup of the WAL log files; when judging that the capacity of the custom backup directory is greater than 2 times 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 of the changed PostgreSQL database in the form of a WAL log archive backup.

[0051] The removal script does the following: When it is determined that 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 archived incremental backup of the WAL log files continues.

[0052] When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0053] Embodiment 6 A computer program product includes a computer program / instruction, and when the computer program / instruction is executed by a processor, the method for automatically deleting WAL logs based on a PostgreSQL database system in an incremental backup is implemented. The method includes the following steps: starting the PostgreSQL database and calling a deletion script each time, performing a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of a WAL log archive backup, storing the files of the previous successful basic backup, deleting the files of the earlier successful basic backup, saving the WAL log copy of the archive backup 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 directory of the archive backup, and when judging that the capacity of the custom backup directory is less than or equal to 2 times 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 an incremental archive backup of the WAL log files; when judging that the capacity of the custom backup directory is greater than 2 times 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 of the changed PostgreSQL database in the form of a WAL log archive backup.

[0054] The removal script does the following: When it is determined that 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 archived incremental backup of the WAL log files continues.

[0055] When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

[0056] Embodiment 7 This embodiment provides a method for automatically deleting WAL logs based on a PostgreSQL database system through incremental backup, which is implemented by a script (such as tmcs_archivecleanup_script.sh), which is simple and easy to operate and is placed in the installation directory / usr / local / pgsql / of the PostgreSQL system. It is necessary to configure the value of the "archive_command" field in the configuration file / usr / local / pgsql / data / postgresql.conf of the PostgreSQL database system 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'", archive_command: Set the archive command to specify how to copy the WAL segment file to the archive storage when it is full. %p represents the path of the WAL file, and %f represents the name of the WAL file.

[0057] The script tmcs_archivecleanup_script.sh has the following scheme: Each execution will perform an archive incremental backup operation of the WAL log file.

[0058] When the backup directory capacity is too large, which is twice the capacity of the PostgreSQL data directory / usr / local / pgsql / data, first clear outdated WAL log archive files, that is, WAL logs in the pg_wal directory and the custom directory, and then perform a basic backup of the PostgreSQL data directory / usr / local / pgsql / data. 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 archive incremental backup of WAL log files is performed.

[0059] See also Figure 4 , the specific process is as follows: Create a script tmcs _archivecleanup_script.sh to perform a basic backup, i.e., a full backup, in the form of WAL log archive backup when the PostgreSQL database is first started after deployment. Then, it is automatically executed according to the configuration information in the configuration file / usr / local / pgsql / data / postgresql.conf. The configuration information includes: enabling WAL log archiving and the value of the "archive_command" field. The PostgreSQL database system automatically calls the method to execute the script, and performs a WAL log archive operation each time. After the method of the script tmcs _archivecleanup_script.sh is called and executed, if it is determined 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, the basic backup operation of the PostgreSQL data directory / usr / local / pgsql / data will not be performed again, and the WAL log files in the backup directory / usr / local / pgsql / tmcs_archivedir / tmcs _database_back and the WAL log files in the PostgreSQL system's own WAL log directory / usr / local / pgsql / data / pg_wal / will not be cleared. Only incremental backup of the WAL log files will be performed. When the data on the TMCS server needs to be restored, data loss can be guaranteed.

[0060] After the method of the script TMCS_archivecleanup_script.sh is called and executed, if it is determined that 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 PostgreSQL system's own WAL log directory / usr / local / pgsql / data / pg_wal / will be cleared, and then the basic backup operation of the PostgreSQL data directory / usr / local / pgsql / data will be performed again, that is, a full backup of the changed PostgreSQL database is made in the form of WAL log archive backup, which also greatly reduces the backup directory / usr / local / pgsql / tmcs_archivedir / tmcs_database_back. 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 archive, will be used for overall recovery, which can ensure the overall recovery of the TMCS server data.

[0061] It will be appreciated by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0062] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, 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 device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0063] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0064] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process in the computer or other programmable device. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0065] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit its protection scope. Although the present invention has been described in detail with reference to the above embodiments, ordinary technicians in the field should understand that after reading the present invention, those skilled in the art can still make various changes, modifications or equivalent substitutions to the specific implementation methods of the invention, but these changes, modifications or equivalent substitutions are all within the protection scope of the pending claims of the invention.

Claims

1. A WAL log automatic incremental backup and deletion method based on a PostgreSQL database system, characterized in that: The steps include: Start the PostgreSQL database and call the deletion script. Perform a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup. Store the files that were successfully backed up last time, delete the files that were successfully backed up before the last time, and save the archived WAL log copy in the custom backup directory. Execute the deletion script to determine whether the capacity of the custom backup directory is larger than the capacity of the WAL log directory for archived backup. 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 archived 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, 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 WAL log archive backup.

2. A WAL log automatic incremental backup and deletion method based on a PostgreSQL database system according to claim 1, characterized in that: When restoring the backup data of the TMCS database on the trusted management platform server, perform overall recovery according to the latest basic backup file and the incremental backup file archived by the WAL log.

3. A WAL log automatic incremental backup and deletion method based on a PostgreSQL database system according to claim 1, characterized in that: The PostgreSQL database system automatically calls and executes the deletion script. 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 a WAL log archive backup.

4. A WAL log automatic incremental backup and deletion method based on a PostgreSQL database system according to claim 1, characterized in that: The removal script is installed in the installation directory of the PostgreSQL database system.

5. A WAL log automatic incremental backup and deletion method based on a PostgreSQL database system according to claim 1, characterized in that: In the configuration file of the PostgreSQL database system, configure the value of the deletion field to point to the storage path of the deletion script, and the deletion field is automatically executed according to the configuration file.

6. A WAL log automatic incremental backup and deletion device based on a PostgreSQL database system, characterized in that: include: The information initialization module starts the PostgreSQL database and calls the deletion script. It performs a basic backup of the PostgreSQL data directory of the PostgreSQL database in the form of WAL log archive backup, stores the files of the last successful basic backup, and deletes the files of the successful basic backup before the last basic backup. An information judgment module, wherein the information acquisition module is used to determine whether the capacity of the custom backup directory is greater than the capacity of the WAL log directory of the archive backup; An information processing module, wherein when it is determined that 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 archived incremental backup of the WAL log files is continued; When it is determined that 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 of the changed PostgreSQL database is performed in the form of WAL log archive backup.

7. A database log processing system, characterized in that: It comprises a device for executing each step of a WAL log automatic incremental backup and deletion method based on a PostgreSQL database system according to any one of claims 1-5.

8. An electronic device, characterized in that: The invention comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the method for automatically deleting WAL logs based on a PostgreSQL database system as described in any one of claims 1 to 5 is implemented.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method for automatically deleting WAL log incremental backup based on a PostgreSQL database system described in any one of claims 1 to 5 is implemented.

10. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instruction is executed by a processor, the method for automatically deleting WAL log incremental backup based on a PostgreSQL database system as described in any one of claims 1 to 5 is implemented.

Images