User Tools

Site Tools


doc:appunti:linux:sa:postgresql_upgrade_11_to_13

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:

apt install postgresql-13-postgis-3 postgresql-13-postgis-3-scripts

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:

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

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:

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

Execute pg_upgradecluster

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

psql --cluster 13/main
postgres=# \l

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

pg_dropcluster --stop 13 main
pg_upgradecluster 11 main

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

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

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

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

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:

pg_dropcluster 11 main 
pg_ctlcluster 13 main stop

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

systemctl daemon-reload
systemctl enable postgresql@13-main
systemctl start postgresql@13-main
doc/appunti/linux/sa/postgresql_upgrade_11_to_13.txt · Last modified: 2022/07/07 11:45 by niccolo