[SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

wolfram · 2019-01-04 14:07:42

Hi all,

after upgrading PostgreSQL to 11.1, the PostgreSQL database server could not be started, the status shows:

$ systemctl status postgresql.service 
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2019-01-04 08:49:27 CET; 2h 42min ago

Jan 04 08:49:27 x200s systemd[1]: Starting PostgreSQL database server...
Jan 04 08:49:27 x200s postgres[449]: An old version of the database format was found.
Jan 04 08:49:27 x200s postgres[449]: See https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL
Jan 04 08:49:27 x200s systemd[1]: postgresql.service: Control process exited, code=exited status=1
Jan 04 08:49:27 x200s systemd[1]: postgresql.service: Failed with result 'exit-code'.
Jan 04 08:49:27 x200s systemd[1]: Failed to start PostgreSQL database server.

So I followed the recommendations in the Wiki for upgrading PostgreSQL:

I installed the postgresql-old-upgrade package.
I made sure that PostgreSQL is stopped.
I renamed the databases cluster directory, and created an empty one.

The next step (call "pg_upgrade") failed:

$ pwd
/var/lib/postgres/tmp
$ whoami
postgres
$ pg_upgrade -b /opt/pgsql-10/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
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
Creating dump of global objects                             ok
Creating dump of database schemas
  gis_test                                                  
*failure*

Consult the last few lines of "pg_upgrade_dump_16385.log" for
the probable cause of the failure.
Failure, exiting

The contents of "pg_upgrade_dump_16385.log":

command: "/usr/bin/pg_dump" --host /var/lib/postgres/tmp --port 50432 --username postgres --schema-only --quote-all-identifiers --binary-upgrade --format=custom  --file="pg_upgrade_dump_16385.custom" 'dbname=gis_test' >> "pg_upgrade_dump_16385.log" 2>&1
pg_dump: [archiver (db)] query failed: ERROR:  could not access file "$libdir/postgis-2.5": No such file or directory
pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, a.attidentity, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions ,NULL as attmissingval FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '17927'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attnum

I tried to copy the missing library (along with possible dependencies from the postgis package) into the "$libdir":

$ /opt/pgsql-10/bin/pg_config --libdir
/opt/pgsql-10/lib
# cp /usr/lib/postgresql/address_standardizer.so /opt/pgsql-10/lib/
# cp /usr/lib/postgresql/postgis-2.5.so /opt/pgsql-10/lib/
# cp /usr/lib/postgresql/postgis_topology-2.5.so /opt/pgsql-10/lib/
# cp /usr/lib/postgresql/rtpostgis-2.5.so /opt/pgsql-10/lib/

After running "pg_upgrade" again, the error in the log file looks different:

pg_dump: [archiver (db)] query failed: ERROR:  could not load library "/opt/pgsql-10/lib/postgis-2.5.so": /opt/pgsql-10/lib/postgis-2.5.so: undefined symbol: SearchSysCache3

The output of "ldd":

$ ldd /opt/pgsql-10/lib/postgis-2.5.so 
	linux-vdso.so.1 (0x00007ffd0b38d000)
	libgeos_c.so.1 => /usr/lib/libgeos_c.so.1 (0x00007f6a5c557000)
	libproj.so.13 => /usr/lib/libproj.so.13 (0x00007f6a5c4de000)
	libjson-c.so.4 => /usr/lib/libjson-c.so.4 (0x00007f6a5c4cc000)
	libprotobuf-c.so.1 => /usr/lib/libprotobuf-c.so.1 (0x00007f6a5c4c1000)
	libxml2.so.2 => /usr/lib/libxml2.so.2 (0x00007f6a5c359000)
	libm.so.6 => /usr/lib/libm.so.6 (0x00007f6a5c1d4000)
	libc.so.6 => /usr/lib/libc.so.6 (0x00007f6a5c00e000)
	libgeos-3.7.1.so => /usr/lib/libgeos-3.7.1.so (0x00007f6a5be57000)
	libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00007f6a5bcc8000)
	libgcc_s.so.1 => /usr/lib/libgcc_s.so.1 (0x00007f6a5bcae000)
	libpthread.so.0 => /usr/lib/libpthread.so.0 (0x00007f6a5bc8d000)
	/usr/lib64/ld-linux-x86-64.so.2 (0x00007f6a5c6a0000)
	libdl.so.2 => /usr/lib/libdl.so.2 (0x00007f6a5bc88000)
	libicuuc.so.63 => /usr/lib/libicuuc.so.63 (0x00007f6a5bab6000)
	libz.so.1 => /usr/lib/libz.so.1 (0x00007f6a5b89f000)
	liblzma.so.5 => /usr/lib/liblzma.so.5 (0x00007f6a5b679000)
	libicudata.so.63 => /usr/lib/libicudata.so.63 (0x00007f6a59c8b000)

