doc:appunti:linux:sa:postgresql_upgrade
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
doc:appunti:linux:sa:postgresql_upgrade [2020/03/02 17:46] – [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/ | + | ====== PostgreSQL/ |
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**. | 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 40: | 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//. | 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//. | ||
- | We have several standard (non-GIS) databases and a few PostGIS-enabled ones, 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. | - **Dump** the PostGIS-enabled databases into files. | ||
Line 66: | Line 66: | ||
===== Upgrade cluster from 9.6 to 11 ===== | ===== Upgrade cluster from 9.6 to 11 ===== | ||
- | Drop the new 11 main cluster (which should be indeed empty): | + | Drop the new 11/main cluster (which should be indeed empty): |
< | < | ||
Line 82: | Line 82: | ||
< | < | ||
Fixing hardcoded library paths for stored procedures... | Fixing hardcoded library paths for stored procedures... | ||
- | Upgrading database | + | Upgrading database |
- | Analyzing database | + | Analyzing database |
</ | </ | ||
- | 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/ |
< | < | ||
Line 101: | Line 101: | ||
</ | </ | ||
- | 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 |
< | < |
doc/appunti/linux/sa/postgresql_upgrade.1583167593.txt.gz · Last modified: 2020/03/02 17:46 by niccolo