Re: Backup "Best Practices" - Mailing list pgsql-general

On Nov 28, 2016, at 10:04 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster <israel@ravnalaska.net> wrote:

- What is the "best" (or just a good) method of keeping the WAL archives under control? Obviously when I do a new basebackup I can "cleanup" any old files that said backup doesn't need, 

You have said you might be interested in doing PITR. So you want to delay the cleanup so as to not compromise that ability.  You need to develop a policy on how far back you want to be able to do a PITR.

 

but how do I know what those are?

pg_archivecleanup -n /mnt/server/archiverdir 000000010000000000000010.00000020.backup

Ok, but where does that "000000010000000000000010.00000020.backup" come from? I mean, I can tell it's a WAL segment file name (plus a backup label), but I don't have anything like that in my WAL archives, even though I've run pg_basebackup a couple of times. 

I get one file like that for every pg_basebackup I run.  Could your archive_command be doing something to specifically short-circuit the writing of those files?  Like testing the length of %p or %f?

My archive command is simply a copy - straight out of the examples given in the documentation, actually. Only test I do is to make sure the file doesn't exist before running the copy

Do I have to call something to create that file? Some flag to pg_basebackup? At the moment I am running pg_basebackup such that it generates gziped tar files, if that makes a difference.

That is how I run it as well.  I don't think there is a flag to pg_basebackup which even allows you to bypass the creation of those files.  You are looking in the WAL archive itself, correct?  Not somewhere in a listing of the base.tar.gz file? 

I am looking at the WAL archive itself. One thing that just occurred to me: in my testing, I've been running the base backup from the secondary slave server. Perhaps that makes a difference? I know the slave itself doesn't archive WAL files, but I would have expected the master to get the message a backup was being run and do any needed archiving itself.

Cheers,

Jeff