Re: Rotating WAL files

Alexander Farber wrote:
> Hello,
>
> I've just read about WAL and tried to set these 2 commands
> for my test database (which is doing nothing 99% of time):
>
> archive_command = 'cp -v %p /var/lib/pgsql/data/archive/%f'
> archive_timeout = 300 # force a logfile segment switch after this
> # many seconds; 0 is off
>
> And after few days it is already crowded in the archive dir:
>
> # ll /var/lib/pgsql/data/archive/|wc -l
> 1098
>
> # du -hs /var/lib/pgsql/data/archive/
> 18G /var/lib/pgsql/data/archive/
>
> Is there some archive_XXXX command for rotating WAL files
> available (can't find it in the docs) or is it my responsibility
> to get rid of the old files (and how do I identify them then?)
>
> Or should I maybe just set archive_timeout to 0?
> (the doc isn't clear enough for me what happens then)
>
>
You need a setup/script which does the following:

1) In recovery.conf, use recovery_target_time to restore up to a certain
timestamp
2) In your archive dir, run the following to restore WALs up to 24 hours
ago: find . -maxdepth 1 -type f -daystart -mtime +1 -exec mv {}
/somewhere/else/where/your/recover/script/can/find/them/ \; -- change
the mtime arg to suit your needs.
3) Once recovery is complete, remove files using a variation of the
above find command -- something along the lines of: find /mypath -type f
-daystart -mtime +2 -exec rm -vf {} \; -- use +2 here so that you don't
remove everything in case you need a few files from the previous 24
hours later on.

You need to be really careful when running the remove command... if you
lose even one WAL file which postgres may need later on to continue the
restore, you're boned.

-salman