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 revisionNext revisionBoth sides next revision | ||
doc:appunti:linux:sa:postgresql_upgrade [2020/03/02 17:20] – [Upgrade cluster from] niccolo | doc:appunti:linux:sa:postgresql_upgrade [2020/03/02 17:48] – [Upgrade cluster from 9.6 to 11] niccolo | ||
---|---|---|---|
Line 13: | Line 13: | ||
11 main 5433 online postgres / | 11 main 5433 online postgres / | ||
</ | </ | ||
+ | |||
+ | 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: | 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//. | 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, so our strategy will be: |
- **Dump** the PostGIS-enabled databases into files. | - **Dump** the PostGIS-enabled databases into files. | ||
- | - **Drop** PostGIS databases. | + | - **Drop** |
- | - Do a **pg_upgradecluster** to migrate all the remaining | + | - Do a **pg_upgradecluster** to migrate |
- | - **Restore** PostGIS | + | - **Restore** |
===== Dump and DROP the PostGIS databases ===== | ===== Dump and DROP the PostGIS databases ===== | ||
Line 54: | Line 56: | ||
</ | </ | ||
- | now we can DROP them: | + | Then we DROP them: |
< | < | ||
Line 64: | Line 66: | ||
===== Upgrade cluster from 9.6 to 11 ===== | ===== Upgrade cluster from 9.6 to 11 ===== | ||
- | Drop the new 11 (empty) | + | Drop the new 11/main cluster |
< | < | ||
pg_dropcluster --stop 11 main | pg_dropcluster --stop 11 main | ||
Line 79: | 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 98: | Line 101: | ||
</ | </ | ||
- | Now the 11 main cluster is running | + | Now the **11 main cluster** is running, but **started by the postgres user**. We want it to be started by the regular |
< | < | ||
Line 104: | Line 107: | ||
</ | </ | ||
- | and as the root user, restore proper systemd status: | + | and the following |
< | < | ||
Line 113: | Line 116: | ||
</ | </ | ||
+ | ===== Restore the PostGIS databases ===== | ||
+ | |||
+ | We need to CREATE the PostGIS databases as new, users should instead aready exist because they were automatically restored by **pg_upgradecluster**. As the postgres user: | ||
+ | |||
+ | < | ||
+ | ~$ psql | ||
+ | postgres=# CREATE DATABASE gis_database OWNER gis_owner ENCODING ' | ||
+ | postgres=# \connect gis_database | ||
+ | gis_database=# | ||
+ | </ | ||
+ | |||
+ | Finally you can restore the dump, as a regular user (you will be prompted for the DB user password): | ||
+ | |||
+ | < | ||
+ | ~$ pg_restore -U gis_user -W -h localhost -d gis_database -Fc gis_database.dump | ||
+ | </ | ||
+ | |||
+ | **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. |
doc/appunti/linux/sa/postgresql_upgrade.txt · Last modified: 2022/07/07 10:53 by niccolo