Postgres 17 -> 18 upgrade via pacman, now stuck on pg_upgrade because of PostGIS library mismatch

Support System Updates
1

Hi all,

Looking for some advice (or a reality check) on how to cleanly recover from a PostgreSQL upgrade that I let pacman do for me.

  • System: ManjaroLinux 26.0.0 Anh-Linh (x86_64)

  • Previously running PostgreSQL 17 with PostGIS 3 on a data dir under /home/postgresql/17/main

  • pacman -Syu pulled in a huge upgrade (~900 packages) and upgraded PostgreSQL from 17 to 18, plus postgis 3.6.0-3

  • I did not run any migration steps before the upgrade (no pg_upgrade, no dump/restore)

After noticing the version bump, I tried to do an inā€‘place upgrade using the 17 binaries installed under /opt/pgsql-17 (from postgresql-old-upgrade) and my existing 17 data directory:

bash

pg_upgrade -b /opt/pgsql-17/bin -B /usr/bin -d /home/postgresql/17/main -D /home/postgresql/18/main

pg_upgrade consistently fails while dumping the <DB> database (the one using PostGIS) with this error (from the pg_upgrade_dump_*.log):

pg_dump: error: query failed: ERROR: incompatible library ā€œ/opt/pgsql-17/lib/postgis-3.soā€: version mismatch DETAIL: Server is version 17, library is version 18.

Manjaro currently has only this PostGIS package installed:

pacman -Q postgis

postgis 3.6.0-3

pacman -Ql postgis | grep '\.so$'

/usr/lib/postgresql/address_standardizer-3.so

/usr/lib/postgresql/postgis-3.so

/usr/lib/postgresql/postgis_raster-3.so

/usr/lib/postgresql/postgis_topology-3.so

There is no postgis-old-upgrade or postgresql-17-postgis-3 style package available in the repos, and no 17ā€‘specific PostGIS .so on disk. I tried the ā€œobvious but wrongā€ thing and symlinked the 18 postgis-3.so into /opt/pgsql-17/lib, which just gives the version mismatch above (PostgreSQL 17 correctly refuses to load a library built for 18).

I also tried to bypass pg_upgrade and do a straight pg_dump of the 17 <DB> DB:

bash

/opt/pgsql-17/bin/postgres -D /home/postgresql/17/main -p 55432 & /opt/pgsql-17/bin/pg_dump -h /tmp -p 55432 -U postgres -d <DB> -F c -b -v -f

/home/postgresql/_17.backup

That fails with the same error:

ERROR: incompatible library ā€œ/opt/pgsql-17/lib/postgis-3.soā€: version mismatch

DETAIL: Server is version 17, library is version 18.

So at this point:

  • Data directory for 17 is intact and still starts with /opt/pgsql-17/bin/postgres.

  • PostgreSQL 18 and PostGIS 3.6.0-3 are installed in the normal Manjaro locations.

  • There is no 17ā€‘compatible PostGIS library on the system anymore.

  • Both pg_upgrade and pg_dump choke as soon as they touch PostGIS objects, due to the 17 vs 18 ABI mismatch.

What Iā€™m trying to figure out:

  1. What is the ā€œManjaroā€‘correctā€ way to get a PostGIS build that is compatible with PostgreSQL 17 again?

    • Is there an official/archived package (like postgresql-17-postgis-3 or similar) in Manjaro or the Arch archives that I should install, or do I need to build PostGIS from source with PG_CONFIG=/opt/pgsql-17/bin/pg_config and install into /opt/pgsql-17 myself?

  2. Is there a recommended way to handle this upgrade path on Manjaro?

    • E.g. should I fully remove 18, reinstall 17 + PostGIS, get clean dumps, and then redo the 17ā†’18 migration in a more controlled way?

    • Or is there a documented approach for using postgresql-old-upgrade + some PostGIS package that Iā€™ve missed?

  3. If no 17ā€‘compatible PostGIS is available via packages, is ā€œbuild PostGIS from source for 17ā€ the expected route on Manjaro now?

Iā€™m comfortable compiling from source if needed, but before going down that path Iā€™d like to know if thereā€™s an existing Manjaro/Arch workflow for this scenario, or a package Iā€™ve overlooked.

