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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Oct 23, 2013 at 9:10 PM, Jayadevan M <maymala.jayadevan@gmail.com>wrote:

Hi,
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?
Regards,
Jayadevan

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt; 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Before going through something like delayed replication, you really want to
consider using zfs or lvm and taking regular snapshots on your hot or warm
standby. In the event of the accidental table drop, you can just roll back
to the snapshot prior and then do PITR from there.

Greg Haase

On Fri, Oct 25, 2013 at 11:14 PM, Jayadevan <maymala.jayadevan@gmail.com>wrote: