Postgresql自动归档清除不发生

Question

我读了postgresql归档恢复和清理的几个文件，但我的postgresql服务器仍然不清除归档，或者我真的不明白它是如何工作的。

简单地说，我为WAL归档清理编写了一个shell脚本。当我从命令行运行该脚本时，它可以工作并清除我的存档（我只保留比三天更新的存档）。我的脚本名为pg_archive_cleanup，是可执行文件，并在这儿：/usr/sbin目录/

我configuire我/etc/postgresql/9.3/main/recovery.conf这样的：

# ------------------------------- 
# PostgreSQL recovery config file 
# ------------------------------- 
# 
# Edit this file to provide the parameters that PostgreSQL needs to 
# perform an archive recovery of a database, or to act as a replication 
# standby. 
# 
# If "recovery.conf" is present in the PostgreSQL data directory, it is 
# read on postmaster startup. After successful recovery, it is renamed 
# to "recovery.done" to ensure that we do not accidentally re-enter 
# archive recovery or standby mode. 
# 
# This file consists of lines of the form: 
# 
# name = value 
# 
# Comments are introduced with '#'. 
# 
# The complete list of option names and allowed values can be found 
# in the PostgreSQL documentation. 
# 
#--------------------------------------------------------------------------- 
# ARCHIVE RECOVERY PARAMETERS 
#--------------------------------------------------------------------------- 
# archive_cleanup_command 
# 
# specifies an optional shell command to execute at every restartpoint. 
# This can be useful for cleaning up the archive of a standby server. 
# 
archive_cleanup_command = '/usr/sbin/pg_archive_cleanup' 
# 
#--------------------------------------------------------------------------- 
# RECOVERY TARGET PARAMETERS 
#--------------------------------------------------------------------------- 
# 
# By default, recovery will rollforward to the end of the WAL log. 
# If you want to stop rollforward at a specific point, you 
# must set a recovery target. 
# 
# You may set a recovery target either by transactionId, by name, 
# or by timestamp. Recovery may either include or exclude the 
# transaction(s) with the recovery target value (ie, stop either 
# just after or just before the given target, respectively). 
# 
# 
#recovery_target_name = ''  # e.g. 'daily backup 2011-01-26' 
# 
#recovery_target_time = ''  # e.g. '2004-07-14 22:39:00 EST' 
# 
#recovery_target_xid = '' 
# 
#recovery_target_inclusive = true 
# 
# 
# If you want to recover into a timeline other than the "main line" shown in 
# pg_control, specify the timeline number here, or write 'latest' to get 
# the latest branch for which there's a history file. 
# 
#recovery_target_timeline = 'latest' 
# 
# 
# If pause_at_recovery_target is enabled, recovery will pause when 
# the recovery target is reached. The pause state will continue until 
# pg_xlog_replay_resume() is called. This setting has no effect if 
# hot standby is not enabled, or if no recovery target is set. 
# 
#pause_at_recovery_target = true 
# 
#--------------------------------------------------------------------------- 
# STANDBY SERVER PARAMETERS 
#--------------------------------------------------------------------------- 
# 
# standby_mode 
# 
# When standby_mode is enabled, the PostgreSQL server will work as a 
# standby. It will continuously wait for the additional XLOG records, using 
# restore_command and/or primary_conninfo. 
# 
standby_mode = on 
restore_command = 'cp /var/lib/postgresql/database/archive/%f "%p"' 
# 
# primary_conninfo 
# 
# If set, the PostgreSQL server will try to connect to the primary using this 
# connection string and receive XLOG records continuously. 
# 
primary_conninfo = 'host=db-master port=5432 user=repli password=Esibfegiav4'   # e.g. 'host=localhost port=5432' 
# 
# 
# By default, a standby server keeps restoring XLOG records from the 
# primary indefinitely. If you want to stop the standby mode, finish recovery 
# and open the system in read/write mode, specify path to a trigger file. 
# The server will poll the trigger file path periodically and start as a 
# primary server when it's found. 
# 
trigger_file = '/var/lib/postgresql/database/failover_trigger' 
# 
#--------------------------------------------------------------------------- 
# HOT STANDBY PARAMETERS 
#--------------------------------------------------------------------------- 
# 
# Hot Standby related parameters are listed in postgresql.conf 
# 
#--------------------------------------------------------------------------- 

你可以看到线

archive_cleanup_command = '/usr/sbin/pg_archive_cleanup' 

我清理脚本：

#!/bin/bash 

ARCHIVEDIR='/var/lib/postgresql/database/archive' 
CHECKPOINT=$(find $ARCHIVEDIR -type f -mtime +3 -type f -printf '%f\n' | sort -r | head -1) 
cd $ARCHIVEDIR 
/usr/bin/pg_archivecleanup $ARCHIVEDIR $CHECKPOINT 

find $ARCHIVEDIR -type f -mtime +3 -a -type f -a ! -newer $CHECKPOINT -delete 

但4天后磁盘空间增加了。 我在文档中看到，的recovery.conf文件在在check_point和restart_point读... 所以，我想知道为什么归档文件不自动清除？我在哪里设置偶发性？ postgresl什么时候应该进行清除？ 我希望这一天出现，我有义务将我的清理脚本放在crontab中吗？或者别的地方？ 而且我的postgresql日志文件中也没有任何痕迹。清理日志写在哪里？

感谢您的回复。

Answer 1

pg_archivecleanup接受参数：

$ /usr/pgsql-9.4/bin/pg_archivecleanup 
pg_archivecleanup: must specify archive location 
Try "pg_archivecleanup --help" for more information. 

如果您在副本上的日志看，你可能会看到从pg_archivecleanup重复的帮助信息。

the manual表明archive_cleanup_command有%r取代最后一个有效的启动点，并给出了一个例子配置：

archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r' 

Answer 2

我解决了这个问题。

我的脚本和我的配置都很好。我只是没有重新启动postgresql服务有一个新的重新启动点考虑到我的修改。

我没有重新启动servicec，它现在正常工作正常。

Thanks @Craig