An automated processing method, medium, and device for PostgreSQL archive logs.

Abstract

This invention relates to an automatic processing method, medium, and device for PostgreSQL archive logs. The method is applied to a PostgreSQL database system including a master and slave databases, and includes the following steps: 1) obtaining the execution status of the streaming replication process and the current pushed file information of the master database, and simultaneously obtaining the streaming replication receiving process status of the slave database at the same time; 2) comparing the execution status of the streaming replication process of the master database with the streaming replication receiving process status of the slave database, and automatically cleaning up the logs based on the comparison result. Compared with the prior art, this invention has advantages such as reducing error rate and improving operation and maintenance efficiency.

Description

Technical Field

[0001] This invention belongs to the field of database processing technology, and in particular relates to an automatic processing method, medium and device for POSTGRESQL archive logs. Background Technology

[0002] Oracle and MySQL are traditional relational databases widely used in practice. Currently, with an increasing number of database options, some databases offer open-source licenses for easier use. For example, PostgreSQL not only offers an open-source license but also various free add-ons to help improve database storage and processing performance.

[0003] For production systems with high availability requirements, the simplest high-availability setup using PostgreSQL is to configure at least two databases in a master-slave configuration. The working principle involves enabling archiving on the master database. This archive is a binary file that records all database changes. The slave database acts as a mirror. A communication mechanism is established between the master and slave databases. The master pushes the archived changes to the slave. The slave then replicates these changes, ensuring consistency with the master. This creates a database mirroring. If the master database becomes unrecoverable, the slave, having received the changed data from the master in near real-time, is largely guaranteed to have no data loss. In such extreme scenarios where the master database is unrecoverable, the slave takes over the business operations to maintain business continuity to the greatest extent possible.

[0004] Based on the above principles, archived logs act as a transmission bridge. After changes are performed on the slave database, their role becomes largely irrelevant. After the transmission task is completed, archiving is only used for analyzing change events. These logs accumulate indefinitely. Currently, the approach is to manually determine and clear expired logs. However, this method has two problems: one is the possibility of a log surge, where disk space is exhausted before the data is ready for cleanup; the other is that if the slave database receives data slowly, manually clearing these archives breaks the bridge between the master and slave databases, terminating the master-slave relationship, causing data discontinuity, rendering the logic invalid, and requiring re-establishment.

[0005] Therefore, it is necessary to develop a new technology to avoid the above problems. Summary of the Invention

[0006] The purpose of this invention is to overcome the shortcomings of the existing technology and provide an automatic processing method, medium and device for POSTGRESQL archive logs to improve operation and maintenance efficiency.

[0007] The objective of this invention can be achieved through the following technical solutions:

[0008] One aspect of the present invention provides an automatic processing method for PostgreSQL archive logs, applied to a PostgreSQL database system including a master database and slave databases, the method comprising the following steps:

[0009] 1) Obtain the execution status of the streaming replication process and the current pushed file information of the master database, and at the same time obtain the streaming replication receiving process status of the slave database at the same moment;

[0010] 2) Compare the execution status of the master database's streaming replication process with the status of the slave database's streaming replication receiving process, and automatically clean up the logs based on the comparison results.

[0011] The execution status of the master database's streaming replication process, the current pushed file information, and the status of the slave database's streaming replication receiving process are obtained through operating system commands.

[0012] Furthermore, the operating system commands are combined and concatenated to generate instructions for obtaining the execution status of the master database's streaming replication process, the current pushed file information, and the status of the slave database's streaming replication receiving process.

[0013] Furthermore, the comparison result includes the number of differences between the streaming push and the receiving at the same time, and the number of differences is expressed in decimal.

[0014] Furthermore, the automatic log cleanup based on the comparison results specifically involves:

[0015] Only when the number of differences is less than or equal to 1, delete all archived logs prior to the currently pushed file information.

[0016] Furthermore, the automatic log cleanup is specifically achieved as follows:

[0017] Use the archived log cleanup tool pg_archivecleanup to automatically clean up the logs.

[0018] Furthermore, the method also includes:

[0019] Periodically obtain disk usage, and execute steps 1) and 2) when the disk usage exceeds a set value.

[0020] A second aspect of the present invention provides an automatic processing apparatus for PostgreSQL archive logs, the apparatus being deployed in a PostgreSQL database system including a master database and slave databases, comprising:

[0021] The status information acquisition module is used to acquire the execution status of the streaming replication process of the master database and the current pushed file information, and at the same time acquire the streaming replication receiving process status of the slave database.

[0022] The automatic cleanup module is used to compare the execution status of the streaming replication process of the master database with the streaming replication receiving process status of the slave database, and to automatically clean up the logs based on the comparison results.

[0023] A third aspect of the present invention provides a computer-readable storage medium including one or more programs executable by one or more processors of an electronic device, said one or more programs including instructions for performing an automated processing method for POSTGRESQL archive logs as described above.

[0024] A fourth aspect of the present invention provides an electronic device including one or more processors, a memory, and one or more programs stored in the memory, said one or more programs including instructions for performing the automatic processing method for POSTGRESQL archive logs as described above.

