Postgres docker image 15.3 is rebuilt and does not work for us anymore.

[mlromramse]

Hi!

We have been using postgres docker images for the past years now and a peculiar this happen recently.

After bumping version of the docker base image to 15.3 on the 29:th of November, upon we add postgis and friends it has been working perfectly for some months.
When we recently rebuilt our image, still based on a freshly pulled 15.3 we got some strange behavior on complex Postgis commands like st_intersect(geom, geom).
Digging into the image I found that the digest on postgres 15.3 had changed from sha256:ad126c2b7482... to sha256:8775adb39f0d downloaded today.

I was under the impression that a built docker version stays the same over time. Without that understanding you will find yourself on a very loose ground indeed.

We have tried other versions but none with higher version numbers work together with postgis for us. However, downgrading to 15.2 works although we haven't dared to pull it recently.

What I really want to know is if this is intended and let you know is that your docker images has changed after they where introduced, possibly at numerous occasions in case you do not know this.

Looking at the tags list today a lot of images was rebuilt 9 days ago by [doijanky] among others: 16, 16.2, 15.6, 15, 14.11, 14, 13.14, 13, 12.18, 12 but not 16.1, 15.5, 14.10, 13.12. It is hard to understand why some but not all are rebuilt if it is due to a change in the underlying OS.

Furthermore if you have a solution to our problem with postgis it would be much appreciated because at current state we need to find another solution to this for us to keep maintaining our images. Possibly using the postgis docker images loosing the tighter upgrade on the base os.

We install the POSTGIS that is available in the underlying distribution and are running on RedHat Enterprise Linux 9.3 with linux kernel 5.14.0-362.18.1.el9_3.x86_64

[ImreSamu]

We have tried other versions but none with higher version numbers work together with postgis for us.

As I know :
Currently, there are 2 fresh Debian-based PostgreSQL 15 versions available,
which include both the security patches and the operating system patches:

• postgres:15.6-bullseye
• postgres:15.6-bookworm

And it makes sense to install the latest PostGIS 3.4.2 with these.

I would recommend upgrading to pg 15.6, as it is likely to solve the PostGIS problem as well.
see the Release Notes :

• https://www.postgresql.org/docs/release/15.4/
• https://www.postgresql.org/docs/release/15.5/
• https://www.postgresql.org/docs/release/15.6/

Currently, in the https://github.com/postgis/docker-postgis repository,
a maintained x86-64 PostGIS image based on postgres:15-bullseye is still available
( postgis/postgis:15-3.4 )

DockerHub image	Dockerfile	OS	Postgres	PostGIS
postgis/postgis:12-3.4	Dockerfile	debian:bullseye	12	3.4.2
postgis/postgis:13-3.4	Dockerfile	debian:bullseye	13	3.4.2
postgis/postgis:14-3.4	Dockerfile	debian:bullseye	14	3.4.2
postgis/postgis:15-3.4	Dockerfile	debian:bullseye	15	3.4.2
postgis/postgis:16-3.4	Dockerfile	debian:bullseye	16	3.4.2

Support for bookworm is expected soon ( currently under testing ).

postgis/postgis:15-3.4 ( debian:bullseye )

$ docker pull postgis/postgis:15-3.4
15-3.4: Pulling from postgis/postgis
Digest: sha256:cee62c46cec1c2afacd42541e6e1be2203289fd20ab714718ac2b2a02ee27b01
Status: Image is up to date for postgis/postgis:15-3.4
docker.io/postgis/postgis:15-3.4

$ docker run --name some-postgis -e POSTGRES_PASSWORD=mysecretpassword -d postgis/postgis:15-3.4
98533cf5e73458b452f97aa14cfa87eace76fa1b328a1cf702d194be03f456c3

$ docker exec -ti some-postgis psql -U postgres
psql (15.6 (Debian 15.6-1.pgdg110+2))
Type "help" for help.

postgres=#     
    CREATE EXTENSION IF NOT EXISTS postgis;
    CREATE EXTENSION IF NOT EXISTS postgis_raster;
    CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;
    CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; --needed for postgis_tiger_geocoder;
    CREATE EXTENSION IF NOT EXISTS address_standardizer;
    CREATE EXTENSION IF NOT EXISTS address_standardizer_data_us;
    CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
    CREATE EXTENSION IF NOT EXISTS postgis_topology;
    SELECT version();
    SELECT PostGIS_Full_Version();
    \dx
NOTICE:  extension "postgis" already exists, skipping
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
NOTICE:  extension "fuzzystrmatch" already exists, skipping
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
NOTICE:  extension "postgis_tiger_geocoder" already exists, skipping
CREATE EXTENSION
NOTICE:  extension "postgis_topology" already exists, skipping
CREATE EXTENSION
                                                           version                                                           
