pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR WAL Restore and configuration
4+ messages / 3 participants
[nested] [flat]
* PITR WAL Restore and configuration
@ 2014-08-01 23:09 Techie <techchavez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Techie @ 2014-08-01 23:09 UTC (permalink / raw)
To: pgsql-admin
Hi All,
We are moving to a new backup solution where we will backup direct to the
backup server using a Daily Base backup with pg_start_backup and WAL Log
backups every 4 hours.
In regards to retention we are planning on this.
WAL Logs
Daily 4 to 6 times: 60 days retention.
Base Backup
Daily: Mon-Thursday 7 Day Retention
Weekly: Friday 31 Day Retention
Monthly: Last Day of month: 365 Day Retention
So what I am looking for is some clarification on the Base Backups that are
6 months old for example.
Since my WAL Log backups have a 2 month retention I can do PITR from any
Base Backup + WAL Logs any day from 2 months back.
However let's say for example I want to restore the Base Backup from 6
months ago, I only have the Base Backups.. Will I be able to recover to
that point in time using that Base backup? I have read the PITR
documentation however I still struggle to understand this part of it.
Thanks
Jimmy
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR WAL Restore and configuration
@ 2014-08-03 16:12 Kevin Grittner <kgrittn@ymail.com>
parent: Techie <techchavez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2014-08-03 16:12 UTC (permalink / raw)
To: Techie <techchavez@gmail.com>; pgsql-admin
Techie <techchavez@gmail.com> wrote:
> let's say for example I want to restore the Base Backup from 6
> months ago, I only have the Base Backups.. Will I be able to
> recover to that point in time using that Base backup?
Not without all of the WAL files from the point pg_start_backup()
was run until the point pg_stop_backup() was run. Earlier WAL
files won't matter. You will be able to restore to any point in
time from when pg_stop_backup() onward as long as you have an
unbroken series of WAL files (and any .history files which were
generated).
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR WAL Restore and configuration
@ 2014-08-04 05:53 Techie <techchavez@gmail.com>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Techie @ 2014-08-04 05:53 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-admin
Thanks man, I think I understand after re-reading the PITR WAL
documentation a little better. I think that I need the WAL log or logs
mentioned in the .backup file.
So for example when I run the pg_start_bkup and pg_stop_backup It outputs
something like this into the .backup file
START WAL LOCATION: 111/CD000020 (file 0000000200000111000000CD)
STOP WAL LOCATION: 111/CD0000A8 (file 0000000200000111000000CD)
CHECKPOINT LOCATION: 111/CD000020
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2014-08-02 01:15:10 CDT
LABEL: master_backup
STOP TIME: 2014-08-02 01:16:34 CDT
This means that in ordrt to restore this base backup I need the contents of
the base backup and this WAL log 0000000200000111000000CD, is that correct?
If so I can reference the latest .backup file in the pg_xlog directory for
the base backup.. I think thi is how it works.
Thanks
Jimmy
On Sun, Aug 3, 2014 at 9:12 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Techie <techchavez@gmail.com> wrote:
>
> > let's say for example I want to restore the Base Backup from 6
> > months ago, I only have the Base Backups.. Will I be able to
> > recover to that point in time using that Base backup?
>
> Not without all of the WAL files from the point pg_start_backup()
> was run until the point pg_stop_backup() was run. Earlier WAL
> files won't matter. You will be able to restore to any point in
> time from when pg_stop_backup() onward as long as you have an
> unbroken series of WAL files (and any .history files which were
> generated).
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PITR WAL Restore and configuration
@ 2014-08-04 07:35 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Techie <techchavez@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Albe Laurenz @ 2014-08-04 07:35 UTC (permalink / raw)
To: Techie *EXTERN* <techchavez@gmail.com>; Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-admin
Techie wrote:
> Thanks man, I think I understand after re-reading the PITR WAL documentation a little better. I think that I need the WAL log or logs mentioned in the .backup file.
> So for example when I run the pg_start_bkup and pg_stop_backup It outputs something like this into the .backup file
>
> START WAL LOCATION: 111/CD000020 (file 0000000200000111000000CD)
> STOP WAL LOCATION: 111/CD0000A8 (file 0000000200000111000000CD)
[...]
> This means that in ordrt to restore this base backup I need the contents of the base backup and this WAL log 0000000200000111000000CD, is that correct?
Yes.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-08-04 07:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-01 23:09 PITR WAL Restore and configuration Techie <techchavez@gmail.com>
2014-08-03 16:12 ` Kevin Grittner <kgrittn@ymail.com>
2014-08-04 05:53 ` Techie <techchavez@gmail.com>
2014-08-04 07:35 ` Albe Laurenz <laurenz.albe@wien.gv.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox