rigacci.org

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PostgreSQL/PostGIS Upgrade from 11 to 13 ======

Notes about upgrading a **Debian 10 Buster** to **Debian 11 Bullseye**, this causes the following packages upgrade:

  * **Debian 10 Buster**
    * postgresql-11
    * postgresql-11-postgis-2.5
  * **Debian 11 Bullseye**
    * postgresql-13
    * postgresql-13-postgis-3

The **PostgreSQL database** will upgrade from version **11.16** to version **13.7**, the **PostGIS library** will upgrade from version **2.5.1** to version **3.1.1**.

===== After the system upgrade =====

Verify the that the system upgrade installed the new packages, otherwise install them manually:

<code>
apt install postgresql-13-postgis-3 postgresql-13-postgis-3-scripts
</code>

Once the system is fully upgraded, you will find that your databases still run on the older **11/main cluster**. Switch to the **postgres** user and run the **pg_lsclusters** command to verify:

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

As you can see the default **5432/TCP** port is still used by the old version. Connect to both the clusters and check where are **your databases**: they are still **on the old cluster** and they are using the **old PostGIS extension**:

<code>
psql --cluster 11/main
postgres=# \l
        Name        |       Owner        | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------------+--------------------+----------+-------------+-------------+-----------------------
 postgres           | postgres           | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 strade             | strade             | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
...

postgres=# \connect strade
strade=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description                             
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 2.5.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
</code>

===== Execute pg_upgradecluster =====

All the administrative tasks should be performed by the **postgres** user.

Connect to the new cluster and verify that it is empty (only the system databases ''postgres'', ''template0'' and ''template1'' should exist):

<code>
psql --cluster 13/main
postgres=# \l
</code>

So you can remove the 13/main cluster ad perform the automatic upgrade:

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

The old cluster was moved to port **5433/TCP** and turned **down**, the new cluster was moved to the default port **5432/TCP**:

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

Connect to the new cluster and verify that your geographic databases were upgraded to the new PostGIS extension:

<code>
psql --cluster 13/main
postgres=# \connect strade
strade=# \dx
                                     List of installed extensions
  Name   | Version |   Schema   |                             Description                             
---------+---------+------------+---------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 3.1.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
</code>

===== Cleanup =====

All the above operations were made using the **posgres** user (not ''root''). It is time to remove the old cluster and stop the new one:

<code>
pg_dropcluster 11 main 
pg_ctlcluster 13 main stop
</code>

We stopped the new cluster because we need to start it again using the **systemd** subsystem, under the controlo of the **root** user:

<code>
systemctl daemon-reload
systemctl enable postgresql@13-main
systemctl start postgresql@13-main
</code>