Talk:PostgreSQL

From ArchWiki
Jump to navigation Jump to search

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

ChrisDennis (talk) 18:26, 18 November 2018 (UTC)

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.

—This unsigned comment is by Mrechte (talk) 14:07, 29 November 2019 (UTC). Please sign your posts with ~~~~!