Re: Backup "Best Practices"

[Israel Brewster <israel@xxxxxxxxxxxxxx>]

On Nov 29, 2016, at 8:12 AM, Israel Brewster <israel@xxxxxxxxxxxxxx> wrote:

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

On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster <israel@xxxxxxxxxxxxxx> 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.

So to test, I ran a base backup from my primary server rather than the secondary - and the .backup file WAS indeed created in the WAL archive directory. So I guess that means I have to run base backups from the primary server. Are there any performance implications to doing this that I should be aware of? Something that would imply I need to make sure to run the backup during lull periods?

-----------------------------------------------

Israel Brewster

Systems Analyst II

Ravn Alaska

5245 Airport Industrial Rd

Fairbanks, AK 99709

(907) 450-7293

-----------------------------------------------

Cheers,

Jeff