Thread: pg_upgrade link mode

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of AI Rumman
Sent: Thursday, May 16, 2013 1:56 PM
To: Fabio Rueda Carrascosa
Cc: pgsql-general
Subject: Re: [GENERAL] pg_upgrade link mode

I always think its a bit risky to use link instead of copying. However, I'd suggest to try the  --check at first of
pg_upgrade.

--------------------------------------------------------------

Why?
Do you have specific experience, when link mode caused any problems?
Could you share?

Regards,
Igor Neyman

On Thu, May 16, 2013 at 11:03 AM, Igor Neyman <ineyman@perceptron.com> wrote:
>
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of AI Rumman
> Sent: Thursday, May 16, 2013 1:56 PM
> To: Fabio Rueda Carrascosa
> Cc: pgsql-general
> Subject: Re: [GENERAL] pg_upgrade link mode
>
> I always think its a bit risky to use link instead of copying. However, I'd suggest to try the  --check at first of
pg_upgrade.
>
> --------------------------------------------------------------
>
> Why?
> Do you have specific experience, when link mode caused any problems?
> Could you share?

I assume what he's referring to is if the upgrade gets partially
completed and fails for any reason, then you have a broken mess, with
no simple rollback path.  Since your database is only about 1GB in
size, it shouldn't take very long to run a base backup before doing
the upgrade.  You can send that backup over the network to a remote
system, so that you have a fallback solution if the upgrade fails.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Lonni J Friedman
> Sent: Thursday, May 16, 2013 2:23 PM
> To: Igor Neyman
> Cc: AI Rumman; Fabio Rueda Carrascosa; pgsql-general
> Subject: Re: [GENERAL] pg_upgrade link mode
> 
> On Thu, May 16, 2013 at 11:03 AM, Igor Neyman <ineyman@perceptron.com>
> wrote:
> >
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of AI Rumman
> > Sent: Thursday, May 16, 2013 1:56 PM
> > To: Fabio Rueda Carrascosa
> > Cc: pgsql-general
> > Subject: Re: [GENERAL] pg_upgrade link mode
> >
> > I always think its a bit risky to use link instead of copying.
> However, I'd suggest to try the  --check at first of pg_upgrade.
> >
> > --------------------------------------------------------------
> >
> > Why?
> > Do you have specific experience, when link mode caused any problems?
> > Could you share?
> 
> I assume what he's referring to is if the upgrade gets partially
> completed and fails for any reason, then you have a broken mess, with
> no simple rollback path.  Since your database is only about 1GB in
> size, it shouldn't take very long to run a base backup before doing the
> upgrade.  You can send that backup over the network to a remote system,
> so that you have a fallback solution if the upgrade fails.
> 
> 
> --

Well, you should have current (and tested in regards to restore procedure) database backup regardless whether you
upgradeyour Postgres or just run it in normal "everyday mode".
 
And, if you plan to upgrade, you double check that your backup is absolutely valid and "restorable".

Now, pg_upgrade in link mode checks a lot of things before getting to the point of irreversible changes, and if
anything'swrong you can start older cluster, fix any problems pg_upgrade found and re-run pg_upgrade.
 
And if anything still breaks during pg_upgrade, you should have your database backup (that I talked about before) as a
"lastresort".
 
Also, no one should do such things as upgrading Postgres (or any software for that matter) on production system, before
"ironingout all kinks" on the test system.
 

I tested my upgrade process (which includes pg_upgrade in link mode) on test system, and now I run it on production
systems(note "plural") without fear of breaking anything, besides the fact that every production system of course has
"restorable"backup.
 

Regards,
Igor Neyman

Fabio Rueda Carrascosa <avances123@gmail.com> writes:
> Hello, I have a 9.1 cluster with 50 databases, only one table per db with
> 2000 rows only, but a lot of schema around each one (postgis databases)

> The old cluster size is 1GB

> du -chs /var/lib/postgresql/9.1/main/
> 1.1G

> now I run a pg_upgrade to 9.2 with hard link mode,

> pg_upgrade  --link \
>     --old-datadir=/var/lib/postgresql/9.1/main \
>     --new-datadir=/var/lib/postgresql/9.2/main \
>     --old-bindir=/usr/lib/postgresql/9.1/bin \
>     --new-bindir=/usr/lib/postgresql/9.2/bin

> du -chs /var/lib/postgresql/9.2/main/
> 880M

> Is the expected behaviour? I can't double the space in production.

I don't think anybody actually answered your original question.
The above doesn't represent a doubling of disk space, it just shows that
"du" only tells you how much file space is linked into the directory
tree you ask it about.  That is, there's lots of overlap between the
first and second du results.  If you try "du" passing it both directory
trees together, it should give you a number for the total space
consumption that's not much more than 1.1G.  (Depending on which version
of "du" you're using, you may need to give it an additional switch to
tell it not to double-count multiply-linked files.)

            regards, tom lane

On Thu, May 16, 2013 at 02:21:16PM -0400, AI Rumman wrote:
> I told you I would never go for a production with link mode. However, I was
> working with pg_upgrade copy mode and in the middle I got an error saying
> missing some extensions. I rollback and start the operation after setting
> everything up.
> I don't know how it will behave in link mode if you fail in the middle.

I guess a lot of people prefer to guess about the risks of --link mode
rather than read the pg_upgrade manual, which says:

    http://www.postgresql.org/docs/9.3/static/pgupgrade.html

    If you use link mode, the upgrade will be much faster (no file copying),
    but you will not be able to access your old cluster once you start the
    new cluster after the upgrade. Link mode also requires that the old and
    new cluster data directories be in the same file system. See pg_upgrade
    --help for a full list of options.

So, no, you don't get a mess if the upgrade fails in the middle, even in
link mode, but you would be in trouble if you started the new server and
wanted to revert to the old server.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +