[postgis-users] Hard upgrade (everything)

[Frank Broniewski]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi list,

I'm running two database cluster with PostGIS 1.5 and PostgreSQL 9.1
on FreeBSD 9.2-RELEASE-p3 and apparently my PostGIS is a little bit
outdated.
Now the plan is to upgrade both PostGIS to 2.1 and PostgreSQL to 9.3,
which are the latest versions in the ports.

Now, I am a little bit lost with all the precautions and measures one
has to undertake to do the hard upgrade. From what I read on the
PostgreSQL site, I can choose whether I want to use pg_upgrade
(without the need of a intermediate dump) or pg_dumpall to put the
data aside. I presume I can't use pg_upgrade since this wouldn't take
PostGIS into account, right?

That leaves me with pg_dumpall for the PostgreSQL upgrade.
Now, reading the PostGIS instructions to upgrade I come to the
conclusion that a binary dump is required to put the data aside while
doing the upgrade. Thing is pg_dump only dumps one spatial database,
and I have several in my clusters, so I'd need to dump all of them, right?

And here's where my confusion starts, since there are different tools
used for PostgreSQL and PostGIS for the individual upgrade. What would
be the correct procedure to dump a complete cluster in a PostGIS and
PostgreSQL compliant way? My ideas so far:

Step one: Use pg_dumpall to dump the roles and cluster metadata
Step two: Iterate and use pg_dump in binary mode to dump every
database in the cluster
Step three: rename/empty the target drive/folder
Step four: do the upgrade of PostgreSQL and PostGIS
Step five: restore the roles and metadata
Step six: use the command utils/postgis_restore.pl to restore each
individual database

Does that look sound?

On a side note, I tried upgrading each part individually, but the port
dependencies won't let me do that because upgrading PostgreSQL to 9.3
will also pull PostGIS 2.1 and upgrading PostGIS 2.1 will also pull
PostgreSQL 9.3, so I only get the two of them ...


Any tips on the procedure are greatly welcome :-)

Frank

- -- 
Frank BRONIEWSKI

METRICO s.à r.l.
géomètres
technologies d'information géographique
rue des Romains 36
L-5433 NIEDERDONVEN

tél.: +352 26 74 94 - 28
fax.: +352 26 74 94 99
http://www.metrico.lu
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJS77W8AAoJEHopqW0d1cQVohsH/25cquSHlSBPJ/r89NAF7hEn
kLRKp+LPnbWRBj8pgIaLsd1U6jEamr4W92VMBnSHbWIpMyi/SLweSWWnIJgFZKMx
mGCsJc7jgcuSyerOYd5C/timaEoRvrobNP+DcDB1UCKHhE29lxG0+dyzsX27AkUV
dHn1gfPh9Q853XyPWz/pgwM57CQ0LNGNgJtBFrRtYp9CUcVJzr7t1eE59pOUICVE
wCxelV+8mVeDf+9CW3I6cjcA47pKh2dNW/PGce+pXgkHeLuo6I0G1nT9PpEJfBcD
29pY2xGbpwMhEAbtldSEO/4fRsQzBxAfohiJOJDWpSWZ3Th/YuV9Up3mBa0Q17E=
=QlbU
-----END PGP SIGNATURE-----