Re: PostgreSQL Point In Time Recovery

On Thursday, October 24, 2013 11:31:38 AM John R Pierce wrote:
> On 10/24/2013 9:47 AM, Jeff Janes wrote:
> > I restore from my base backup plus WAL quite often. It is how I get a
> > fresh dev or test instance when I want one. (It is also how I have
> > confidence that everything is working well and that I know what I'm
> > doing should the time come to do a real restore). When that starts to
> > take an annoyingly long time, I run a new base backup. How often that
> > is, can be anywhere from days to months, depending on what's going on
> > in the database.
>
> hey, silly idea formed on half a cup of coffee.... if that base backup
> is in the form of a copy of the data directory (as opposed to tar.gz or
> something), could you 'update' it by pointing an instance of postgres at
> it, and then playing the WAL archive into it, then shutting that
> instance down? or would it be impossible to synchronize the ongoing
> new WAL's from the master with the timeline of this?

That's basically what warm standby's do, isn't it? As long as they keep
recovery open it should work.

You can also use rsync to take your base backup - just update the rsync copy.
That's what I do (and keep a separate tarball of that rsync copy, made on the
backup host).