------------------------------------------------------------------------------------------
 PostgreSQL 15.6 (Debian 15.6-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

                    postgis_full_version                                                                                                                                                                                        
-------------------------------------------------------------------------------------------
 POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" GEOS="3.9.0-CAPI-1.16.2"  SFCGAL="1.3.8" 
 PROJ="7.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj DATABASE_PATH=/usr/share/proj/proj.db" 
 GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
(1 row)


                                                                       List of installed extensions
             Name             | Version |   Schema   |                                                     Description                                                     
------------------------------+---------+------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2   | public     | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2   | public     | Address Standardizer US dataset example
 fuzzystrmatch                | 1.1     | public     | determine similarities and distance between strings
 plpgsql                      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                      | 3.4.2   | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster               | 3.4.2   | public     | PostGIS raster types and functions
 postgis_sfcgal               | 3.4.2   | public     | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology             | 3.4.2   | topology   | PostGIS topology spatial types and functions
(9 rows)

The PostGIS images are regenerated weekly to include the latest changes.
However, if you want to lock the image, you can do so with the sha256 digest:
docker pull postgis/postgis:15-3.4@sha256:cee62c46cec1c2afacd42541e6e1be2203289fd20ab714718ac2b2a02ee27b01

If you have more questions about postgis/postgis:15-3.4 , feel free to ask at https://github.com/postgis/docker-postgis .

[yosifkit]

The postgres:15.3 image hasn't been modified on Docker Hub since ~28 July, 2023 (https://hub.docker.com/_/postgres/tags?page=1&name=15.3). It was dropped from our supported list in 1a73ab6 when 15.4 was released. Every supported image tag in Docker Official images on Docker Hub can be rebuilt. Only the latest of each active major version is supported.

Tags like 15 will move to a new image when, for example, the Dockerfile is updated from 15.5 to 15.6 or tags like 15.6 will move to a new image when the deb package version moves from 15.6-1.pgdg110+1 to 15.6-1.pgdg110+2. We also rebuild the base Debian images a least every month and then rebuild all Docker Official Images FROM them; this is to incorporate things like OS package updates.

If you require a truly unchanging tag, then pulling by digest is the solution.

[mlromramse]

OK, I understand the concept of pulling from digest but how to handle the fact that it after your update contains breaking changes for use with POSTGIS. Please note that POSTGIS can be installed but it doesn't work on complex intersects.

[mlromramse]

I'm sorry that I wrote the last comment on the run and completely forgot to be polite. I do very much appreciate your efforts in answering my question and give me inputs and solutions. Thank you for that and thank you for bringing PostgreSQL to us in a container as well. You do a stellar work!

[ImreSamu]

@mlromramse :

contains breaking changes for use with POSTGIS.

Without knowledge of your specific Dockerfile, it's difficult to offer assistance.
PostGIS can be installed from source or from a package.

Since with version postgres:15.3, the apt-get upgrade would update many Debian packages, including PostgreSQL to version 15.6, which is not optimal, it is advisable to build on the latest Docker image postgres:15, where an apt-get upgrade is not necessary.

-- 15.3 is rebuilt and does not work for us anymore
However, downgrading to 15.2 works although we haven't dared to pull it recently.

It's possible that the automatic Debian version change caused the problem.
Now

• postgres:15.3=="Debian 12 (bookworm)"
• postgres:15.2=="Debian 11 (bullseye)"

You have to check on which Debian version the old, functioning image is based. ( Probably it was bullseye )

Now:

$ docker run -it --rm postgres:15.2 bash -c "cat etc/os-release | grep VERSION"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye

$ docker run -it --rm postgres:15.3 bash -c "cat etc/os-release | grep VERSION"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm

$ docker run -it --rm postgres:15-bullseye bash -c "cat etc/os-release | grep VERSION"
VERSION_ID="11"
VERSION="11 (bullseye)"
VERSION_CODENAME=bullseye

$ docker run -it --rm postgres:15 bash -c "cat etc/os-release | grep VERSION"
VERSION_ID="12"
VERSION="12 (bookworm)"
VERSION_CODENAME=bookworm

It's always a wise practice to lock the main PostgreSQL version (15) together with the Debian version in the Docker tag.

If the Debian version change is the issue, you have two options,

• Stay with the older bullseye version and modify the Dockerfile to use FROM postgres:15-bullseye.
• Upgrade to FROM postgres:15-bookworm , but in this case, you'll need to address complex COLLATION / Reindexing issues, which could also affect PostGIS results.
  ( Docker image for version 15.3 throws collation warnings #1099 )

After the change, it's also advisable to perform a full reindex!

• related to Postgis: https://www.postgresql.org/docs/release/15.5/
  • "Fix misbehavior during recursive page split in GiST index build " "It may be advisable to reindex all GiST indexes after installing this update."

I believe the upgrade ( FROM postgres:15-bullseye ) + REINDEX should resolve all your issues,
but if not, then it's advisable to seek help through the PostGIS support channels.