====== PostgreSQL/PostGIS Upgrade from 9.6 to 11 ======

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:

<code>
~# 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
</code>

Notice that the new **11/main** cluster is running on the non-standard **TCP port 5433**.

We can also verify that **all our databases** are running **into the old cluster** 9.6/main:

<code>
~$ psql --cluster 9.6/main
postgres=# \l
        Name        |       Owner        | 
--------------------+--------------------+-
 first_database     | ...                |
 second_database    | ...                |
 third_database     | ...                |
...
</code>

Instead, into the new cluster, only the three system databases should exist: **postgres**, **template0** and **template1**:

<code>
~$ psql --cluster 11/main
postgres=# \l
</code>

===== 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//.

We have several standard (non-GIS) databases and a few PostGIS-enabled ones. You can see if the PostGIS extension is enabled issuing the **\dx** once connected to the database. So our strategy will be:

  - **Dump** the PostGIS-enabled databases into files.
  - **Drop** the PostGIS databases.
  - Do a **pg_upgradecluster** to migrate and upgrade all the non-GIS databases to the new cluster.
  - **Restore** the PostGIS databases from the dump files into the new cluster.

===== Dump and DROP the PostGIS databases =====

First of all, we create a dump of all the PostGIS-enabled databases:

<code>
~# su - postgres
~$ pg_dump -Fc gis_database > gis_database.dump
</code>

Then we DROP them:

<code>
~$ psql 
postgres=# DROP DATABASE gis_database;
DROP DATABASE
</code>

===== Upgrade cluster from 9.6 to 11 =====

Drop the new 11/main cluster (which should be indeed empty):

<code>
pg_dropcluster --stop 11 main
</code>

Do the automatic upgrade of all the non-PostGIS databases:

<code>
pg_upgradecluster 9.6 main
</code>

This will print some information for each database:

<code>
Fixing hardcoded library paths for stored procedures...
Upgrading database nongis_database...
Analyzing database nongis_database...
</code>

Verify the new situation. Notice that the new **11/main** cluster was moved to the default **TCP** port **5432** and it is the only one running:

<code>
~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
</code>

Finally we can drop the old 9.6/main cluster

<code>
~$ pg_dropcluster 9.6 main
</code>

Now the **11/main cluster** is running, but **started by the postgres user**. We want it to be started by the regular **systemd** subsystem. To fix the situation run the following **as the postgres user**:

<code>
~$ pg_ctlcluster 11 main stop
</code>

and the following as the **root user**:

<code>
~# systemctl daemon-reload
~# systemctl stop postgresql@9.6-main
~# systemctl stop postgresql@11-main
~# systemctl start postgresql@11-main
</code>

===== Restore the PostGIS databases =====

We need to CREATE the PostGIS databases as new, users should instead aready exist because they were automatically restored by **pg_upgradecluster**. As the postgres user:

<code>
~$ psql
postgres=# CREATE DATABASE gis_database OWNER gis_owner ENCODING 'UTF8';
postgres=# \connect gis_database
gis_database=# CREATE EXTENSION postgis;
</code>

Finally you can restore the dump, as a regular user (you will be prompted for the DB user password):

<code>
~$ pg_restore -U gis_user -W -h localhost -d gis_database -Fc gis_database.dump
</code>

**NOTICE**: during the restore some warnings will be printed, because some objects already exist into the database and they actually should be not restored: the **public** schema, the **plpgsql** language, the **postgis** extension and the **spatial_ref_sys** table.