PostGIS not working with Postgres 13

Support Software & Applications
1

I recently performed a system update, which updated my postgres installation to 13.1.2. However, this produces the following issue when trying to use the postgis extension in postgres

CREATE EXTENSION postgis;
ERROR:  PostGIS built for PostgreSQL 12.0 cannot be loaded in PostgreSQL 13.1
CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE

I have re-installed the latest available version of postgis (3.0.2-2), however this does not help. I imagine that the postgis package must be rebuilt against Postgres 13.

For now I guess I have to downgrade postgres, however I’m a bit reluctant to do so. Unless there’s something else to be done?

I’m a bit oblivious to how the packaging process actually works. I understand that the latest version of postgis in the arch repository is 3.0.3-1. Is there a correct way to request this to be brought over to the manjaro repository?

2

Same problem. The PostgreSQL that is installed on the system was installed as a dependency to PostGIS.

image
image646×553 27.8 KB

3

As a workaround to be able to continue my work, I’ve uninstalled postgresql (possibly unnecessary, however I wasn’t able to completely disable the server quickly), and used it via docker instead. How to do it (with camptocamp postgres image, which has postgis built in)

docker swarm init
docker pull camptocamp/postgres:12-postgis-3
docker service create \
--name postgres \
-p 5432:5432 \
-e 'POSTGRES_PASSWORD=postgres' \
-e 'POSTGRES_USER=postgres' \
-e 'POSTGRES_DB=postgres' \
camptocamp/postgres:12-postgis-3


# Using postgres via interactive docker terminal and psql
# Get container id
docker container ls
docker exec -it [container-id] bash
psql -U postgres -W
4

Thanks for the workaround…

Does anyone know how to request package updates? Should we submit a bug somewhere?

5

Running essential software in Docker is not a fix. That’s about as bad as running Arch in production, as I am learning. :confused:

So is PostGIS on PostgreSQL 13 broken or is this just an Arch thing?

My postGIS-enabled postgreSQL 12 database is inaccessible and the backups are for version 12 of course.

Can anyone recommend any other solution? I also don’t see an easy way to downgrade back to version 12, but there is version 10 and 11 in the AUR.

6

@lk_werter I think Manjaro is a desktop OS and since it’s a rolling release expect things to break… not for production for sure. I pinned all the packages I need for work to a specific version, so they don’t break. It’s not always the packager fault (apart from providing a newer version)… this week I got two broken packages, that had bugs upstream. That comes with the being bleeding-edge thingy.

If you look at the Arch versions you will see that there’s an updated PostGIS package… probably the one that fixes the problem with PostgreSQL 13 :wink:

As for docker, I too was a bit skeptical… though on second thought it’s not that bad. I use PostGIS for development and using docker I don’t ‘pollute’ the OS with packages and their dependencies. On Linux the performance hit is negligible. Also you will not have problems with things getting upgraded/broken. And you can also get the PostgreSQL/PostGIS version combo that you need.

I then found I need PostgreSQL 11 with PostGIS 2.5. I do something similar as @Hogis did and it works perfectly (you have to stop the local PostgreSQL first so ports won’t clash):

docker run --name postgis-11-2.5 -e POSTGRES_PASSWORD=verysecret -d -p 5432:5432 postgis/postgis:11-2.5

More tags (version combos) can be found here: Docker Hub

It’s certainly an easier setup than having to install everything by hand. Note also, that the image already persists the database volume…

7

@lk_werter About your question on getting old versions… use Arch Linux archive: Arch Linux Archive - ArchWiki (in there find the downgrading packages instruction).

To prevent a package for being upgraded, edit /etc/pacman.conf. And add a line:

IgnorePkg   = package_to_keep