pg_upgrade?: Upgrade method from/to any version on random OS?

Started by Hans Schouover 8 years ago2 messagesgeneral
Jump to latest
#1Hans Schou
hans.schou@gmail.com

If I ask this question without mention the PG version I upgrade from and
to, and don't mention the operating system, and don't mention replication,
would the standard answer be:
Use pg_upgrade!

I have looked through
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
but it seems more complicated than necessary.

My actual situation was PG version from 9.3 to 9.6, on the same server, no
replication, and OS was Windows.

I did:
* SET PGUSER=postgres
* SET /P PGPASSWORD=
* change port number in 9.3 and restart
* 9.3\bin\pg_dumpall.exe --port=5431 | 9.6\bin\psql.exe --port=5433
* change port number on 9.6 to 5432 and restart, done

It went very good but took 100 minutes - where we had downtime - not so
good.

Would it have been:
* better
* faster
* safer
* easier
with pg_upgrade?

The way I did it is similar to what I have done with Oracle and MySQL. It
feels right.

best regards
Hans

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Hans Schou (#1)
Re: pg_upgrade?: Upgrade method from/to any version on random OS?

On Wed, Sep 27, 2017 at 12:48 PM, Hans Schou <hans.schou@gmail.com> wrote:

I have looked through
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
but it seems more complicated than necessary.

​[perform dump/restore]​

It went very good but took 100 minutes - where we had downtime - not so

good.

​There is a correlation between the decreased downtime that pg_upgrade
facilitates and its complexity.​

If you cannot afford the 2 hours of downtime for dump/restore then likely
learning the pg_upgrade process would be the better choice for you.

David J.