Re: pg_upgrade and rsync

On 1/29/15 5:53 PM, David Steele wrote:
> On 1/29/15 12:42 PM, Josh Berkus wrote:
>> On 01/29/2015 09:11 AM, Bruce Momjian wrote:
>>> On Thu, Jan 29, 2015 at 12:09:58PM -0500, Andrew Dunstan wrote:
>>>> Then step 2 should specify that it's for the master.
>>> Right. Josh is just listing all the steps --- the pg_upgrade docs
>>> already have that spelled out in detail.
>> What I'm also saying is that, if we expect anyone to be able to follow
>> all of these steps, it has to be very explicit; just saying "Follow the
>> pg_upgrade docs but don't start the master yet" isn't clear enough,
>> because the pg_upgrade docs have a few alternative paths.
>>
>> On the whole, I personally would never follow this procedure at a
>> production site. It's way too fragile and easy to screw up.
>
> I'm in agreement with Josh - I would not use this method. I may be
> wrong, but it makes me extremely nervous.
>
> I prefer to upgrade the primary and get it back up as soon as possible,
> then take a backup and restore it to the replicas. If the replicas are
> being used for read-only queries instead of just redundancy then I
> redirect that traffic to the primary while the replicas are being
> upgraded and restored. This method has the least downtime for the primary.
>
> If you want less downtime overall then it's best to use the hot rsync /
> cold rsync with checksums method, though this depends a lot on the size
> of your database.
>
> 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*.

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.

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 also don't entirely trust PITR backups. It's too easy to accidentally break them in subtle ways.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com