Benutzer:WEGC1

From PostGIS 2.1 to PostGIS 2.3 (with PostgreSQL 9.4 to PostgreSQL 9.6)

In theory, upgrading PostgreSQL with PostGIS should be straightforward with the ALTER EXTENSION postgis UPDATE TO and pg_upgradecluster commands by hand. Unfortunately, Debian Jessie although having postgresql-9.6 in the backports does not ship with postgresql-9.6-postgis and Debian Stretch does not support postgresql-9.4-postgis. As a result, an easy "soft" upgrade is not possible and you have to do a "hard" upgrade which means dumping the old database in Jessie, upgrading to Stretch and then restoring the database from the dump. As this may be OK for small databases, many systems run huge databases with terabytes of data and the whole dump/restore process can take a lot of time where you have to keep your database in read-only for consistency. If you want to avoid this, you can follow this procedure for doing a soft-upgrade to PostGIS 2.3.1, then installing PostGIS to PostgreSQL 9.6, upgrading and testing the database and finally upgrade to Stretch:

Backup your databases

To be safe, make a custom format dump and perhaps a file system level backup of all your databases before beginning with the upgrade. You may skip this step if feel safe to do so. Refer to https://www.postgresql.org/docs/9.4/static/backup.html for details on taking postgres backups.

#Make a dump for each of your databases
pg_dump -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

#Additionally dump the global entries
pg_dumpall -g -p 5432 > dumpall_globals.sql

Upgrading PostGIS from 2.1 to 2.3 in Debian Jessie

Simply activate jessie-backports in the apt sources and install the package postgresql-9.4-postgis-2.3:

1. Edit /etc/apt/sources.list

   deb http://ftp.at.debian.org/debian/ jessie-backports main contrib non-free
   deb-src http://ftp.at.debian.org/debian/ jessie-backports main contrib non-free
   

2. Install postgresql-9.4-postgis-2.3

   sudo apt update
   sudo aptitude install postgresql-9.4-postgis-2.3
   

   You have to tell aptitude to do the right thing here, meaning to skip the options which do not result in installing the packages postgresql-9.4-postgis-2.3 and postgresql-9.4-postgis-2.3-scripts.

3. Upgrade postgis extension

   psql <yourdb>
   ALTER EXTENSION postgis UPDATE TO "2.3.1";
   

Install PostgreSQL 9.6 with PostGIS 2.3 in Jessie

1. Install postgresql-9.6

   sudo aptitude install postgresql-9.6
   

   Choose the option to upgrade libpq, postgresql-client-common and postgresql-common.

2. Install postgis-2.3.1 from source

   # install required libraries
   aptitude install postgresql-server-dev-9.6 libxml2-dev libproj-dev libgdal-dev
   # download postgis
   cd /usr/local/src
   wget http://download.osgeo.org/postgis/source/postgis-2.3.1.tar.gz
   tar xvzf postgis-2.3.1.tar.gz 
   # configure, build and install
   cd postgis-2.3.1 
   ./configure 
   make 
   make install
   

Use pg_upgradecluster to upgrade your databases

Use the Debian script pg_upgradecluster with method upgrade to upgrade PostgreSQL using pg_upgrade:

# first drop the 9.6 cluster which was created when installing postgresql-9.6
pg_dropcluster 9.6 main --stop
# then upgrade the 9.4 cluster to 9.6
systemctl stop postgresql@9.4-<clustername>.service 
pg_upgradecluster -m upgrade 9.4 <clustername>
# <clustername> is main by default

After this, you can test the new cluster and remove the old one using pg_dropcluster. Also you can remove all postgresql-9.4 packages and upgrade to stretch once you are ready.