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.

On Thu, May 16, 2013 at 1:42 PM, Fabio Rueda Carrascosa <
avances123@gmail.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?

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

On Thu, May 16, 2013 at 2:03 PM, Igor Neyman <ineyman@perceptron.com> wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes Lonni. I agree with you.

On Thu, May 16, 2013 at 2:23 PM, Lonni J Friedman <netllama@gmail.com>wrote:

-----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 upgrade your 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's wrong 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 "last resort".
Also, no one should do such things as upgrading Postgres (or any software for that matter) on production system, before "ironing out 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

As usual, you are totally right Tom.

But thanks everybody for the debate. Only I have to add, is near a "must"
to run with --check before actually run the command.

2013/5/16 Tom Lane <tgl@sss.pgh.pa.us>

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. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general