pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with PITR WAL Restore and configuration.
4+ messages / 3 participants
[nested] [flat]
* Help with PITR WAL Restore and configuration.
@ 2014-08-01 23:08 Techie <techchavez@gmail.com>
2014-08-04 17:15 ` Re: Help with PITR WAL Restore and configuration. Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 4+ messages in thread
From: Techie @ 2014-08-01 23:08 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: Help with PITR WAL Restore and configuration.
2014-08-01 23:08 Help with PITR WAL Restore and configuration. Techie <techchavez@gmail.com>
@ 2014-08-04 17:15 ` Scott Whitney <scott@journyx.com>
2014-08-04 17:36 ` Re: Help with PITR WAL Restore and configuration. Jason Mathis <jmathis@redzonesoftware.com>
0 siblings, 1 reply; 4+ messages in thread
From: Scott Whitney @ 2014-08-04 17:15 UTC (permalink / raw)
To: Techie <techchavez@gmail.com>; +Cc: pgsql-admin
Let's say that you began this in January. According to your retention plan, you will have:
Base Backups:
--------------
Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014
July 31, 2014
WAL Logs:
Daily WAL logs since Jun 5, 2014
If my understanding is correct, none of your base backups prior to Jun 5, 2014 will be useful for recovering any PITR other than the specific time _of_ the base backup. You cannot replay later WALs on the March backup, for example, as you are missing all of the WAL segments between Mar 31, 2014 and Jun 5, 2014.
So, if I am reading this correctly, the only points which you could recover are:
Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014 + any point in time to your latest WAL backups.
There would be a gap between May 31st and June 5th which would not allow PITR.
----- Original Message -----
> 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: Help with PITR WAL Restore and configuration.
2014-08-01 23:08 Help with PITR WAL Restore and configuration. Techie <techchavez@gmail.com>
2014-08-04 17:15 ` Re: Help with PITR WAL Restore and configuration. Scott Whitney <scott@journyx.com>
@ 2014-08-04 17:36 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-08-04 18:52 ` Re: Help with PITR WAL Restore and configuration. Techie <techchavez@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jason Mathis @ 2014-08-04 17:36 UTC (permalink / raw)
To: Scott Whitney <scott@journyx.com>; Techie <techchavez@gmail.com>; Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
You may want to look at the “-x” option for the older backups.
"Includes the required transaction log files (WAL files) in the backup. This will include all transaction logs generated during the backup. If this option is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup."
http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
Otherwise you need those wal segments to perform a restore.
-jason
On August 4, 2014 at 11:18:40 AM, Scott Whitney (scott@journyx.com) wrote:
Let's say that you began this in January. According to your retention plan, you will have:
Base Backups:
--------------
Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014
July 31, 2014
WAL Logs:
Daily WAL logs since Jun 5, 2014
If my understanding is correct, none of your base backups prior to Jun 5, 2014 will be useful for recovering any PITR other than the specific time _of_ the base backup. You cannot replay later WALs on the March backup, for example, as you are missing all of the WAL segments between Mar 31, 2014 and Jun 5, 2014.
So, if I am reading this correctly, the only points which you could recover are:
Jan 31, 2014
Feb 28, 2014
Mar 31, 2014
Apr 30, 2014
May 31, 2014
Jun 30, 2014 + any point in time to your latest WAL backups.
There would be a gap between May 31st and June 5th which would not allow PITR.
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
--
This transmission contains confidential and privileged information intended
solely for the party identified above. If you receive this message in
error, you must not use it or convey it to others. Please destroy it
immediately and contact the sender at (303) 386-3955 or by return e-mail to
the sender.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help with PITR WAL Restore and configuration.
2014-08-01 23:08 Help with PITR WAL Restore and configuration. Techie <techchavez@gmail.com>
2014-08-04 17:15 ` Re: Help with PITR WAL Restore and configuration. Scott Whitney <scott@journyx.com>
2014-08-04 17:36 ` Re: Help with PITR WAL Restore and configuration. Jason Mathis <jmathis@redzonesoftware.com>
@ 2014-08-04 18:52 ` Techie <techchavez@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Techie @ 2014-08-04 18:52 UTC (permalink / raw)
To: Jason Mathis <jmathis@redzonesoftware.com>; +Cc: Scott Whitney <scott@journyx.com>; Scott Whitney <swhitney@journyx.com>; pgsql-admin
Hmm, ok that's a concern. Therefore in order for this to work properly
(pg_start_bkup method) I need to keep WAL logs for the same retention as
the Base backups.
The concern here is that we are moving to a backup solution that grabs only
changed blocks so it must be a filesystem level backup to work properly.
Actually I do this, run a pg_start_backup then copy the data , then run
pg_start_backup. This happens very fast because the solution grabs only the
changed blocks since the last backup. Although this is the case each backup
is a full backup kinda like synthetic full backup. Using pg_base_backup is
going to copy every block every time and it will be really slow for large
databses.
So I guess I have a few options.
1) Test pg_basebackup with the "plain format" and hopefully this will write
identical data with the exception of the changes each time.
2) Adjust WAL log retention to match Base backup retention.
3) Use a hook at the end of the base backup script to grab the needed WALs
by referencing the .backup file.This would require going to disk target
first instead of direct to backup server.
Thanks again for the clarification, I could have made a mistake here using
different retentions.
Jimmy
On Mon, Aug 4, 2014 at 10:36 AM, Jason Mathis <jmathis@redzonesoftware.com>
wrote:
>
> You may want to look at the “-x” option for the older backups.
>
> "Includes the required transaction log files (WAL files) in the backup.
> This will include all transaction logs generated during the backup. If this
> option is specified, it is possible to start a postmaster directly in the
> extracted directory without the need to consult the log archive, thus
> making this a completely standalone backup."
>
> http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
>
> Otherwise you need those wal segments to perform a restore.
>
> -jason
>
>
> On August 4, 2014 at 11:18:40 AM, Scott Whitney (scott@journyx.com) wrote:
>
> Let's say that you began this in January. According to your retention
> plan, you will have:
>
> Base Backups:
> --------------
> Jan 31, 2014
> Feb 28, 2014
> Mar 31, 2014
> Apr 30, 2014
> May 31, 2014
> Jun 30, 2014
> July 31, 2014
>
> WAL Logs:
> Daily WAL logs since Jun 5, 2014
>
> If my understanding is correct, none of your base backups prior to Jun 5,
> 2014 will be useful for recovering any PITR other than the specific time
> _of_ the base backup. You cannot replay later WALs on the March backup, for
> example, as you are missing all of the WAL segments between Mar 31, 2014
> and Jun 5, 2014.
>
> So, if I am reading this correctly, the only points which you could
> recover are:
>
> Jan 31, 2014
> Feb 28, 2014
> Mar 31, 2014
> Apr 30, 2014
> May 31, 2014
> Jun 30, 2014 + any point in time to your latest WAL backups.
>
> There would be a gap between May 31st and June 5th which would not allow
> PITR.
>
> ------------------------------
>
> 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
>
>
>
> This transmission contains confidential and privileged information
> intended solely for the party identified above. If you receive this message
> in error, you must not use it or convey it to others. Please destroy it
> immediately and contact the sender at (303) 386-3955 or by return e-mail
> to the sender.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-08-04 18:52 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:08 Help with PITR WAL Restore and configuration. Techie <techchavez@gmail.com>
2014-08-04 17:15 ` Scott Whitney <scott@journyx.com>
2014-08-04 17:36 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-08-04 18:52 ` Techie <techchavez@gmail.com>
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