Thanks for any guidance or pointers to docs/PKGBUILDs that address this kind of 17ā†’18 + PostGIS upgrade.

2

Since it is an official package, it is a bug.

If this is important to get up sooner, there is a postgis-git AUR (which gets it from source for you). So it would use the latest, currently 3.6.1, and it supports postgres 18.

Edit:

Actually that may not be maintained.

AUR URL                       : https://aur.archlinux.org/packages/postgis-git
First Submitted               : Tue 29 Oct 2019 04:08:37 PM MDT
Keywords                      : None
Last Modified                 : Wed 27 Jan 2021 05:55:08 AM MST
3

I have not tried it, but the documentation seems to suggest pg_dumpall can dump data from a previous version of PostgeSQL, assuming the version 18 server is running. It might be worth investigating it if pg_upgrade isnā€™t working.

I canā€™t offer much more help - I did a dump before the upgrade.

4

I am looking at converting a mssql to postgresql so I have been looking at the arch wiki.

I have noted that it is recommended to add postgresql packages to the IgnorePkg in pacman.conf.

To ensure you do not accidentally upgrade the database to an incompatible version, it is recommended to skip updates to the PostgreSQL packages.
ā€“ PostgreSQL - ArchWiki

That said - I have next to experience with postgresql - but I would expect that you have the previous postgresql packages in your pacman cache /var/cache/pacman/pkg.

I donā€™t know how far gone you are - but if it is not too far - then perhaps you can use the pacman -U for the relevant packages from the cache.

If that could work you would be in a working state and from there proceed with an upgrade of postgresql.

5

I used to deal with a fair amount of postgres, but itā€™s been a few years.

I have always used pg_upgrade for major version upgrades. Itā€™s efficient, and itā€™s whatā€™s it made for.

But with pg_upgrade I donā€™t think there is an offical Manjaro correct way, because you need both versions of postgres installed. There are a few ways to do this. The easiest, possibly this:

Edit ā€“ Thereā€™s an AUR: Thanks @freggel.doe
Thereā€™s an official package:

pacman -Si postgresql-old-upgrade
Repository      : extra
Name            : postgresql-old-upgrade
Version         : 17.7-1
Description     : Older PostgreSQL for migrating major versions with pg_upgrade
Architecture    : x86_64
URL             : https://www.postgresql.org/
Licenses        : PostgreSQL
Groups          : None
Provides        : None
Depends On      : gcc-libs  glibc  icu  krb5  libldap  libxml2  llvm-libs  lz4  openssl  pam  systemd-libs  util-linux-libs  zlib  zstd  postgresql-libs>=17.7
Optional Deps   : perl: for PL/Perl support
                  python: for PL/Python 3 support
                  tcl: for PL/Tcl support
Conflicts With  : None
Replaces        : None
Download Size   : 16.79 MiB
Installed Size  : 50.89 MiB
Packager        : Jan Alexander Steffens (heftig) <heftig@archlinux.org>
Build Date      : Thu 13 Nov 2025 10:03:43 AM
Validated By    : MD5 Sum  SHA-256 Sum  Signature

This provides you with the old binaries for pg_upgrade.

Then weā€™re back to where I was before..

https://postgis.net/workshops/postgis-intro/upgrades.html

And you will need postgis 3.6.1 to complete the last step.

1 Like
6

Better yet, itā€™s in the repos and not the AUR.

7

Thank you @Phemisters, I was hopeful, but apparently pg_dumpall is still fundamentally a pg_dump ā€“ it did a lot, but had the same hiccup as the other attempts:

pg_dump: error: query failed: ERROR: incompatible library ā€œ/opt/pgsql-17/lib/postgis-3.soā€: version mismatch
DETAIL: Server is version 17, library is version 18.

8

You need to dump it with the old postgres binaries. (Or the old pg_upgrade.)

(My posts have been brief, as itā€™s hard to be at a computer.)

Snuck in some time.. Iā€™ll clarify.

When running postgres you have do do this every major upgrade. So if you did the dump before upgrading to postgres v18, it probably would have worked.

Still not a great way to run a DB.

