search

postgis / docker-postgis

Docker image for PostGIS
https://hub.docker.com/r/postgis/postgis/
MIT License
1.37k stars 462 forks source link

Error in /docker-entrypoint-initdb.d/10_postgis.sh on tags 13-master and 14-master #264

Closed ivanlonel closed 2 years ago

ivanlonel commented 2 years ago

The master builds are currently unable to load postgis 3.2.0dev in the database.

Apparently, CREATE EXTENSION fails in finding the new function gserialized_gist_sortsupport_2d in postgis-3.so.

This is the end of the log of my postgis container's first run:

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2021-11-04 01:19:41.709 -03 [49] LOG:  starting PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2021-11-04 01:19:41.724 -03 [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-04 01:19:41.764 -03 [50] LOG:  database system was shut down at 2021-11-04 01:19:37 -03
2021-11-04 01:19:41.779 -03 [49] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/10_postgis.sh
CREATE DATABASE
Loading PostGIS extensions into template_postgis
2021-11-04 01:19:44.367 -03 [63] ERROR:  could not find function "gserialized_gist_sortsupport_2d" in file "/usr/lib/postgresql/14/lib/postgis-3.so"
2021-11-04 01:19:44.367 -03 [63] STATEMENT:  CREATE EXTENSION IF NOT EXISTS postgis;

PostgreSQL Database directory appears to contain a database; Skipping initialization

Inspecting pg_available_extensions, this is what I get:

root@349e24a90eb6:/# psql -U postgres -c "SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%';"
           name           | default_version | installed_version |                          comment                           
--------------------------+-----------------+-------------------+------------------------------------------------------------
 postgis_raster-3         | 3.1.4           |                   | PostGIS raster types and functions
 postgis_tiger_geocoder-3 | 3.1.4           |                   | PostGIS tiger geocoder and reverse geocoder
 postgis_sfcgal-3         | 3.1.4           |                   | PostGIS SFCGAL functions
 postgis_topology-3       | 3.1.4           |                   | PostGIS topology spatial types and functions
 postgis-3                | 3.1.4           |                   | PostGIS geometry and geography spatial types and functions
 postgis_tiger_geocoder   | 3.2.0dev        |                   | PostGIS tiger geocoder and reverse geocoder
 postgis_raster           | 3.2.0dev        |                   | PostGIS raster types and functions
 postgis                  | 3.2.0dev        |                   | PostGIS geometry and geography spatial types and functions
 postgis_topology         | 3.2.0dev        |                   | PostGIS topology spatial types and functions
 postgis_sfcgal           | 3.2.0dev        |                   | PostGIS SFCGAL functions
(10 rows)

Perhaps Postgres is looking for a 3.2 function in a 3.1 shared object?

ImreSamu commented 2 years ago

The master builds are currently unable to load postgis 3.2.0dev in the database.

strange;

Can you replicate with:

docker pull postgis/postgis:14-master
docker images | grep 14-master
docker run -e POSTGRES_PASSWORD=pw postgis/postgis:14-master

My log:

$ docker pull  postgis/postgis:14-master

14-master: Pulling from postgis/postgis
Digest: sha256:98dc144f45ebc36429a5f437620b53ce1cbaf82a3c4763a159ca5f44446f46a0
Status: Image is up to date for postgis/postgis:14-master
docker.io/postgis/postgis:14-master

$ docker images | grep 14-master
postgis/postgis                         14-master       629a5ee19134   2 days ago      887MB

$ docker run -e POSTGRES_PASSWORD=pw postgis/postgis:14-master

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2021-11-04 05:04:35.964 UTC [48] LOG:  starting PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2021-11-04 05:04:35.965 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-04 05:04:35.970 UTC [49] LOG:  database system was shut down at 2021-11-04 05:04:35 UTC
2021-11-04 05:04:35.975 UTC [48] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/10_postgis.sh
CREATE DATABASE
Loading PostGIS extensions into template_postgis
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
Loading PostGIS extensions into postgres
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

2021-11-04 05:04:38.737 UTC [48] LOG:  received fast shutdown request
waiting for server to shut down....2021-11-04 05:04:38.737 UTC [48] LOG:  aborting any active transactions
2021-11-04 05:04:38.739 UTC [48] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2021-11-04 05:04:38.742 UTC [50] LOG:  shutting down
2021-11-04 05:04:38.819 UTC [48] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2021-11-04 05:04:38.871 UTC [1] LOG:  starting PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2021-11-04 05:04:38.871 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-11-04 05:04:38.871 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-11-04 05:04:38.873 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-04 05:04:38.876 UTC [67] LOG:  database system was shut down at 2021-11-04 05:04:38 UTC
2021-11-04 05:04:38.881 UTC [1] LOG:  database system is ready to accept connections

And I have different output 

$ docker run --name pgis14m -e POSTGRES_PASSWORD=pwq -d postgis/postgis:14-master
e5957b51a0603fc68df9742626f4defac226e1ee2b736d6125e7da0c2ea82c43

$ docker exec -it pgis14m psql -U postgres -c "SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%';"
          name          | default_version | installed_version |                          comment                           
------------------------+-----------------+-------------------+------------------------------------------------------------
 postgis_tiger_geocoder | 3.2.0dev        | 3.2.0dev          | PostGIS tiger geocoder and reverse geocoder
 postgis_sfcgal         | 3.2.0dev        |                   | PostGIS SFCGAL functions
 postgis_topology       | 3.2.0dev        | 3.2.0dev          | PostGIS topology spatial types and functions
 postgis_raster         | 3.2.0dev        |                   | PostGIS raster types and functions
 postgis                | 3.2.0dev        | 3.2.0dev          | PostGIS geometry and geography spatial types and functions
(5 rows)
ivanlonel commented 2 years ago

Well, this is embarassing.

Turns out the container I was running was from an image built with this Dockerfile:

FROM postgis/postgis:14-master

RUN apt-get update && \
    apt-get install -y --no-install-recommends postgresql-$PG_MAJOR-pgrouting && \
    apt-get purge -y --auto-remove && \
    rm -rf /var/lib/apt/lists/*

postgresql-$PG_MAJOR-postgis-3_3.1.4 is currently a dependency of postgresql-$PG_MAJOR-pgrouting on Bullseye, at least for $PG_MAJOR in {13, 14}, so that's where my postgis-3.so was coming from.

Thank you so much for your time and attention, and sorry for demanding them on this misguided issue to begin with.

ImreSamu commented 2 years ago

postgresql-$PG_MAJOR-postgis-3_3.1.4 is currently a dependency of postgresql-$PG_MAJOR-pgrouting on Bullseye,

Thanks for the explanation!

This means that particular care should be taken when expanding the "-master" images!

ImreSamu commented 2 years ago

@ivanlonel

side note for your interesting project: https://github.com/ivanlonel/postgis-with-extensions/blob/master/.github/workflows/docker-publish.yml

the

no longer supported ( no security fix ! ) the -3.0 images were updated 10 months ago!

$ docker pull postgis/postgis:11-3.0
11-3.0: Pulling from postgis/postgis
Digest: sha256:e589980b522585186fbe1b90289c4ce548d5fe4ae789e640f13a6ad79738627c
Status: Image is up to date for postgis/postgis:11-3.0
docker.io/postgis/postgis:11-3.0

$ docker images  postgis/postgis:11-3.0
REPOSITORY        TAG       IMAGE ID       CREATED         SIZE
postgis/postgis   11-3.0    1dd39e8e9c42   10 months ago   427MB
ivanlonel commented 2 years ago

@ImreSamu Thanks a lot for going out of your way to point that out. I've updated my workflow to stop extending these tags.