Upgrade PostgreSQL 9.5 to 9.6 with PostGIS - pg_upgrade

Question

I'm trying to upgrade a 9.5 server to 9.6, and pg_upgrade gives me the following error:

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* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                        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

Which contains:

could not load library "$libdir/rtpostgis-2.2":
ERROR:  could not access file "$libdir/rtpostgis-2.2": No such file or directory

When I start the server and run "CREATE EXTENSION postgis" and then stop the server, pg_upgrade then gives me an error about a non-empty DB:

New cluster database "postgres" is not empty

So, I'm caught in a catch-22 - I can't upgrade without installing the extension, but once I do it considers the database not empty.

Any tips how to fix this?

Answer 1

The problem is that the postgis extension version in the old cluster is not the same as the library version in the new one. So when it tries to migrate the database, it can't find the older version of the extension.

To fix this, for each my_database that has the postgis extension installed, run:

psql my_database postgres -c 'ALTER EXTENSION postgis UPDATE TO "2.3.2";'

in the old cluster. Then run pg_update.

Answer 2

Your 9.6 install probably has postgis 2.3 and the corresponding rtpostgis-2.3 library. If you had the libaries for postgis 2.2 in your 9.6 installation, pg_upgrade should work fine.

If you need postgis 2.3 you will probably have to pg_dumpall and pg_restore.

Answer 3

I wanted to upgrade from PostGis 2.4.4 to 3.0.2 and I got the same error message. For me it helped to install the missing package by hand using:

psql -d my_database -f /usr/share/postgresql/10/contrib/postgis-3.0/rtpostgis.sql