Shulou(Shulou.com)06/01 Report--

This article mainly shows you "PostgreSQL pg_archivecleanup and how to clean up archivelog", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "PostgreSQL pg_archivecleanup and how to clean up archivelog" this article.

Pg_archivecleanup and pg_rewind are two important functions in PG, one is to clean up out-of-date commands used by archive log, and the other is a porter of wal log that you can understand as a physical level.

Let's start with the first pg_archivecleanup command, which is mainly used for postgresql that uses the archive log feature, but how do you clean up these logs according to certain rules when archive log is piling up?

This is about to use the pg_archivecleanup command, you can run it regularly, and start cleaning up the archivelog that has been moved to the directory of archivecleanup as required.

Of course, first of all, let's talk about what will happen if we don't clean up regularly.

1 if archive is not cleaned periodically, if the location where archivelog is stored cannot accept new logs, a large number of WAL logs will be stranded in the wal_log directory, and the overall database system will be affected.

2 take up a lot of storage space and store invalid data

Generally speaking, if there is no third-party backup tool, how to clean up archivelog through pg_archivecleanup.

There are several points to pay attention to.

1 when cleaning, whether the cleaned WAL_LOG is already included in the last backup, to ensure that the cleaned WAL_LOG can also restore the database from the backup file

2 when cleaning, what about the wal_log saved in the non-main library

In general, setting automatic cleanup archive_log can be added to the configuration file

Archive_cleanup_command = 'pg_archivecleanup archivelocation% r'

To operate.

But generally speaking, this has fewer advantages and more disadvantages. I prefer to write relevant scripts, run them regularly, and record relevant LOG logs, and so on.

You can write a script to assist in regularly cleaning up related archive_log

Of course, this method also has drawbacks. If you directly use the number of days to clean up because of the failure of the backup, you will directly clean up the wal_log because there is no backup, so a more reliable method is to use some commands to get the Wal_log name of the cleaning that needs to be cut off.

For example, after backup

There will be a backup tag in the wal_log, which means that the previous data of the WAL log has been backed up, and it is safe to clean up the log before the WAL LOG.

000000010000000300000030.00000060.backup

Use the following script to clean up more safely

#! / bin/bash

ARCHIVEDIR='/pgdata/archive'

CHK_SAFE=$ (find $ARCHIVEDIR-type f-mtime + 3-name'* backup'-printf'% f\ n'| sort-r | head-1)

Cd $ARCHIVEDIR

/ usr/local/postgres/bin/pg_archivecleanup $ARCHIVEDIR $CHK_SAFE

Find $ARCHIVEDIR-type f-mtime + 3-a-name'* backup'-a!-newer $CHKPOINT-delete is all the content of this article "PostgreSQL pg_archivecleanup and how to clean up archivelog". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.