Clarification on using pg_upgrade

From: Tory M Blue <tmblue(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Clarification on using pg_upgrade
Date: 2016-03-04 22:27:59
Message-ID: CAEaSS0atiPVYLhMSwtzcZB5efeTxYjYDR1s3OZSshpkAY1VsAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Howdy

Postgres9.2 going to 9.4
CentOS 6.5

So in most of my environments, I use slony and thus use slony replication
for my upgrades (Drop/add nodes etc).

But I've got a pretty big DB just shy of a TB that is on a single node. A
dump restore would take over 48 hours because of index creations etc, so
thought maybe I would look at doing a upgrade via pg_upgrade.

There are some challenges, since I build my rpm's to a standard directory
for binaries and then the data directory. So I will have to move/rename
directories, but when that's done, I'm slightly confused on the pg_upgrade
using link options.

If my data is located in /data

and I link to a new dir in /data1, what actually happens. do I end up with
2 file systems and links and thus am not able to delete or cleanup any old
data, or how does this work?

Also will the reindex creation still happen with this type of in-place
upgrade, as if so, then it may not save too much time vs a dump/import.

I'm nervous about using pg_upgrade but it's really tough to recover from
the jobs that backup during a dump/restore process (2-3 days), so really
trying to wrap my head around pg_upgrade..

Suggestions, opinions on pg_upgrade vs dump/restore, the filesystem/mount
below is what I'm working with.

Filesystem Size Used Avail Use% Mounted on

/dev/sda6 4.0T 1.1T 2.8T 29% /data

Thanks
Tory

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2016-03-04 22:58:11 Re: Clarification on using pg_upgrade
Previous Message Merlin Moncure 2016-03-04 22:18:34 Re: Odd behavior with indices