This is an old revision of the document!
Table of Contents
PostgreSQL/PostGIS Upgrade
The present notes are about upgrading PostgreSQL from Debian 9 Stretch to Debian 10 Buster, i.e. upgrading from PostgreSQL 9.6 to version 11 and consequently upgrading PostGIS 2.3 to PostGIS 2.5.
Preliminary checks
We did the Debian upgrade with the standard apt-get dist-upgrade procedure. Once the upgrade finished, we can verify that both versions of PostgreSQL are running:
~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log 11 main 5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
We can also verify that all our databases are running into the old cluster 9.6/main:
~$ psql --cluster 9.6/main postgres=# \l Name | Owner | --------------------+--------------------+- first_database | ... | second_database | ... | third_database | ... | ...
Instead, into the new cluster, only the three system databases should exist: postgres, template0 and template1:
~$ psql --cluster 11/main postgres=# \l
Upgrade strategy
The pg_upgradecluster can safely upgrade standard databases, but the relative manpage clearly states that: Some PostgreSQL extensions like PostGIS need metadata in auxiliary tables which must not be upgraded from the old version, but rather initialized for the new version before copying the table data.
So our strategy will be:
- Dump the PostGIS-enabled databases into files.
- Drop PostGIS databases.
- Do a pg_upgradecluster to migrate all the remaining databases to the new cluster.
- Restore PostGIS database from the dump files.
Dump and DROP the PostGIS databases
First of all, we create a dump of all the PostGIS-enabled databases:
~# su - postgres ~$ pg_dump -Fc gis_database > gis_database.dump
now we can DROP them:
~$ psql postgres=# DROP DATABASE gis_database; DROP DATABASE
Upgrade cluster from
Drop the new 11 (empty)cluster:
pg_dropcluster --stop 11 main
~# systemctl daemon-reload
pg_upgradecluster 9.6 main