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@xxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, September 23, 2014 11:06 AM
To: pgsql-admin@xxxxxxxxxxxxxx; Campbell, Lance
Subject: Re: Clarification on
pg_basebackup
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.
On September 23, 2014 at 9:18:37 AM, Campbell, Lance (lance@xxxxxxxxxxxx)
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.
