postgis:12-3.0 fails when Loading PostGIS extensions into template_postgis

[brunohadlich]

I realized that while postgis:12-2.5 initialization runs flawlessly, the same does not apply to postgis:12-3.0. Although the issue seems to reside in initdb-postgis.sh, both files in 2.5 and 3.0 are identical. Both images were downloaded in 9/23/2020.

This is my service on docker-compose.yml:

  db:
    deploy:
      resources:
        limits:
          memory: "128M"
    image: postgis/postgis:12-3.0
    restart: always
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./postgresql/data/:/var/lib/postgresql/data/
    networks:
      - backend

And here is the log from first initialization:

db_1                | The files belonging to this database system will be owned by user "postgres".
db_1                | This user must also own the server process.
db_1                | 
db_1                | The database cluster will be initialized with locale "en_US.utf8".
db_1                | The default database encoding has accordingly been set to "UTF8".
db_1                | The default text search configuration will be set to "english".
db_1                | 
db_1                | Data page checksums are disabled.
db_1                | 
db_1                | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1                | creating subdirectories ... ok
db_1                | selecting dynamic shared memory implementation ... posix
db_1                | selecting default max_connections ... 100
db_1                | selecting default shared_buffers ... 128MB
db_1                | selecting default time zone ... Etc/UTC
db_1                | creating configuration files ... ok
db_1                | running bootstrap script ... ok
db_1                | performing post-bootstrap initialization ... ok
db_1                | syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
db_1                | You can change this by editing pg_hba.conf or using the option -A, or
db_1                | --auth-local and --auth-host, the next time you run initdb.
db_1                | ok
db_1                | 
db_1                | 
db_1                | Success. You can now start the database server using:
db_1                | 
db_1                |     pg_ctl -D /var/lib/postgresql/data -l logfile start
db_1                | 
db_1                | waiting for server to start....2020-09-23 18:40:58.988 UTC [47] LOG:  starting PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1                | 2020-09-23 18:40:58.997 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1                | 2020-09-23 18:40:59.039 UTC [48] LOG:  database system was shut down at 2020-09-23 18:40:57 UTC
db_1                | 2020-09-23 18:40:59.049 UTC [47] LOG:  database system is ready to accept connections
db_1                |  done
db_1                | server started
db_1                | 
db_1                | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/10_postgis.sh
db_1                | CREATE DATABASE
db_1                | Loading PostGIS extensions into template_postgis
db_1                | server closed the connection unexpectedly
db_1                |   This probably means the server terminated abnormally
db_1                |   before or while processing the request.
db_1                | connection to server was lost
db_1                | 2020-09-23 18:41:04.231 UTC [47] LOG:  server process (PID 72) was terminated by signal 9: Killed
db_1                | 2020-09-23 18:41:04.231 UTC [47] DETAIL:  Failed process was running: CREATE EXTENSION IF NOT EXISTS postgis;
db_1                | 2020-09-23 18:41:04.239 UTC [47] LOG:  terminating any other active server processes
db_1                | 2020-09-23 18:41:04.269 UTC [52] WARNING:  terminating connection because of crash of another server process
db_1                | 2020-09-23 18:41:04.269 UTC [52] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
db_1                | 2020-09-23 18:41:04.269 UTC [52] HINT:  In a moment you should be able to reconnect to the database and repeat your command.

When I only change the image to postgis/postgis:12-2.5 it works fine like below:

db_1                | The files belonging to this database system will be owned by user "postgres".
db_1                | This user must also own the server process.
db_1                | 
db_1                | The database cluster will be initialized with locale "en_US.utf8".
db_1                | The default database encoding has accordingly been set to "UTF8".
db_1                | The default text search configuration will be set to "english".
db_1                | 
db_1                | Data page checksums are disabled.
db_1                | 
db_1                | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1                | creating subdirectories ... ok
db_1                | selecting dynamic shared memory implementation ... posix
db_1                | selecting default max_connections ... 100
db_1                | selecting default shared_buffers ... 128MB
db_1                | selecting default time zone ... Etc/UTC
db_1                | creating configuration files ... ok
db_1                | running bootstrap script ... ok
db_1                | performing post-bootstrap initialization ... ok
db_1                | syncing data to disk ... ok
db_1                | 
db_1                | 
db_1                | Success. You can now start the database server using:
db_1                | 
db_1                |     pg_ctl -D /var/lib/postgresql/data -l logfile start
db_1                | 
db_1                | initdb: warning: enabling "trust" authentication for local connections
db_1                | You can change this by editing pg_hba.conf or using the option -A, or
db_1                | --auth-local and --auth-host, the next time you run initdb.
db_1                | waiting for server to start....2020-09-23 18:38:48.580 UTC [47] LOG:  starting PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1                | 2020-09-23 18:38:48.590 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1                | 2020-09-23 18:38:48.644 UTC [48] LOG:  database system was shut down at 2020-09-23 18:38:46 UTC
db_1                | 2020-09-23 18:38:48.656 UTC [47] LOG:  database system is ready to accept connections
db_1                |  done
db_1                | server started
db_1                | 
db_1                | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/10_postgis.sh
db_1                | CREATE DATABASE
db_1                | Loading PostGIS extensions into template_postgis
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | Loading PostGIS extensions into postgres
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | CREATE EXTENSION
db_1                | 
db_1                | waiting for server to shut down....2020-09-23 18:39:58.498 UTC [47] LOG:  received fast shutdown request
db_1                | 2020-09-23 18:39:58.510 UTC [47] LOG:  aborting any active transactions
db_1                | 2020-09-23 18:39:58.559 UTC [47] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
db_1                | 2020-09-23 18:39:58.655 UTC [49] LOG:  shutting down
db_1                | ..2020-09-23 18:40:01.136 UTC [47] LOG:  database system is shut down
db_1                |  done
db_1                | server stopped
db_1                | 
db_1                | PostgreSQL init process complete; ready for start up.
db_1                | 
db_1                | 2020-09-23 18:40:01.277 UTC [1] LOG:  starting PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1                | 2020-09-23 18:40:01.277 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1                | 2020-09-23 18:40:01.277 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1                | 2020-09-23 18:40:01.298 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1                | 2020-09-23 18:40:01.351 UTC [85] LOG:  database system was shut down at 2020-09-23 18:40:01 UTC
db_1                | 2020-09-23 18:40:01.370 UTC [1] LOG:  database system is ready to accept connections

