i read several document on postgresql archive recovery , cleanup postgresql server still don't purge archive, or didn't understand how works.
simply, wrote shell script wal archive cleanup. when run script command line, works , purges archive (i leave archive newer 3 days). script named pg_archive_cleanup, executable , put here : /usr/sbin/
and configuire /etc/postgresql/9.3/main/recovery.conf :
# ------------------------------- # postgresql recovery config file # ------------------------------- # # edit file provide parameters postgresql needs # perform archive recovery of database, or act replication # standby. # # if "recovery.conf" present in postgresql data directory, # read on postmaster startup. after successful recovery, renamed # "recovery.done" ensure not accidentally re-enter # archive recovery or standby mode. # # file consists of lines of form: # # name = value # # comments introduced '#'. # # complete list of option names , allowed values can found # in postgresql documentation. # #--------------------------------------------------------------------------- # archive recovery parameters #--------------------------------------------------------------------------- # archive_cleanup_command # # specifies optional shell command execute @ every restartpoint. # can useful cleaning archive of standby server. # archive_cleanup_command = '/usr/sbin/pg_archive_cleanup' # #--------------------------------------------------------------------------- # recovery target parameters #--------------------------------------------------------------------------- # # default, recovery rollforward end of wal log. # if want stop rollforward @ specific point, # must set recovery target. # # may set recovery target either transactionid, name, # or timestamp. recovery may either include or exclude # transaction(s) recovery target value (ie, stop either # after or before 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 want recover timeline other "main line" shown in # pg_control, specify timeline number here, or write 'latest' # latest branch there's history file. # #recovery_target_timeline = 'latest' # # # if pause_at_recovery_target enabled, recovery pause when # recovery target reached. pause state continue until # pg_xlog_replay_resume() called. setting has no effect if # hot standby not enabled, or if no recovery target set. # #pause_at_recovery_target = true # #--------------------------------------------------------------------------- # standby server parameters #--------------------------------------------------------------------------- # # standby_mode # # when standby_mode enabled, postgresql server work # standby. continuously wait 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, postgresql server try connect primary using # connection string , receive xlog records continuously. # primary_conninfo = 'host=db-master port=5432 user=repli password=esibfegiav4' # e.g. 'host=localhost port=5432' # # # default, standby server keeps restoring xlog records # primary indefinitely. if want stop standby mode, finish recovery # , open system in read/write mode, specify path trigger file. # server poll trigger file path periodically , start # primary server when it's found. # trigger_file = '/var/lib/postgresql/database/failover_trigger' # #--------------------------------------------------------------------------- # hot standby parameters #--------------------------------------------------------------------------- # # hot standby related parameters listed in postgresql.conf # #--------------------------------------------------------------------------- you can see line
archive_cleanup_command = '/usr/sbin/pg_archive_cleanup' my cleanup script :
#!/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 but after 4 days disk space has grown. saw in documentation recovery.conf file read @ at check_point , restart_point... know why archive not purged automatically? set occurency? when postgresl supposed proceed purge? want occure dayly, i'm obliged put cleanup script in crontab instead? or somewhere else? , have no trace in postgresql log files. cleanup log writen?
thanks replies.
pg_archivecleanup takes arguments:
$ /usr/pgsql-9.4/bin/pg_archivecleanup pg_archivecleanup: must specify archive location try "pg_archivecleanup --help" more information. if in logs on replica you'll see repeated message pg_archivecleanup.
the manual shows archive_cleanup_command has %r substitution last valid restart point, , shows example configuration:
archive_cleanup_command = 'pg_archivecleanup /mnt/server/archivedir %r' 
Comments
Post a Comment