Thread: PostgreSQL Point In Time Recovery

On 10/23/2013 9:10 PM, Jayadevan M wrote:
> I went through
> http://www.postgresql.org/docs/9.3/static/continuous-archiving.html
> and set up the archiving process. With this approach, if my database
> crashes after a couple of weeks after the base backup is taken,
> recovering would mean replaying the WAL logs for about 2 weeks, right?
> To avoid that, what is the standard process followed - take a base
> backup every day or once a week?


before you decide on what interval is appropriate for new base backups,
its probably a good idea to test how long the restore actually takes on
your hardware, with your WAL volume...  then decide.     restoring the
base backup files takes some time, then playing the WAL takes more time,
you need to decide what your acceptable tolerance for restore time is,
and decide on backup intervals appropriately.

I like to keep two sets of base backups with their WAL logs, so I wait
til the newest is done before purging the one done two intervals ago.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

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?



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

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).

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.
>
> Cheers,
>
> Jeff

That makes sense. So we take a new base backup once we feel "Hey , recovery
may take time". Thanks.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775872.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

A warn standby will be almost in sync with the primary, right? So recovery
to point-in-time (like 10 AM this morning) won't be possible. We need a
base, but it shouldn't be so old that it takes hours to catchup- that was my
thought. As John mentioned, looking at the WAL/transaction numbers, time to
recover etc need to be looked at.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775874.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thursday, October 24, 2013 11:13:34 PM Jayadevan wrote:
> Alan Hodgson wrote
>
> > That's basically what warm standby's do, isn't it? As long as they keep
> > recovery open it should work.
>
> A warn standby will be almost in sync with the primary, right? So recovery
> to point-in-time (like 10 AM this morning) won't be possible. We need a
> base, but it shouldn't be so old that it takes hours to catchup- that was my
> thought. As John mentioned, looking at the WAL/transaction numbers, time to
> recover etc need to be looked at.
>

Well, yeah. The point was that you possibly could run it for a while to "catch
up" without taking a new base backup if you desired. You should also keep
copies of it for PITR.

Alan Hodgson wrote
> Well, yeah. The point was that you possibly could run it for a while to
> "catch
> up" without taking a new base backup if you desired. You should also keep
> copies of it for PITR.

Something like this -
delayed replication
<http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html>   might
help. I could say lag by 12 hours, or 10000 transactions...



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/PostgreSQL-Point-In-Time-Recovery-tp5775717p5775997.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.