[postgis-users] Upgrade postgres 9.0 -> 9.3 mantainingpostgis1.5?

Tue Feb 25 02:00:25 PST 2014

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-20 17:00, schrieb Frank Broniewski:
> This is really helpful, thank you very much! I'll write down what I'll
> be doing and post my recipe here on the list for further reference for
> others,
> 
> Frank
> 
> Am 2014-02-19 21:56, schrieb Paragon Corporation:
>> Frank, You don't need to use postgis_restore.pl if you are
>> restoring just tables with geometries.  Doesn't hurt to use it
>> though.
>>
>> What postgis_restore.pl does is exclude restoring postgis packaged 
>> functions, types, operators etc. and also renames deprecated table 
>> constraints.  If you do a simple restore over a new postgis, you'll
>> get ambiguous function name conflicts.
>>
>> What I did forget to mention in last post is that if you are coming
>> from PostGIS 1.5, you'll probably want to install
>>
>> legacy.sql
>>
>> File.
>>
>> So steps
>>
>> Create extension postgis; Install legacy.sql
>>
>> Restore your data - if you restore the whole backup that contains
>> postgis old functions -- use postgis_restore.pl (otherwise you can
>> get away with just using pg_restore).
>>
>> Hope that helps, Regina http://www.postgis.us http://postgis.net
>>
>>
>>
>>
>> -----Original Message----- From:
>> postgis-users-bounces at lists.osgeo.org 
>> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Frank
>> Broniewski Sent: Wednesday, February 19, 2014 3:01 AM To:
>> postgis-users at lists.osgeo.org Subject: Re: [postgis-users] Upgrade
>> postgres 9.0 -> 9.3 mantainingpostgis1.5?
>>
>> Hi Regina,
>>
>> I've had a similar question a few days ago and unfortunately no
>> reply so far. When you write
>>
>>> reload your PostGIS tables
>>
>> do you mean to use the postgis_restore.pl script to do that? My
>> package / software system will only let me upgrade both Postgresql
>> (from 9.0 to 9.3) and Postgis (from 1.5 to 2.1) at once and not one
>> after another so I am looking for a solution on how to get by
>> upgrade done smoothly
>>
>> Thanks,
>>
>> Frank
>>
>> Am 2014-02-18 11:41, schrieb Paragon Corporation:
>>> Giorgio,
>>
>>> For 9.2 you can install 1.5.6+ (though we frown on people using
>>> 1.5 with 9.2) . 9.3+ supports only 2.1+
>>
>>> Refer to the Support Matrix for details
>>
>>> http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
>>
>>
>>
>>> Regarding if you can dump table with geometries, putting aside
>>> tables with no reference to postgis whatsoever -- yes you can.
>>
>>> 1) In theory you could just do a pg_upgrade (you might have to
>>> drop your postgis tables and uninstall postgis first) -> 9.3
>>
>>> Then install PostGIS 2.1 on your new server and reload your
>>> postgis tables
>>
>>> or
>>
>>> 2) backup your postgis tables, uninstall PostGIS 1.5, install
>>> PostGIS 2.1, reload your PostGIS tables --> then pg_upgrade over
>>> to 9.3
>>
>>> Hope that helps, Regina http://www.postgis.us http://postgis.net
>>
>>
>>> _____
>>
>>> From: postgis-users-bounces at lists.osgeo.org 
>>> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of
>>> Sorbara, Giorgio (CIOK) Sent: Friday, February 14, 2014 5:49 AM
>>> To: postgis-users at lists.osgeo.org Subject: [postgis-users]
>>> Upgrade postgres 9.0 -> 9.3 mantaining postgis1.5?
>>
>>
>>
>>> Dear all,
>>
>>
>>
>>> I am about to upgrade postgres from 9.0 to 9.3 but the gis team
>>> would like to maintain postgis 1.5 version.
>>
>>>> From what I have browsed around it seems that as of the 9.2
>>>> version only 2.0
>>> is supported.
>>
>>> Can you confirm this?
>>
>>
>>
>>> If that is correct I need to upgrade 1.5 -> 2.0 before upgrading
>>> the server, so far so good.
>>
>>> Then comes another question. the only way to upgrade is to dump
>>> and reimport databases.
>>
>>> Now it happens that we have tables with geometries which count
>>> roughly 10000 records, but in the same database we have tables
>>> with billions records (which I would like not to dump)
>>
>>
>>
>>> Is it possible for the sake of upgrading postgis, to just dump
>>> tables with geometries, putting aside tables with no reference to
>>> postgis whatsoever?
>>
>>
>>
>>> thank you,
>>
>>> g
>>
>>
>>
>>
>>
>>
>>
>>
>>> _______________________________________________ postgis-users
>>> mailing list postgis-users at lists.osgeo.org 
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________ postgis-users
>> mailing list postgis-users at lists.osgeo.org 
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________ postgis-users
>> mailing list postgis-users at lists.osgeo.org 
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
> 

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