[0025] Compared with the prior art, the present invention has the following beneficial effects:

[0026] 1. This invention can clear out the archived logs that have expired in the master database when the master database space is insufficient, while ensuring that the slave database receives the archived logs from the master database. This ensures that even if the logs surge, the disk will not be exhausted, and there will be no accidental deletion due to human judgment, thus providing strong support for IT operations and maintenance.

[0027] 2. This invention obtains the execution status of the master database's streaming replication process, the current pushed file information, and the status of the slave database's streaming replication receiving process through operating system commands, thereby achieving automatic log cleanup. It does not require configuring the database username and password, nor does it require logging into the database instance. Only the administrator password of the operating system where the database is located is needed, which can be determined at the operating system level, making it convenient to use.

[0028] 3. This invention uses a streaming replication mode to build a master-slave high-availability architecture. It focuses on identifying the process transmission status of streaming replication at the operating system level, rather than at the database instance level. The method is simpler and can achieve fast automatic log cleanup.

[0029] 4. In existing methods, PostgreSQL database beginners who are unfamiliar with master-slave relationships, how to check execution progress, and how to delete files, or even directly delete archives at the operating system level, are prone to causing failures. This invention effectively lowers the operational threshold for beginners, solving operational problems, especially for those unfamiliar with PostgreSQL and Linux, and reducing learning costs.

[0030] 5. This invention is the first to propose automating the processing of archived logs in a PostgreSQL database. Maintenance personnel are no longer required to handle the same fault types and repetitive operations every day, reducing the workload and error rate of manual troubleshooting, saving manpower and time costs, thereby improving the security of IT system maintenance and management, and enhancing operational efficiency. Attached Figure Description

[0031] Figure 1 This is a schematic diagram of the process of the present invention. Detailed Implementation

[0032] The present invention will now be described in detail with reference to the accompanying drawings and specific embodiments. These embodiments are based on the technical solution of the present invention and provide detailed implementation methods and specific operating procedures. However, the scope of protection of the present invention is not limited to the following embodiments.

[0033] Example 1

[0034] like Figure 1 As shown, this embodiment provides an automatic processing method for PostgreSQL archive logs, applied to a PostgreSQL database system including a master and slave databases. The method includes the following steps:

[0035] S1. Obtain the execution status of the streaming replication process of the master database and the current pushed file information, and at the same time obtain the streaming replication receiving process status of the slave database.

[0036] In this step, the execution status of the master database's streaming replication process, the current pushed file information, and the status of the slave database's streaming replication receiving process are obtained through operating system commands.

[0037] Typically, a process might contain a format like streaming 23E / 2F7592F8; in this embodiment, we'll call this variable A. The location of the file the push process is currently pushing is typically specified in the archive directory, for example, 000000010000023E0000002F; in this embodiment, we'll call this variable B. The file is named in hexadecimal. There's a correspondence between processes and files. As illustrated above, it means that the current push has reached the problem file 000000010000023E0000002F. The file 000000010000023E0000002E is called C, indicating that the previous file has already been pushed.

[0038] Based on the above judgment logic, the method in this embodiment utilizes the changes in the streaming replication process via `ps` on the master database. Simultaneously, on the master database, the operating system command `ssh root@slaveIP'ps-ef|grep pg'` is used, along with a custom script using commands such as AWK to obtain changes in the streaming replication process on the slave database, thereby achieving comparison. The current receiving process status of the streaming replication on the slave database is obtained, and this variable is called D. The naming format of variable D is similar to that of variable A.

[0039] This embodiment uses a combination of commands at the operating system level, eliminating the need for a database administrator or login. Only the administrator password of the operating system where the database resides is required. The PS command is used to query the execution status of the PostgreSQL streaming replication process. By comparing the number of differences between the streaming replication push and reception at the same time in the master and slave databases, the success progress of the push can be determined, thereby determining the status of the master database archived logs being applied to the slave database. The status information is easy to obtain.

[0040] S2. Compare the execution status of the streaming replication process of the master database with the streaming replication receiving status of the slave database, and automatically clean up the logs based on the comparison result. The comparison result includes the number of differences between streaming replication push and receive at the same time, specifically, the number of differences is expressed in decimal.

[0041] The automatic log cleanup based on the comparison results is as follows: Only when the number of differences is less than or equal to 1, all archived logs prior to the currently pushed file information are deleted. That is, based on the status information obtained in step S1, if AD <= 1 (hexadecimal needs to be converted to decimal for comparison), it can be determined that all archived logs C before the archived file with sequence number B in the master database can be deleted. After determining that the deletable file is valid, the PostgreSQL archived log cleanup tool pg_archivecleanup can be called, passing parameter C, for automated cleanup.

[0042] The above method can be applied to scenarios where there is no time to handle emergencies. Existing log cleanup methods usually involve setting a retention period or manual cleanup in case of sudden events. However, in practice, business data anomalies often cause alarm thresholds to be reached before the configured retention period is reached, leaving insufficient time for deletion. The above method can effectively solve these problems.

