Notes about upgrading a Debian 10 Buster to Debian 11 Bullseye, this causes the following packages upgrade:
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.
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
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):
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
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