Thread: Hard upgrade (everything)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi list, I've asked this question already on the PostGIS list, but I think it might get great answers here too. I'm running two database cluster with PostGIS 1.5 and PostgreSQL 9.1 on FreeBSD 9.2-RELEASE-p3 and apparently my PostGIS and PostgreSQL are 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/ iQEcBAEBAgAGBQJS80FVAAoJEHopqW0d1cQVjzMIAJQ6povfOSYg0NIw5DXF0KlZ 0MQQjwoOwRIPrCkllwDEpmioI2OtkBH03LYuQQYi8SArovtBRlTwyvZsUgFuuxy6 qAQhHcGxLyJPvxBIXVRhqnmn89a1otGxXTI+ZedNbJsj74vW0h29KRBAyklphe/C iAGw8+2zr0yiBLJdHWZvnMdS0PkL4jc7UY1XfmCg3AvNQU1EgiUYdWOEn26fqj0g bXrpHERgv8c+Hk8r8/G4WRD6rC0aMirB0lynxn+FHhSc9mzXUbDbER99M06vXrtF uIIeOTfr/Pu5eyjHDc3stg2LAtoNTvnvvJ0S+5Shi6ndLRy3P7AHZ6y915AMkRA= =4KIY -----END PGP SIGNATURE-----
I would use normal pg_dump and pg_restore for the DBs and not utils/postgis_restore.pl. Also, AFTER I backup all databases and everything else, you could try to upgrade Postgis without upgrading PostgreSQL by buliding from source e.g. http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1304src. There it says that Postgis 2.1 could be used with PostgreSQL 9.1. This will give you access to Postgis 2.1 features without reinstalling everything. Of course normally I would not upgrade if this is an 1-2 years project, unless I 100% need Postgis 2.1 features not present in 1.5. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hard-upgrade-everything-tp5790801p5790811.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On my private computer I upgraded first the postgres to 9.3, then upgraded postgis.
Sadly according to http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS ,By the way postgis is very easy to compil with ubuntu (use package system to get dependecies, then simply sudo ./autogen.sh, sudo ./configure, make , sudo make install)
Cheers,2014-02-06 alexandros_e <alexandros.ef@gmail.com>:
I would use normal pg_dump and pg_restore for the DBs and not
utils/postgis_restore.pl. Also, AFTER I backup all databases and everything
else, you could try to upgrade Postgis without upgrading PostgreSQL by
buliding from source e.g.
http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21Ubuntu1304src. There it
says that Postgis 2.1 could be used with PostgreSQL 9.1. This will give you
access to Postgis 2.1 features without reinstalling everything.
Of course normally I would not upgrade if this is an 1-2 years project,
unless I 100% need Postgis 2.1 features not present in 1.5.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hard-upgrade-everything-tp5790801p5790811.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Feb 6, 2014 at 10:07:18AM +0100, Rémi Cura wrote: > On my private computer I upgraded first the postgres to 9.3, then upgraded > postgis. > Sadly according to http://trac.osgeo.org/postgis/wiki/ > UsersWikiPostgreSQLPostGIS , > postgis 1.5 is not compatible with postgres 9.3. > However POstgis 2.1 is compatible with you current postgres option. > So as suggested you can upgrade postgis (see hard/soft upgrade), the upgrade > postgres. > By the way postgis is very easy to compil with ubuntu (use package system to > get dependecies, then simply sudo ./autogen.sh, sudo ./configure, make , sudo > make install) Yes, that is the order I have head. You can upgrade postgis from 1.5 to 2.1 on the old server, then use pg_upgrade to upgrade Postgres. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Hi everybody, I just wanted to let you know my notes I took during the upgrade process from Postgresql 9.1 to 9.3 and Postgis 1.5 to 2.1. Maybe someone finds them useful. I'm running the cluster on FreeBSD 9.2 so all commands apply to FreeBSD of course, but it should be fairly easy to translate the process to any other OS. So here they come: -- plain sql files are suffixe with .sql, binary files with .dump -- -- (double dashes) are comments, > is user shell, # is root shell, $ is pqsql user shell -- just in case > pg_dumpall -U postgres -h localhost -f cluster.sql -- pg_dumpall for roles on the cluster > pg_dumpall --roles-only -f roles.sql -- dump each database > pg_dump -h localhost -U postgres -Fc -b -v -f db.dump db -- become root > su - -- update the ports tree # portsnap fetch update -- stop the cluster # service postgresql stop -- delete the target dir contents -- remember to backup also the config files! # rm -r /data/pgdata/* -- uninstall the old versions -- watch out for dependent ports! # pkg delete -f postgis-1.5.3_3 postgresql-contrib-9.1.9 postgresql91-client-9.1.11 postgresql91-server-9.1.10 -- postgresql91-client-9.1.11 is required by: py27-psycopg2-2.5.1 mapnik-2.2.0_2 gdal-1.10.1_1 mapserver-6.2.1_3 php5-pgsql-5.4.23 php5-pdo_pgsql-5.4.23 py27-gdal-1.10.1 php5-extensions-1.7 osm2pgsql-0.84.0, deleting anyway -- install PostgreSQL Server, Client, Contrib (client comes as dependency anyway) # portmaster databases/postgresql93-server databases/postgresql93-contrib -- install PostGIS # portmaster databases/postgis21 -- rebuild ports that depend on (the old) postgresql-client # portmaster py27-psycopg2 mapnik-2 mapserver php5-pgsql php5-pdo_pgsql php5-extensions osm2pgsql -- init database cluster & postgis # su pgsql $ initdb --pgdata=MOUNTPOINT-DATA/pgdata --locale=de_DE.UTF-8 $ exit # service postgresl start # su pgsql $ createuser -srdP postgres $ exit # psql -U postgres -d postgres postgres=# create extension postgis; // and others if required postgres=# \q # exit -- install legacy.sql if required (from 1.5 to 2.1 it was) -- legacy.sql is in /usr/local/share/postgresql/contrib/postgis-2.1/legacy.sql # psql -U postgres -f /usr/local/share/postgresql/contrib/postgis-2.1/legacy.sql -- restore the roles to the cluster > psql -d postgres -U postgres -f roles.sql -- restore the databases through postgis_restore.pl -- FreeBSD: postgis_restore.pl is in /usr/local/share/postgis/utils/ > perl /usr/local/share/postgis/utils/postgis_restore.pl epc.dump | psql -U postgres epc -- uninstall legacy.sql > psql -U postgres -f /usr/local/share/postgresql/contrib/postgis-2.1/uninstall_legacy.sql Hth, Frank Am 2014-02-12 18:58, schrieb Bruce Momjian: > On Thu, Feb 6, 2014 at 10:07:18AM +0100, Rémi Cura wrote: >> On my private computer I upgraded first the postgres to 9.3, then upgraded >> postgis. >> Sadly according to http://trac.osgeo.org/postgis/wiki/ >> UsersWikiPostgreSQLPostGIS , >> postgis 1.5 is not compatible with postgres 9.3. >> However POstgis 2.1 is compatible with you current postgres option. >> So as suggested you can upgrade postgis (see hard/soft upgrade), the upgrade >> postgres. >> By the way postgis is very easy to compil with ubuntu (use package system to >> get dependecies, then simply sudo ./autogen.sh, sudo ./configure, make , sudo >> make install) > > Yes, that is the order I have head. You can upgrade postgis from 1.5 to > 2.1 on the old server, then use pg_upgrade to upgrade Postgres. > -- 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