Dies ist die Benutzerseite des Users WegC1.

Bearbeiten

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

Bearbeiten

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

Bearbeiten

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

Bearbeiten

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

Bearbeiten
  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

Bearbeiten

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.

Test-Zitate

Bearbeiten

[1]

[2]

[3]

  1. Boletín de Lima. Editorial Los Pinos, 1983 (google.de [abgerufen am 28. Dezember 2020]).
  2. Hellmut Grabert: Der AMAZONAS: Geschichte und Probleme eines Stromgebietes zwischen Pazifik und Atlantik. Springer-Verlag, 2013, ISBN 978-3-642-75564-4 (google.de [abgerufen am 28. Dezember 2020]).
  3. How Long Is the Amazon River? Abgerufen am 31. Dezember 2018 (englisch).