Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1)

I followed the official tutorial to configure online recovery with PITR: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery

There is a tar command in script "copy-base-backup":

tar -C /data -zcf pgsql.tar.gz pgsql

So all my PG cluster directory being copied while script is running:

ls -1 /srv/pg/data/
PG_VERSION 
archive # directory with postgres archive files
backup_label.old
base
global
pg_clog
pg_hba.conf
pg_ident.conf
pg_log
pg_multixact
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
pg_xlog
pgpool_recovery
pgpool_recovery_pitr
pgpool_remote_start
postgresql.conf
postmaster.opts
postmaster.pid
recovery.conf
recovery.done

How can I safely remove old archive logs from primary and from standby nodes? There are about 30Gb of archives per day on my nodes.

share|improve this question
    
pg_archivecleanup look at that contrib module. –  Hz.Root Mar 6 '12 at 9:14

2 Answers 2

The documentation on PITR recovery that comes with pgpool is really incomplete, and downright misleading at points. It suggests keeping all the archive files on the master than then having a recovery_command that uses scp to copy from there to each standby. That's worthless. Each standby needs to have its own copy of the archive, or there's no real redundancy there. If the master fails in that situation, you have nothing useful. The archive_command on the master should distribute files to the standby systems instead. There is no need to save a copy of the archive on the master at all if you do that.

For cleaning up the archives on each standby, the poor recommendation for the recovery_command given there shouldn't be followed either. Instead pg_standby should be called as the recovery command. See the real documentation on Warm Standby Servers for more details. If you're using pg_standby, one of the things it should gets passed is the "%r" parameter, which is the information needed to remove old archive logs. Use pg_standby that way and it will clean up the archives for you.

The main non-trivial part here is writing an archive_command script that properly copies data to both standby servers. I don't have a good example to point at, but what you need to do is check for errors after each copy, and provide a success return code only if both copies happen.

share|improve this answer
    
For a good solution to correctly distribute archive files, I use OmniPITR, from OmniLabs: github.com/omniti-labs/omnipitr –  François Beausoleil Nov 30 '12 at 14:28

yes, at that point you can safely remove those logs which are in what you have archived. Be a little careful though. You don't want to remove logs that have not yet been archived!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.