On 1/29/15 7:07 PM, Jim Nasby wrote:
>> Ultimately, there is no single best method. It depends a lot on your
>> environment. I would prefer the official documents to contain very safe
>> methods.
>
> How do we define safe though? Your method leaves you without a backup
> server until your base backup completes and the replica catches up. I
> think we do a dis-service to our users by not pointing that out and
> providing a potential alternate *so long as we spell out the
> tradeoffs/risks*.
My method leaves you without a replica, but not without a *backup* as
long as you are shipping WAL somewhere safe. You can set
archive_timeout to something small if you want to make this safer. This
is more practical in 9.4 since unused WAL space is zeroed.
OK, I'm willing to admit it would be better to have the option with all
caveats, so long as they are strongly worded.
> Ultimately, I think this thread really shows the very large need for a
> tool that understands things like LSNs to provide rsync-ish behavior
> that's actually safe.
Safe backups can be done without LSNs provided you are willing to trust
your timestamps.
> FWIW, I personally am very leery of relying on pg_upgrade. It's too
> easy to introduce bugs, doesn't handle all cases, and provides no
> option for going back to your previous version without losing data. I
> much prefer old_version -- londiste --> new_version, and then doing
> the upgrade by reversing the direction of replication.
I think the official docs need to stick with options that are core?
I avoid pg_upgrade wherever it is practical. However, sometimes it
really is the best option.
> I also don't entirely trust PITR backups. It's too easy to
> accidentally break them in subtle ways.
Agreed in general, but I've been doing a lot of work to make this not be
true anymore.
--
- David Steele
david@pgmasters.net