Differences

This shows you the differences between two versions of the page.

--- doc:appunti:linux:sa:postgresql_upgrade_11_to_13 [2022/07/07 11:05] – created niccolo
+++ doc:appunti:linux:sa:postgresql_upgrade_11_to_13 [2023/02/03 12:38] (current) – [Execute pg_upgradecluster] niccolo
@@ Line 10: / Line 10: @@
     * postgresql-13-postgis-3
-The PostgreSQL database will upgrade from version **11.16** to **13.7**, the **PostGIS library** will upgrade from version **2.5.1** to **3.1.1**.
+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**.
-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:
+===== 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>
@@ Line 41: / Line 49: @@
 </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
+  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
+  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>