Re: Hard upgrade (everything) - Mailing list pgsql-general

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