postgresql 如何使用 pg_archivecleanup 按文件年龄或日期指定清理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16943599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to specify cleanup by file age or date with pg_archivecleanup
提问by Sébastien Clément
Is there a way to specify files by age or date instead of by a hardcoded name to cleanup the WAL archives with the pg_archivecleanup command ?
有没有办法按年龄或日期而不是硬编码名称指定文件来使用 pg_archivecleanup 命令清理 WAL 档案？
For example, using the following command is pretty straightforward:
例如，使用以下命令非常简单：
pg_archivecleanup "C:\Program Files\PostgreSQL.2\data\pg_xlog" 000000010000004600000045
where 000000010000004600000045 is the file name, and any file created before will be deleted.
其中 000000010000004600000045 是文件名，之前创建的任何文件都将被删除。
However, if I want to automate the process, there must be a way to choose the files by age/date.
但是，如果我想自动化该过程，则必须有一种方法可以按年龄/日期选择文件。
回答by joris
You can perfectly use it as a standalone tool.
您可以完美地将其用作独立工具。
I see you use Windows, so I can't help you, but here's what I would do on Unix.
我看到你使用 Windows，所以我无法帮助你，但这是我会在 Unix 上做的事情。
The following command will call for a cleanup up to the more recent checkpoint older than 3 days. You must set $ARCHIVEDIRso it matches your dump of archive logs.
以下命令将要求清理到最近 3 天之前的检查点。您必须设置$ARCHIVEDIR使其与您的存档日志转储相匹配。
find $ARCHIVEDIR -mtime +3 -name '*backup' -printf '%f\n' | sort -r | head -1 | xargs pg_archivecleanup $ARCHIVEDIR
A more complete script would be :
更完整的脚本是：
#!/bin/bash
ARCHIVEDIR='/var/lib/pg_xlog_archives/whatever/'
CHKPOINT=$(find $ARCHIVEDIR -type f -mtime +3 -name '*backup' -printf '%f\n' | sort -r | head -1)
cd $ARCHIVEDIR
/usr/lib/postgresql/9.3/bin/pg_archivecleanup $ARCHIVEDIR $CHKPOINT
find $ARCHIVEDIR -type f -mtime +3 -a -name '*backup' -a ! -newer $CHKPOINT -delete
回答by FuzzyChef
You are not supposed to be running pg_archivecleanup via shell script. The purpose of that utility is to be called by the replica through recovery.conf, when it reads the archive directory. Only the actual replica database knows what files it can get rid of.
您不应该通过 shell 脚本运行 pg_archivecleanup。该实用程序的目的是在副本读取存档目录时通过 recovery.conf 调用该实用程序。只有实际的副本数据库知道它可以删除哪些文件。
Also, archivecleanup is notused on your pg_xlog directory. It is used on the archive copy of your logs, which is stored in some location you've specified using archive_command on the master.
此外，archivecleanup不在您的 pg_xlog 目录中使用。它用于日志的存档副本，该副本存储在您在主服务器上使用 archive_command 指定的某个位置。
Example:
例子：
master postgresql.conf:
掌握 postgresql.conf：
archive_command = "scp %f replica:/usr/share/wal_archive:%r"
(note: I strongly recommend having archive command call a script instead of the above)
（注意：我强烈建议让存档命令调用脚本而不是上面的）
replica recovery.conf:
副本恢复.conf：
archive_cleanup_command = 'pg_archivecleanup /usr/share/wal_archive %r'
