Upgrade from 10 to 11
I've just (belatedly) updated Postgresql from version 10 to 11, and had a couple of problems.
pg_upgrade failed, and I couldn't see anything that looked like a helpful error message, so I resorted to the manual method as described in section 7.1 of this page.
That initially failed too, but I got it to work by using
/opt/pgsql-10/bin/pg_dumpall instead of just
pg_dumpall. So the steps I used were:
# systemctl stop postgresql.service # mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data # chown postgres:postgres /var/lib/postgres/data [postgres]$ initdb -D '/var/lib/postgres/data' [postgres]$ /opt/pgsql-10/bin/pg_ctl -D /var/lib/postgres/olddata/ start [postgres]$ /opt/pgsql-10/bin/pg_dumpall -f /tmp/old_backup.sql [postgres]$ /opt/pgsql-10/bin/pg_ctl -D /var/lib/postgres/olddata/ stop # systemctl start postgresql.service [postgres]$ psql -f /tmp/old_backup.sql postgres
- It is hard to argue about errors without seeing them. Upstream recommends to use pg_dumpall from the newer version, so you should probably ask upstream about the problems. -- Lahwaacz (talk) 18:43, 18 November 2018 (UTC)
- I failed to note the exact errors at the time, but one related to a version of a library being missing, so I assumed that the problems were caused by the fact that my whole system had been very out of date before I did a system update and then upgraded postgresql. My point was that a tweak to the instructions on this page was required to solve the problem. ChrisDennis (talk) 18:45, 23 November 2018 (UTC)
pg_upgrade problem if extensions (like postgis) are used
If a database has an extension installed that required extra libraries (like the case of postgis), one ends up with a serious problem with the pg_upgrade path.
The reason is that the postgresql-old-upgrade package does not have any extra libraries but the postgresql itself.
For instance if I try to migrate the 11 database (I am migrating to 12), I get:
$ pg_upgrade -b /opt/pgsql-$PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/data11 -D /var/lib/postgres/data -c Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for tables WITH OIDS ok Checking for invalid "sql_identifier" user columns ok Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file: loadable_libraries.txt
Failure, exiting $ cat loadable_libraries.txt n'a pas pu charger la bibliothèque « $libdir/postgis-2.5 » : ERREUR: n'a pas pu accéder au fichier « $libdir/postgis-2.5 » : Aucun fichier ou dossier de ce type Base de données : rechtem n'a pas pu charger la bibliothèque « $libdir/rtpostgis-2.5 » : ERREUR: n'a pas pu accéder au fichier « $libdir/rtpostgis-2.5 » : Aucun fichier ou dossier de ce type Base de données : rechtem
This was because postgis was just upgraded to 3.0, but even restoring the 2.5 package failed:
$ cat loadable_libraries.txt could not load library "$libdir/postgis-2.5": ERREUR: n'a pas pu charger la bibliothèque « /usr/lib/postgresql/postgis-2.5.so » : /usr/lib/postgresql/postgis-2.5.so: undefined symbol: AllocSetContextCreateExtended Database: rechtem could not load library "$libdir/rtpostgis-2.5": ERREUR: bibliothèque « /usr/lib/postgresql/rtpostgis-2.5.so » incompatible : versions différentes DETAIL: La version du serveur est 12, celle de la bibliothèque est 11. Database: rechtem
I think a warning should be placed about that problem. Backup to 11 may not be obvious in case the problem is discovered too late.