[phillipross]

It may be the case that auto-updating from v2.5 to v3.0 is problematic. Am I correct in interpreting your compose file as using bind mounts to a local filesystem? If you initial first with the v2.5 image, the initialization will create the database in the local filesystem, and when you run with the v3.0 image afterward without clearing out the database that was created during v2.5 initialization, the v3.0 container will attempt to use the existing database.

[brunohadlich]

I am binding container foldder /var/lib/postgresql/data/ to my computer folder ./postgresql/data/, but the following steps were executed before starting the container:

1. Destroyed the previous container with docker-compose down && docker system prune
2. Removed all content of the binded directory with sudo rm -rf ./postgresql/data/
3. Recreated the folder data with sudo mkdir ./postgres/data

This clean up was executed before starting both, 2.5 and 3.0.

[phillipross]

For testing purposes, can you remove the bind mounts and see if initialization works without specifying volumes?

You might also test to see if you can simply start a container using docker directly. Try these:

docker container run --rm -it --name two -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres postgis/postgis:12-2.5
docker container run --rm -it --name three -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres postgis/postgis:12-3.0

[ImreSamu]

@brunohadlich : Hello, could you give us more details about about your environment?
{ host os | docker | docker-compose } version?

my personal experience

• few weeks ago I have checked the docker-compose=v1.27.0 ; but I have to revert to 1.26.2 because I have found some strange bugs .. so be carefull with the latest docker-compose versions ...

I am using similar test case like you ; based on postgis:12-3.0 image + docker-compose version: '2' config + local volume ..without any problems ( except the docker-compose revert )

my dev environment on ubuntu 20.04

user@host:~$ docker-compose -v
docker-compose version 1.26.2, build eefe0d31

user@host:~$ docker version
Client: Docker Engine - Community
 Version:           19.03.13
 API version:       1.40
 Go version:        go1.13.15
 Git commit:        4484c46d9d
 Built:             Wed Sep 16 17:02:52 2020
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          19.03.13
  API version:      1.40 (minimum version 1.12)
  Go version:       go1.13.15
  Git commit:       4484c46d9d
  Built:            Wed Sep 16 17:01:20 2020
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.3.7
  GitCommit:        8fba4e9a7d01810a393d5d25a3621dc101981175
 runc:
  Version:          1.0.0-rc10
  GitCommit:        dc9208a3303feef5b3839f4323d9beb36df0a9dd
 docker-init:
  Version:          0.18.0
  GitCommit:        fec3683

[brunohadlich]

@phillipross removing networks and volumes did not work but the command docker container run --rm -it --name three -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres postgis/postgis:12-3.0 worked. It is really strange that running without docker-compose has worked, maybe what @ImreSamu said about docker-compose version is right as I am using docker-compose 1.27.2.

@ImreSamu I am running Ubuntu 20.04.1 LTS over Microsoft WSL 2, here is docker and docker-compose versions:

bruno@DESKTOP-1HGLI35:~/git/raw-backend$ docker-compose -v
docker-compose version 1.27.2, build 18f557f9
bruno@DESKTOP-1HGLI35:~/git/raw-backend$ docker version
Client: Docker Engine - Community
 Azure integration  0.1.15
 Version:           19.03.12
 API version:       1.40
 Go version:        go1.13.10
 Git commit:        48a66213fe
 Built:             Mon Jun 22 15:45:36 2020
 OS/Arch:           linux/amd64
 Experimental:      false

Server: Docker Engine - Community
 Engine:
  Version:          19.03.12
  API version:      1.40 (minimum version 1.12)
  Go version:       go1.13.10
  Git commit:       48a66213fe
  Built:            Mon Jun 22 15:49:27 2020
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          v1.2.13
  GitCommit:        7ad184331fa3e55e52b890ea95e65ba581ae3429
 runc:
  Version:          1.0.0-rc10
  GitCommit:        dc9208a3303feef5b3839f4323d9beb36df0a9dd
 docker-init:
  Version:          0.18.0
  GitCommit:        fec3683

[brunohadlich]

I found out my issue, before using postgis I was using postgres, my service memory was limited to 128MB, apparently this is not enough for postgis, increasing memory limit to 256MB did the initialization to work. So after all it was not a problem with versions but a settings constraint.

[phillipross]

Glad to see you got it worked out, interesting to see the minimum memory required is 256MB, and also glad to see it wasn't docker-compose's fault 😉

[brunohadlich]

The minimum memory requirement of 256MBonly applies to postgis/postgis:12-3.0, with postgis/postgis:12-2.5 it is possible to run with 128MB, that is why only postgis/postgis:12-3.0 was failing to me.