Am I doing something wrong here?
How can I get back to a working PostgreSQL database including support for the PostGIS extension?

Thank you!

Last edited by wolfram (2019-01-05 14:40:11)

twelveeighty · 2019-01-05 00:45:28

I assume you forgot to back up your database(s) *before* you did a major version upgrade of postgresql? If so, first downgrade postgresql and take a full pg_dump for all the db's you wish to pull forward. My guess is that postgresql-old-upgrade does not handle PostGIS, in which case you'll have to import those databases manually from a pg_dump *after* you've upgraded postgresql to 11. So: 1) downgrade 2) backup your dbs with pg_dump 3) drop the dbs that reference PostGIS 4) upgrade postgresql and postgis 5) recreate the dbs (in a blank state) and then 6) restore the pg_dump backup files.

stronnag · 2019-01-05 08:01:36

You need to plan carefully prior to the upgrade and do not do the upgrade until the new postgis is released.

* IgnorePkg = postgresql postgresql-libs postgis

When all the required packages are available

* copy the (old) postgis library to the $olddir/lib
* Stop the database
* Install the new packages
* Perform the upgrade
* Start the new database

If it all fails, roll the process back as described above and use a backup. In my experience this is only necessary if you've skipped intermediate upgrades to either pgsql or postgis. If you have both the old and new postgis implementations in the correct locations, `pg_upgrade` works with postgis, even for large and complex GIS databases like global OSM.

wolfram · 2019-01-05 14:38:30

Thank you for your responses, with your help I could finally solve the problem!

First I added "IgnorePkg = postgresql* postgis" to /etc/pacman.conf to avoid major PostgreSQL updates by accident in the future.
Then I downgraded postgresql, postgresql-libs and postgis, and made a backup (with pg_dumpall).

I copied the (old) postgis library into the postgresql-old-upgrade lib directory (/opt/pgsql-10/lib/).
After an upgrade of the packages postgresql, postgresql-libs, postgis and postgresql-old-upgrade, the "pg_upgrade" worked without any problems :-)
So I didn't have to restore the backup.

@stronnag: I guess my mistake in the first place was that I put the new postgis library in the $olddir/lib (instead of the one before the update), which led to the incompatibilities / undefined symbols described in my first post.

Thank you again for your help!

gewaaid · 2019-11-26 17:13:26

I am having the same issue. An update brought me form postgresql 11 to 12. No clue how to downgrade or to what version. And just like Wolfram I only use geodata with a postgis extension. I did what the wiki told but I stranded with this:
-------
$ pg_upgrade -b /opt/pgsql-11/bin -B /usr/bin -d /var/lib/postgres/olddata -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
------
Any help would be very welcome.

V1del · 2019-11-26 17:58:53

Please don't necrobump solved topics, make your own thread on whatever new problems you have.

Closing.

Arch Linux

#1 2019-01-04 14:07:42

[SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

#2 2019-01-05 00:45:28

Re: [SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

#3 2019-01-05 08:01:36

Re: [SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

#4 2019-01-05 14:38:30

Re: [SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

#5 2019-11-26 17:13:26

Re: [SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

#6 2019-11-26 17:58:53

Re: [SOLVED] PostgreSQL: Upgrade from 10.x to 11.1 with PostGIS fails

Board footer