[0043] In a preferred embodiment, the method further includes: periodically acquiring disk usage, and executing steps S1 and S2 above when the disk usage exceeds a set value. Specifically, a command tool is formed based on steps S1 and S2, which is implemented as a scheduled task to check the current disk usage every minute. When disk space is insufficient, this tool is invoked for automated cleanup.

[0044] The comparison between the above method and existing traditional methods is shown in Table 1.

[0045]

[0046] The above methods can be applied to scenarios such as "a sudden increase in business volume leads to a large number of write operations in the database, a significant increase in archive logs, and insufficient disk space", or "a problem with the application code logic leads to a large number of write operations in the database, a significant increase in archive logs, and insufficient disk space".

[0047] The application of the above methods in specific scenarios is shown in Table 2.

[0048] Table 2

[0049]

[0050]

[0051] Example 2

[0052] This embodiment provides an automatic processing device for PostgreSQL archive logs. The device is deployed in a PostgreSQL database system including a master and slave databases. It includes a status information acquisition module and an automatic cleanup module. The status information acquisition module acquires the execution status of the streaming replication process and the currently pushed file information of the master database, and simultaneously acquires the streaming replication receiving process status of the slave database at the same time. The automatic cleanup module compares the execution status of the streaming replication process of the master database with the streaming replication receiving process status of the slave database, and performs automatic log cleanup based on the comparison result. The rest is the same as in Embodiment 1.

[0053] The automatic processing device in this embodiment can be made into a log automation processing tool.

[0054] Example 3

[0055] This embodiment provides an electronic device, including one or more processors, a memory, and one or more programs stored in the memory, the one or more programs including instructions for performing an automatic processing method for POSTGRESQL archive logs as described in Embodiment 1.

[0056] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can 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. The solutions in the embodiments of the present invention can be implemented using various computer languages, such as the object-oriented programming language Java and the interpreted scripting language JavaScript.

[0057] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations 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, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0058] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0059] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0060] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0061] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

Claims

1. An automatic processing method for PostgreSQL archive logs, characterized in that, Applied to a PostgreSQL database system including a master and slave databases, the method includes the following steps: 1) Obtain the execution status of the streaming replication process of the master database and the current pushed file information, and at the same time obtain the streaming replication receiving process status of the slave database. Specifically, obtain the execution status of the streaming replication process of the master database, the current pushed file information, and the streaming replication receiving process status of the slave database through operating system commands. 2) Compare the execution status of the streaming replication process of the master database with the streaming replication receiving status of the slave database, and automatically clean up the logs based on the comparison results; When comparing, the execution status of the master database's streaming replication process and the receiving status of the slave database's streaming replication process are converted from hexadecimal to decimal. Use the archived log cleanup tool pg_archivecleanup to automatically clean up the logs.

2. The automatic processing method for PostgreSQL archive logs according to claim 1, characterized in that, The operating system commands are combined and concatenated to generate instructions for obtaining the execution status of the master database's streaming replication process, the current pushed file information, and the status of the slave database's streaming replication receiving process.

3. The automatic processing method for PostgreSQL archive logs according to claim 1, characterized in that, The comparison results include the number of differences between streaming push and receiving at the same time, and the number of differences is expressed in decimal.

4. The automatic processing method for PostgreSQL archive logs according to claim 3, characterized in that, The automatic log cleanup based on the comparison results specifically refers to: Only when the number of differences is less than or equal to 1, delete all archived logs prior to the currently pushed file information.

5. The automatic processing method for PostgreSQL archive logs according to claim 1, characterized in that, Also includes: Periodically obtain disk usage, and execute steps 1) and 2) when the disk usage exceeds a set value.

6. An automatic processing device for PostgreSQL archive logs, characterized in that, The device is deployed in a PostgreSQL database system that includes a master and slave databases, including: The status information acquisition module is used to acquire the execution status of the streaming replication process of the master database and the current pushed file information, and at the same time acquire the streaming replication receiving process status of the slave database. Specifically, the execution status of the streaming replication process of the master database, the current pushed file information, and the streaming replication receiving process status of the slave database are acquired through operating system commands. The automatic cleanup module is used to compare the execution status of the streaming replication process of the master database with the streaming replication receiving process status of the slave database, and to automatically clean up the logs based on the comparison results. In the automatic cleanup module, when comparing data, the execution status of the master database's streaming replication process and the receiving status of the slave database's streaming replication process are converted from hexadecimal to decimal. Use the archived log cleanup tool pg_archivecleanup to automatically clean up the logs.

7. A computer-readable storage medium, characterized in that, Includes one or more programs that are executed by one or more processors of an electronic device, said one or more programs including instructions for performing an automated processing method for POSTGRESQL archive logs as described in any one of claims 1-5.

8. An electronic device, characterized in that, It includes one or more processors, memory, and one or more programs stored in the memory, said one or more programs including instructions for performing an automated processing method for POSTGRESQL archive logs as described in any one of claims 1-5.

Images