How to create stable images (Version '<version>' for 'postgresql-<version>-postgis-<version>' was not found)

[petetnt]

Re-opening this issue because it's similar to the following issue #346 but I'd like to find a more long term solution than just updating the version in the variable.

I am not really familiar how the apt release process works at all, but basically currently every(ish?) time a new version is released, the version we are using is removed (eg. see https://ftp.debian.org/debian/pool/main/p/postgis/), which means that the version variable POSTGIS_VERSION needs to be updated.

For example, the version pointed at now, 3.4.3+dfsg-2.pgdg110+1 results in the following error:

0 2.931 Provides:
#0 2.931 3.5.0+dfsg-1.pgdg120+1 - postgresql-15-postgis (= )
#0 2.931 3.3.2+dfsg-1+b1 - postgresql-15-postgis (= )
#0 2.931 Reverse Provides:
#0 2.948 Reading package lists...
#0 3.284 Building dependency tree...
#0 3.369 Reading state information...
#0 3.374 Package postgresql-15-postgis-3 is not available, but is referred to by another package.
#0 3.374 This may mean that the package is missing, has been obsoleted, or
#0 3.374 is only available from another source
#0 3.374
#0 3.381 E: Version '3.4.3+dfsg-2.pgdg110+1' for 'postgresql-15-postgis-3' was not found

I am looking for a way to make this somewhat stable, eg. I am okay with just 3.4.3 as long as it's available until I update it.

As far as I understand, @sebastic manages the releases on the Debian side if I understand https://tracker.debian.org/pkg/postgis correctly, so maybe they could throw a pointer or two what should be the proposed way forward.

[petetnt]

Fix for the actual issue is tracking in https://github.com/postgis/docker-postgis/pull/395/files

[sebastic]

As far as I understand, @sebastic manages the releases on the Debian side if I understand https://tracker.debian.org/pkg/postgis correctly, so maybe they could throw a pointer or two what should be the proposed way forward.

CC: @df7cb as he manages apt.postgresql.org.

As the package repos don't keep the old versions around, you can't pin specific versions in your CI. You'd need to run you own package repo which does keep old versions around if you need that. Or build the desired version from source.

On Debian regular systems you'd install the postgresql-postgis metapackage which depends on the postgresql specific version that provides the extension (e.g. postgresql-17-postgis-3). When doing a distribution upgrade it pulls in the new version for the postgresql included in that release.

[ImreSamu]

Postgis docker images are rebuilt weekly ( cron: '15 5 * * 1' ) , and our minimal test system prevents publication in case of any issues.
After building, we also run the official Docker tests, along with an additional "postgis-basics" test

/home/runner/official-images/test/run.sh -c /home/runner/official-images/test/config.sh -c test/postgis-config.sh postgis/postgis:15-3.5
testing postgis/postgis:15-3.5
	'utc' [1/6]...passed
	'no-hard-coded-passwords' [2/6]...passed
	'override-cmd' [3/6]...passed
	'postgres-basics' [4/6]...passed
	'postgres-initdb' [5/6]...passed
	'postgis-basics' [6/6]...passed

If everything is successful, the Docker postgis image is published.

We ( docker-postgres repo ) generally follow the style of the upstream Docker Postgres repository,
and the Debian version is also hard-coded there: ENV PG_VERSION 17.0-1.pgdg110+1.

Regarding changes to the postgresql-15-postgis-3 package,
I stay informed via the pgsql-pkg-debian mailing list,
and we try to update the Dockerfiles and images as quickly as possible.

• https://www.postgresql.org/message-id/E1t0NCG-000wPf-FN%40atalia.postgresql.org

---

In the next version ( test : https://github.com/ImreSamu/docker-postgis) , a "recent" version is already being prepared from the source code, along with many other new features, including automatic upstream version detection.

---

If you have any other suggestions, we are open to them.

[petetnt]

Thanks @sebastic and @ImreSamu for clarifications, in our use-case we are tied to a platform provider (eg. Google Cloud SQL, Postgres for Azure) and we want to run our CI on a version that resembles the platform as closely as possible. I guess best way then would be building it from the source or self-hosting the package... unless you have any other pointers :)

[ImreSamu]

@petetnt

in our use-case we are tied to a platform provider (eg. Google Cloud SQL, Postgres for Azure)
and we want to run our CI on a version that resembles the platform as closely as possible.

I have a similar issue with GCP, so I'm trying to design the new system in a way that allows for extensions with specialized versions.

For example, "14-l3.1.9gcp-bookworm, 14-l3.1.9gcp-postgis3.1.9-geos3.6.6-proj6.3.1-bookworm"
would have aimed to model the GCP version from a year ago.
and in the test system:

• https://registry.hub.docker.com/r/imresamu/postgis/tags?page=1&name=14-l3.1.9gcp-postgis3.1.9-geos3.6.6-proj6.3.1-bookworm (arm64, amd64)
• Dockerfile: https://github.com/ImreSamu/docker-postgis/blob/master/14-l3.1.9gcp/bookworm/Dockerfile
  • versions in the Dockerfile :

ARG PGIS1_POSTGIS_CHECKOUT=tags/3.1.9
ARG PGIS1_GEOS_CHECKOUT=tags/3.6.6
ARG PGIS1_GDAL_CHECKOUT=tags/v3.6.4
ARG PGIS1_PROJ_CHECKOUT=tags/6.3.1
ARG PGIS1_SFCGAL_CHECKOUT=tags/v1.5.1
ARG PGIS1_CGAL_CHECKOUT=tags/v5.6

[df7cb]

Simply don't pin the version and be happy.

[petetnt]

I'd happily not pin any versions anywhere, but as said we are often constricted by our platform providers and there's always a risk of doing something incompatible when doing so. :)