In this past, especially when Iā€™ve upgraded other developersā€™ databases, I always trusted pg_upgrade more. It was much quicker, and less problems mid-upgrade. Third party apps and insane ā€œout of the box thinkingā€ developers are notorious for making upgrades difficult.

But thereā€™s nothing wrong with doing a dump, then upgrade, then restore. Especially if you have tested the procedure.

I would still try to use the official package (that I thought was an AUR). Itā€™s there just for this purpose. But PostGIS does make this part a little more difficult, as you have to get it working on this version too. I think I could do it, but I really donā€™t know much about it.

But ignoring that hurdle, if you used that package for upgrades..

Where-ever pacman -Ql postgresql-old-upgrade gets installed are where the old binaries are.

  • With pg_upgrade, thatā€™s your --old-bindir
  • Or use the pg_dump(all) from the above package

Other-wise now, your other option is to downgrade postgres. And now I think thatā€™s messier, and itā€™s not just one package.

9

Thanks to everybody for the suggestions. I will henceforth add postgres packages to IgnorePkg so I donā€™t make the same mistake twice.

Update on how this was resolved, in case anyone hits the same issue later.

As suspected, the core problem was that my PostgreSQL 17 instance no longer had a PostGIS library built for 17. The only postgis-3.so on the system was the one from postgis 3.6.0-3, compiled against PostgreSQL 18, and 17 quite correctly refused to load it (ā€œServer is version 17, library is version 18ā€). That broke both pg_upgrade and any attempt to pg_dump / pg_dumpall the PostGIS database.

The fix was to reinstall a 17ā€‘compatible PostGIS into the /opt/pgsql-17 tree and then use pg_dumpall from 17 to stream into 18:

  1. Stopped the 17 server:

    % /opt/pgsql-17/bin/pg_ctl -D /home/postgresql/17/main stop

  2. Downloaded and built PostGIS 3.5.2 from source, explicitly pointing it at the 17 pg_config so it would install into /opt/pgsql-17:

    % cd /home/postgresql
    % wget https://download.osgeo.org/postgis/source/postgis-3.5.2.tar.gz
    % tar xvzf postgis-3.5.2.tar.gz cd postgis-3.5.2/
    % ./configure --with-pgconfig=/opt/pgsql-17/bin/pg_config
    % make
    % sudo make install

    This dropped postgis-3.so and the SQL files under /opt/pgsql-17/lib and /opt/pgsql-17/share/... for the 17 ecosystem.

  3. Restarted the 17 server on a nonā€‘default port and verified PostGIS now loaded cleanly:
    % /opt/pgsql-17/bin/postgres -D /home/postgresql/17/main -p 55432 > /home/postgresql/pg17.log 2>&1 &
    % /opt/pgsql-17/bin/psql -h 127.0.0.1 -p 55432 -d <DB> -U postgres -c "SELECT postgis_full_version();"
    % /opt/pgsql-17/bin/psql -h 127.0.0.1 -p 55432 -d postgres -U postgres -c "SELECT version();"

    Once postgis_full_version() worked without the versionā€‘mismatch error, pg_dump/pg_dumpall could finally traverse the PostGIS objects again.

  4. Started the 18 server on the normal port:

    % /usr/bin/pg_ctl -D /home/postgresql/18/main start psql -h 127.0.0.1 -p 5432 -d postgres -U postgres -c "SELECT version();"

  5. Ran a full cluster migration from 17 ā†’ 18 using the classic pg_dumpall | psql pipeline:

    % pg_dumpall -h 127.0.0.1 -p 55432 -U postgres | \
    psql -h 127.0.0.1 -p 5432 -d postgres -U postgres

This time there were no PostGIS library errors, and the previously failing PostGIS database came across correctly into the 18 cluster.

I tried the ALTER EXTENSION postgis UPDATE TO ā€˜3.6.1ā€™; but got

ERROR: extension ā€œpostgisā€ has no update path from version ā€œ3.6.0ā€ to version ā€œ3.6.1ā€

I doubt that will be an issue; I presume when 3.6.1 makes it into the manjaro repo, that will get fixed up, and I donā€™t expect any problems until (and after) then.

2 Likes