Page MenuHomePhabricator

Packaging PostGIS 3.1 for the new Maps stack
Closed, ResolvedPublic

Description

The Maps team would ideally like to upgrade PostGIS to v3.1 since I believe it comes with performance improvements. This is part of the Maps stack upgrade work some of which is already underway and parts of which have been completed. We prefer that the new stack run on buster, not stretch, so we can start off without needing to worry about backports, etc.

The reason behind the PostGIS version is some performance improvement we would like to use in our spatial queries ( asMVT queries and feature IDs ).
According to the PostGIS docs, PostGIS 3.1 is compatible with Postgres 11 which is already available for debian buster.

Filing this task for the service-ops team to explore the effort involved in packaging this. On IRC, @MoritzMuehlenhoff said that PostGIS 3.1 is already packaged in debian official.

Event Timeline

Pasting in my comments from IRC (which are based on a very quick look):
[16:33] <moritzm> bullseye has 3.1, but it's hard to tell how complex a backport will be, given that maps is still on stretch
[16:33] <moritzm> and the list of build deps in 3.1 is not small
[16:33] <moritzm> and includes things like protobuf and libgdal
[16:34] <moritzm> I think this can only really be estimated if someone gives it a shot for half an hour

@MoritzMuehlenhoff you can assume that maps will be in buster by the time we need PostGIS v3.1. @hnowlan has already started the needed work (T269582: [OSM] perform imposm3 migration in production) and we are very close to having a safe environment to keep going with the Buster migration.

Change 676395 had a related patch set uploaded (by Muehlenhoff; author: Muehlenhoff):

[operations/puppet@production] Add postgresql-server-dev-all to package builder packages

https://gerrit.wikimedia.org/r/676395

@MSantos You can find a backport for buster at https://people.wikimedia.org/~jmm/postgis/ Can you run some tests whether that's what you need? If all is fine, I'll import them to a repository component on apt.wikimedia.org

Change 676395 merged by Muehlenhoff:

[operations/puppet@production] Add postgresql-server-dev-all to package builder packages

https://gerrit.wikimedia.org/r/676395

@MSantos You can find a backport for buster at https://people.wikimedia.org/~jmm/postgis/ Can you run some tests whether that's what you need? If all is fine, I'll import them to a repository component on apt.wikimedia.org

@MoritzMuehlenhoff just installed in the beta cluster and performed some tests and it works fine in a Debian Buster machine running PostgreSQL 11.

gis=# SELECT version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.11 (Debian 11.11-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

gis=# SELECT Postgis_full_version();
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.0, released 2018/12/14" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)" RASTER
(1 row)

Change 677808 had a related patch set uploaded (by Muehlenhoff; author: Muehlenhoff):

[operations/puppet@production] New component for PostGIS 3 backport

https://gerrit.wikimedia.org/r/677808

Change 677808 merged by Muehlenhoff:

[operations/puppet@production] New component for PostGIS 3 backport

https://gerrit.wikimedia.org/r/677808

Mentioned in SAL (#wikimedia-operations) [2021-04-08T08:17:51Z] <moritzm> imported postgis 3.1.1+dfsg-1~wmf1 to component/postgis for buster-wikimedia T277064

@MSantos, @hnowlan : I've uploaded the postgis 3.1.1 backport to the newly created component/postgis for buster. You can add it to the maps Puppet manifests using apt::package_from_component (feel free to add me as reviewer).

fgiunchedi triaged this task as Medium priority.Apr 13 2021, 1:53 PM

Change 693907 had a related patch set uploaded (by Hnowlan; author: Hnowlan):

[operations/puppet@production] postgresql::postgis: use latest packages on buster

https://gerrit.wikimedia.org/r/693907

Change 693907 merged by Hnowlan:

[operations/puppet@production] postgresql::postgis: use latest packages on buster

https://gerrit.wikimedia.org/r/693907

postgis 3.1.1 running on maps1009 successfully.
Just to note for posterity and reference, we hit some issues upgrading, which are _entirely_ related to the fact we had 2.5.1 installed previously and nothing to do with the 3.1.1 packages:

Running a plain ALTER EXTENSION postgis UPDATE as recommended by the documentation didn't work. Forcing the version caused us to get into a weirder state:

gis=# ALTER EXTENSION postgis UPDATE TO "3.1.1";
WARNING:  unpackaging raster
WARNING:  PostGIS Raster functionality has been unpackaged
HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ALTER EXTENSION
gis=# SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis from 3.1.1 to 2.5.1
ERROR:  extension "postgis" has no update path from version "3.1.1" to version "2.5.1"
CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO "2.5.1";"
PL/pgSQL function postgis_extensions_upgrade() line 79 at EXECUTE

After some digging we discovered that /usr/share/postgresql/11/extension/postgis.control was still configured to load postgis 2.5.1. It then turned out that this file provided by 2.5.1 was a static file and not a link and so could not be automatically updated using alternatives:

root@maps1009:~# ls -ls /usr/share/postgresql/11/extension/postgis.control
4 -rw-r--r-- 1 root root 186 Nov 22  2018 /usr/share/postgresql/11/extension/postgis.control
root@maps1009:~# update-alternatives  --config postgresql-11-postgis.control
There is only one alternative in link group postgresql-11-postgis.control (providing /usr/share/postgresql/11/extension/postgis.control): /usr/share/postgresql/11/extension/postgis-3.control
Nothing to configure.
update-alternatives: warning: forcing reinstallation of alternative /usr/share/postgresql/11/extension/postgis-3.control because link group postgresql-11-postgis.control is broken
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/postgis.control with a link
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/address_standardizer.control with a link
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/address_standardizer_data_us.control with a link
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/postgis_sfcgal.control with a link
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/postgis_tiger_geocoder.control with a link
update-alternatives: warning: not replacing /usr/share/postgresql/11/extension/postgis_topology.control with a link

Forcing update fixed this issue via update-alternatives --config postgresql-11-postgis.control --force:

root@maps1009:~# ls -ls /usr/share/postgresql/11/extension/postgis.control
0 lrwxrwxrwx 1 root root 47 Jun  1 10:48 /usr/share/postgresql/11/extension/postgis.control -> /etc/alternatives/postgresql-11-postgis.control

Once this was done we were able to successfully run SELECT postgis_extensions_upgrade();. We don't anticipate hitting these issues in future with fresh hosts that aren't upgrading (maps2009 will be installing the new stack very soon). Thanks again Moritz!