[df7cb]

well it's obviously more incompatible to do the pinning 🙊

[bostrom]

I agree with @petetnt.

Our platform provider currently uses version 3.3

select postgis_version();
            postgis_version
---------------------------------------
 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

and obviously we'd like to use the same version in development to make sure we're not doing anything that isn't supported in our online environment.

However, that version is no longer available, instead the only available version right now is 3.5.0+dfsg-1.pgdg120+1, which forces us to use a newer (potentially incompatible) version of PostGIS in development/CI than we're actually using in production.

I was hoping there would be some way to keep those images around so we can keep using the same version as our platform provider.

[df7cb]

There is https://apt-archive.postgresql.org/.

[captaincoordinates]

There is https://apt-archive.postgresql.org/.

I'm struggling to find a public GPG key for this package repository. Is there any guidance on how to use it beyond the landing page you linked?

[df7cb]

Same key as the main repo.

[petetnt]

Sorry to still bother you all with this, but today I tried to use apt-archive to get an older version.

My Dockerfile looks like this:

FROM postgres:15-bullseye
ENV POSTGIS_MAJOR 3
ENV POSTGIS_VERSION 3.5.0+dfsg-1.pgdg110+1

RUN apt-get update && apt-get install -y curl
RUN curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
RUN echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt-archive.postgresql.org/pub/repos/apt bullseye-pgdg-archive main" >> /etc/apt/sources.list
RUN echo "deb-src [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt-archive.postgresql.org/pub/repos/apt bullseye-pgdg-archive main" >> /etc/apt/sources.list

RUN apt-get update \
      && apt-cache showpkg postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR \
      && apt-get install -y --no-install-recommends \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR=$POSTGIS_VERSION \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR-scripts \
      && rm -rf /var/lib/apt/lists/*

RUN mkdir -p /docker-entrypoint-initdb.d
COPY ./initdb-postgis.sh /docker-entrypoint-initdb.d/10_postgis.sh

EXPOSE 5432

CMD ["postgres"]

After adding those apt finds and install the package. When trying to run postgis install, it fails however:

#!/bin/sh

set -e

# Perform all actions as $POSTGRES_USER
export PGUSER="$POSTGRES_USER"

# Create the 'template_postgis' template db
"${psql[@]}" <<- 'EOSQL'
CREATE DATABASE template_postgis IS_TEMPLATE true;
EOSQL

# Load PostGIS into both template_database and $POSTGRES_DB
for DB in template_postgis "$POSTGRES_DB"; do
	echo "Loading PostGIS extensions into $DB"
	"${psql[@]}" --dbname="$DB" <<-'EOSQL'
		CREATE EXTENSION IF NOT EXISTS postgis;
		CREATE EXTENSION IF NOT EXISTS postgis_topology;
		CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
		CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
EOSQL
done**

The error:

postgres-1  | Loading PostGIS extensions into template_postgis
postgres-1  | ERROR:  could not find function "geog_brin_inclusion_merge" in file "/usr/lib/postgresql/15/lib/postgis-3.so"
postgres-1  | 2025-01-27 12:13:46.174 UTC [62] ERROR:  could not find function "geog_brin_inclusion_merge" in file "/usr/lib/postgresql/15/lib/postgis-3.so"

I confirmed that usr/lib/postgresql/15/lib/postgis-3.so does exit on the container and the error message did not result into other occurances in Google search. Any ideas what I am doing wrong?