Differences

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

--- doc:appunti:linux:sa:postgresql_upgrade [2020/03/02 17:38] – [Upgrade cluster from 9.6 to 11] niccolo
+++ doc:appunti:linux:sa:postgresql_upgrade [2022/07/07 10:53] (current) – niccolo
@@ Line 1: / Line 1: @@
-====== PostgreSQL/PostGIS Upgrade ======
+====== 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**.
@@ Line 13: / Line 13: @@
   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:
@@ Line 38: / Line 40: @@
 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//.
-So our strategy will be:
+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** PostGIS databases.
+  - **Drop** the PostGIS databases.
-  - Do a **pg_upgradecluster** to migrate all the remaining databases to the new cluster.
+  - Do a **pg_upgradecluster** to migrate and upgrade all the non-GIS databases to the new cluster.
-  - **Restore** PostGIS database from the dump files.
+  - **Restore** the PostGIS databases from the dump files into the new cluster.
 ===== Dump and DROP the PostGIS databases =====
@@ Line 54: / Line 56: @@
 </code>
-now we can DROP them:
+Then we DROP them:
 <code>
@@ Line 64: / Line 66: @@
 ===== Upgrade cluster from 9.6 to 11 =====
-Drop the new 11 main cluster (which should be empty, actually):
+Drop the new 11/main cluster (which should be indeed empty):
 <code>
@@ Line 80: / Line 82: @@
 <code>
 Fixing hardcoded library paths for stored procedures...
-Upgrading database non_gis_database...
+Upgrading database nongis_database...
-Analyzing database non_gis_database...
+Analyzing database nongis_database...
 </code>
-Verify the new situation. Notice that the new 11 cluster was moved to the default **TCP** port **5432**:
+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>
@@ Line 99: / Line 101: @@
 </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:
+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>
@@ Line 125: / Line 127: @@
 </code>
-finally you can restore the dump, as a regular user (you will be prompted for the DB user password):
+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.