pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedClarification on pg_basebackup
7+ messages / 5 participants
[nested] [flat]
* Clarification on pg_basebackup
@ 2014-09-23 15:17 Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
2014-09-24 07:42 ` Re: Clarification on pg_basebackup gparc@free.fr
0 siblings, 2 replies; 7+ messages in thread
From: Campbell, Lance @ 2014-09-23 15:17 UTC (permalink / raw)
To: pgsql-admin
PostgreSQL 9.3
Would someone be able to clarify if one should use the -X option with pg_basebackup when your intention is to include all WAL files created? There are two scenarios. The first scenario listed below makes sense using the -X option. Bus scenario #2 has me a little confused. I don't want to cause any issues when restoring.
1) Use pg_basebackup for a fully standalone restorable snapshot of the database using the -X parameter. When a failure occurs you restore the database to that exact point in time.
2) You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup. Do you want to use the -X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last? Or does it matter. Would PostgreSQL just ignore the duplicate WAL files that were created by the -X option during the pg_basebackup?
Thanks for your clarification. This is a hard scenario to test.
Thanks,
Lance Campbell<http://illinois.edu/person/lance;
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/;
Attachments:
image001.png (image/png, 1.4K) download
view image
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
@ 2014-09-23 16:05 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:18 ` Re: Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
1 sibling, 1 reply; 7+ messages in thread
From: Jason Mathis @ 2014-09-23 16:05 UTC (permalink / raw)
To: pgsql-admin; Campbell, Lance <lance@illinois.edu>
Hi Lance,
Maybe you want another view on it but I fear there is a slight miscommunication from our back and forth last week.
Using the -x will get you to ONE POINT IN TIME. So if you backed up at 9PM you can restore back to that point and that point ONLY. Some people call this point in time, but its only ONE point in time.
If you are archiving the wal files then you can restore to ANY POINT IN TIME. See the difference, its a big deal:) So if you backed up at 9PM and you need your database back to the state at 10am, no problem! Restore your 9PM and all the wal files until 10am. This is ANY POINT IN TIME. I feel the term "point in time” has gotten diluted and now there is two different “point in time;” one or any and that a huge difference.
In all honestly it sounds to me you want ANY POINT IN TIME. So forget about the “-x” and just archive your wal files and its all good. Just make sure you have the base backup + all wal files until the point in time you want. So in order to keep three days of base backups you need at least three days of wal files as well.
Make sense?
On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@illinois.edu) wrote:
PostgreSQL 9.3
Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created? There are two scenarios. The first scenario listed below makes sense using the –X option. Bus scenario #2 has me a little confused. I don’t want to cause any issues when restoring.
1) Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter. When a failure occurs you restore the database to that exact point in time.
2) You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup. Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last? Or does it matter. Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup?
Thanks for your clarification. This is a hard scenario to test.
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
--
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.
Attachments:
image001.png@01CFD717.8CC62810 (application/octet-stream, 1.4K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
@ 2014-09-23 16:18 ` Campbell, Lance <lance@illinois.edu>
2014-09-23 16:38 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:44 ` Re: Clarification on pg_basebackup Jeff Frost <jeff@pgexperts.com>
0 siblings, 2 replies; 7+ messages in thread
From: Campbell, Lance @ 2014-09-23 16:18 UTC (permalink / raw)
To: Jason Mathis <jmathis@redzonesoftware.com>; pgsql-admin
Jason,
Thanks so much. That makes sense. That was what I was trying to communicate. OPIT vs APIT.
So if I am doing a APIT I can do a pg_basebackup each night without the –X option. I save all WAL files. If I need to restore during the day I then only have to restore from the last basebackup and apply all of the WAL files from that time forward. Did I get that correct?
Thanks,
Lance Campbell<http://illinois.edu/person/lance;
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/;
From: Jason Mathis [mailto:jmathis@redzonesoftware.com]
Sent: Tuesday, September 23, 2014 11:06 AM
To: pgsql-admin@postgresql.org; Campbell, Lance
Subject: Re: [ADMIN] Clarification on pg_basebackup
Hi Lance,
Maybe you want another view on it but I fear there is a slight miscommunication from our back and forth last week.
Using the -x will get you to ONE POINT IN TIME. So if you backed up at 9PM you can restore back to that point and that point ONLY. Some people call this point in time, but its only ONE point in time.
If you are archiving the wal files then you can restore to ANY POINT IN TIME. See the difference, its a big deal:) So if you backed up at 9PM and you need your database back to the state at 10am, no problem! Restore your 9PM and all the wal files until 10am. This is ANY POINT IN TIME. I feel the term "point in time” has gotten diluted and now there is two different “point in time;” one or any and that a huge difference.
In all honestly it sounds to me you want ANY POINT IN TIME. So forget about the “-x” and just archive your wal files and its all good. Just make sure you have the base backup + all wal files until the point in time you want. So in order to keep three days of base backups you need at least three days of wal files as well.
Make sense?
On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@illinois.edu<mailto:lance@illinois.edu>) wrote:
PostgreSQL 9.3
Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created? There are two scenarios. The first scenario listed below makes sense using the –X option. Bus scenario #2 has me a little confused. I don’t want to cause any issues when restoring.
1) Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter. When a failure occurs you restore the database to that exact point in time.
2) You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup. Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last? Or does it matter. Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup?
Thanks for your clarification. This is a hard scenario to test.
Thanks,
Lance Campbell<http://illinois.edu/person/lance;
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/;
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.
Attachments:
image001.png (image/png, 1.4K) download
view image
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:18 ` Re: Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
@ 2014-09-23 16:38 ` Jason Mathis <jmathis@redzonesoftware.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jason Mathis @ 2014-09-23 16:38 UTC (permalink / raw)
To: pgsql-admin; Campbell, Lance <lance@illinois.edu>
yep that sounds good!
On September 23, 2014 at 10:18:36 AM, Campbell, Lance (lance@illinois.edu) wrote:
Jason,
Thanks so much. That makes sense. That was what I was trying to communicate. OPIT vs APIT.
So if I am doing a APIT I can do a pg_basebackup each night without the –X option. I save all WAL files. If I need to restore during the day I then only have to restore from the last basebackup and apply all of the WAL files from that time forward. Did I get that correct?
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
From: Jason Mathis [mailto:jmathis@redzonesoftware.com]
Sent: Tuesday, September 23, 2014 11:06 AM
To: pgsql-admin@postgresql.org; Campbell, Lance
Subject: Re: [ADMIN] Clarification on pg_basebackup
Hi Lance,
Maybe you want another view on it but I fear there is a slight miscommunication from our back and forth last week.
Using the -x will get you to ONE POINT IN TIME. So if you backed up at 9PM you can restore back to that point and that point ONLY. Some people call this point in time, but its only ONE point in time.
If you are archiving the wal files then you can restore to ANY POINT IN TIME. See the difference, its a big deal:) So if you backed up at 9PM and you need your database back to the state at 10am, no problem! Restore your 9PM and all the wal files until 10am. This is ANY POINT IN TIME. I feel the term "point in time” has gotten diluted and now there is two different “point in time;” one or any and that a huge difference.
In all honestly it sounds to me you want ANY POINT IN TIME. So forget about the “-x” and just archive your wal files and its all good. Just make sure you have the base backup + all wal files until the point in time you want. So in order to keep three days of base backups you need at least three days of wal files as well.
Make sense?
On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@illinois.edu) wrote:
PostgreSQL 9.3
Would someone be able to clarify if one should use the –X option with pg_basebackup when your intention is to include all WAL files created? There are two scenarios. The first scenario listed below makes sense using the –X option. Bus scenario #2 has me a little confused. I don’t want to cause any issues when restoring.
1) Use pg_basebackup for a fully standalone restorable snapshot of the database using the –X parameter. When a failure occurs you restore the database to that exact point in time.
2) You restore from a pg_basebackup and you also apply all of the WAL files since the last pg_basebackup. Do you want to use the –X option when doing the pg_basebackup if your intention is to have all of the WAL files since you backed up last? Or does it matter. Would PostgreSQL just ignore the duplicate WAL files that were created by the –X option during the pg_basebackup?
Thanks for your clarification. This is a hard scenario to test.
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382
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.
--
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.
Attachments:
image001.png@01CFD720.0B34AAC0 (application/octet-stream, 1.4K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:18 ` Re: Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
@ 2014-09-23 16:44 ` Jeff Frost <jeff@pgexperts.com>
2014-09-24 06:03 ` Re: Clarification on pg_basebackup Rajesh Madiwale <rajeshmadiwale65@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jeff Frost @ 2014-09-23 16:44 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Jason Mathis <jmathis@redzonesoftware.com>; pgsql-admin
That's right.
BTW, rather than rolling your own solution, you probably want to look at a few off the shelf solutions like:
Barman: http://www.pgbarman.org/
WAL-E: https://github.com/wal-e/wal-e
On Sep 23, 2014, at 9:18 AM, Campbell, Lance <lance@illinois.edu> wrote:
> Jason,
> Thanks so much. That makes sense. That was what I was trying to communicate. OPIT vs APIT.
>
> So if I am doing a APIT I can do a pg_basebackup each night without the –X option. I save all WAL files. If I need to restore during the day I then only have to restore from the last basebackup and apply all of the WAL files from that time forward. Did I get that correct?
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Re: Clarification on pg_basebackup Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:18 ` Re: Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:44 ` Re: Clarification on pg_basebackup Jeff Frost <jeff@pgexperts.com>
@ 2014-09-24 06:03 ` Rajesh Madiwale <rajeshmadiwale65@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rajesh Madiwale @ 2014-09-24 06:03 UTC (permalink / raw)
To: Jeff Frost <jeff@pgexperts.com>; +Cc: Campbell, Lance <lance@illinois.edu>; Jason Mathis <jmathis@redzonesoftware.com>; pgsql-admin
On Tue, Sep 23, 2014 at 10:14 PM, Jeff Frost <jeff@pgexperts.com> wrote:
> That's right.
>
Postgres ignore duplicate WAL file so we can recover the data without
using -X option while performing pg_basebackup.
>
> BTW, rather than rolling your own solution, you probably want to look at a
> few off the shelf solutions like:
>
> Barman: http://www.pgbarman.org/
> WAL-E: https://github.com/wal-e/wal-e
>
>
>
> On Sep 23, 2014, at 9:18 AM, Campbell, Lance <lance@illinois.edu> wrote:
>
> Jason,
> Thanks so much. That makes sense. That was what I was trying to
> communicate. OPIT vs APIT.
>
> So if I am doing a APIT I can do a pg_basebackup each night without the –X
> option. I save all WAL files. If I need to restore during the day I then
> only have to restore from the last basebackup and apply all of the WAL
> files from that time forward. Did I get that correct?
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Clarification on pg_basebackup
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
@ 2014-09-24 07:42 ` gparc@free.fr
1 sibling, 0 replies; 7+ messages in thread
From: gparc@free.fr @ 2014-09-24 07:42 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
Selon "Campbell, Lance" <lance@illinois.edu>:
PostgreSQL 9.3
Would someone be able to clarify if one should use the -X option with
pg_basebackup when your intention is to include all WAL files created? There
are two scenarios. The first scenario listed below makes sense using the -X
option. Bus scenario #2 has me a little confused. I don't want to cause any
issues when restoring.
1) Use pg_basebackup for a fully standalone restorable snapshot of the
database using the -X parameter. When a failure occurs you restore the
database to that exact point in time.
2) You restore from a pg_basebackup and you also apply all of the WAL
files since the last pg_basebackup. Do you want to use the -X option when
doing the pg_basebackup if your intention is to have all of the WAL files
since you backed up last? Or does it matter. Would PostgreSQL just ignore
the duplicate WAL files that were created by the -X option during the
pg_basebackup?
Thanks for your clarification. This is a hard scenario to test.
Thanks,
Lance Campbell<http://illinois.edu/person/lance;
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/;
For security, I will do both i.e pg_basebackup -X + continuous WAL archiving.
Reason : you never know what could happen with your WAL archiving process.
At least with -X you will have a "restartable" backup.
Regards
Gilles
--
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] 7+ messages in thread
end of thread, other threads:[~2014-09-24 07:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-23 15:17 Clarification on pg_basebackup Campbell, Lance <lance@illinois.edu>
2014-09-23 16:05 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:18 ` Campbell, Lance <lance@illinois.edu>
2014-09-23 16:38 ` Jason Mathis <jmathis@redzonesoftware.com>
2014-09-23 16:44 ` Jeff Frost <jeff@pgexperts.com>
2014-09-24 06:03 ` Rajesh Madiwale <rajeshmadiwale65@gmail.com>
2014-09-24 07:42 ` gparc@free.fr
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