Init script changes don't persist

[ummarbhutta]

I am working to create a customized postgresql image with postgis plugin. Following are the contents of docker file

# Setting base environment with Postgresql version 11
FROM postgres:11

ENV DB_NAME JoesBlooms

RUN apt update
RUN apt install -y wget curl gnupg2 software-properties-common apt-transport-https

RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |  apt-key add -
RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | tee  /etc/apt/sources.list.d/pgdg.list

RUN apt update
RUN apt install -y postgis postgresql-11-postgis-2.5

COPY ./enable_gis.sql /docker-entrypoint-initdb.d

Contents of enable_gis.sql are as follows

CREATE EXTENSION IF NOT EXISTS postgis;

place both files in same folder and build using following command

sudo docker build -t postgis:11-2.5 .

Then create following docker-compose.yml file

version: '3.9'
services:
    custom-postgresql:
        image: "postgis:11-2.5"
        volumes:
        - /test/postgresql:/var/lib/postgresql/data
        environment:
        - POSTGRES_USER=postgres
        - POSTGRES_PASSWORD=admin
        - POSTGRES_DB=testdb
        - PGDATA=/var/lib/postgresql/data/pgdata
        ports:
        - "5432:5432"
        restart: unless-stopped

Now when you spin-up container using docker compose, following output will be shown

sudo docker compose up

myfolder-custom-postgresql-1   | The files belonging to this database system will be owned by user "postgres".
myfolder-custom-postgresql-1   | This user must also own the server process.
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | The database cluster will be initialized with locale "en_US.utf8".
myfolder-custom-postgresql-1   | The default database encoding has accordingly been set to "UTF8".
myfolder-custom-postgresql-1   | The default text search configuration will be set to "english".
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | Data page checksums are disabled.
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | fixing permissions on existing directory /var/lib/postgresql/data/pgdata ... ok
myfolder-custom-postgresql-1   | creating subdirectories ... ok
myfolder-custom-postgresql-1   | selecting default max_connections ... 100
myfolder-custom-postgresql-1   | selecting default shared_buffers ... 128MB
myfolder-custom-postgresql-1   | selecting default timezone ... Etc/UTC
myfolder-custom-postgresql-1   | selecting dynamic shared memory implementation ... posix
myfolder-custom-postgresql-1   | creating configuration files ... ok
myfolder-custom-postgresql-1   | running bootstrap script ... ok
myfolder-custom-postgresql-1   | performing post-bootstrap initialization ... ok
myfolder-custom-postgresql-1   | syncing data to disk ... ok
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | Success. You can now start the database server using:
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   |     pg_ctl -D /var/lib/postgresql/data/pgdata -l logfile start
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | WARNING: enabling "trust" authentication for local connections
myfolder-custom-postgresql-1   | You can change this by editing pg_hba.conf or using the option -A, or
myfolder-custom-postgresql-1   | --auth-local and --auth-host, the next time you run initdb.
myfolder-custom-postgresql-1   | waiting for server to start....2022-10-18 16:54:07.887 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
myfolder-custom-postgresql-1   | 2022-10-18 16:54:07.906 UTC [50] LOG:  database system was shut down at 2022-10-18 16:54:07 UTC
myfolder-custom-postgresql-1   | 2022-10-18 16:54:07.912 UTC [49] LOG:  database system is ready to accept connections
myfolder-custom-postgresql-1   |  done
myfolder-custom-postgresql-1   | server started
myfolder-custom-postgresql-1   | CREATE DATABASE
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/enable_gis.sql
myfolder-custom-postgresql-1   | CREATE EXTENSION
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | waiting for server to shut down...2022-10-18 16:54:08.859 UTC [49] LOG:  received fast shutdown request
myfolder-custom-postgresql-1   | .2022-10-18 16:54:08.863 UTC [49] LOG:  aborting any active transactions
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.865 UTC [49] LOG:  background worker "logical replication launcher" (PID 56) exited with exit code 1
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.870 UTC [51] LOG:  shutting down
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.940 UTC [49] LOG:  database system is shut down
myfolder-custom-postgresql-1   |  done
myfolder-custom-postgresql-1   | server stopped
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | PostgreSQL init process complete; ready for start up.
myfolder-custom-postgresql-1   |
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.982 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.982 UTC [1] LOG:  listening on IPv6 address "::", port 5432
myfolder-custom-postgresql-1   | 2022-10-18 16:54:08.989 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
myfolder-custom-postgresql-1   | 2022-10-18 16:54:09.004 UTC [86] LOG:  database system was shut down at 2022-10-18 16:54:08 UTC
myfolder-custom-postgresql-1   | 2022-10-18 16:54:09.041 UTC [1] LOG:  database system is ready to accept connections

Clearly showing that enable_gis.sql is called and CREATE EXTENSION as response, but when I execute SQL command SELECT PostGIS_version(); on this server, it return error the function is unknown.

Now if I execute the command CREATE EXTENSION IF NOT EXISTS postgis; then this plugin will be enabled and will start working fine.

My Question is why startup script changes are not persistent, they seem to executed successfully but somehow there changes are not saved.

For easy debugging the docker container is already pushed at ssilhr/postgis:11-2.5
I need PostgreSQL 11 with PostGIS 2.5, so version upgrade is not an option

[wglambert]

Yeah not sure why the init script isn't actually doing the CREATE EXTENSION. But it will persist through restart after manually doing CREATE EXTENSION IF NOT EXISTS postgis;

Also tried swapping the COPY line with RUN echo 'CREATE EXTENSION IF NOT EXISTS "postgis";' > /docker-entrypoint-initdb.d/postgis.sql and same result #826 (comment)

[SuperMata]

I have the same issue. Create a schema in the init file and it's not created in the container when it runs. Only Create Database works.

[yosifkit]

I'm not sure what the difference between your Dockerfile and the PostGIS Dockerfiles is, because they are working over there.

https://github.com/postgis/docker-postgis/tree/4b53c954472c548c6d63e833404952b6bfb6942a/11-3.3

My guess is that it might be related to postgres background threads that don't quite finish with just a single CREATE EXTENSION before the server restart, but I have no proof and I am unable to reproduce locally:

FROM postgres:11-bullseye
#postgres:11 no longer works as the Debian Stretch PostgreSQL repos are no longer available

RUN set -e; \
	apt-get update; \
	apt-get install -y postgis postgresql-11-postgis-3; \
	rm -rf /var/lib/apt/lists/*

RUN set -e; echo 'CREATE EXTENSION IF NOT EXISTS "postgis";' | tee /docker-entrypoint-initdb.d/postgis.sql 

$ docker build .
...
Successfully built 5f98ea5a4547
$ docker run -it --rm --name pg -e POSTGRES_PASSWORD=admin 5f98ea5a4547
$ docker exec -it pg psql -U postgres
psql (15.1 (Debian 15.1-1.pgdg110+1), server 11.18 (Debian 11.18-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT PostGIS_version();
            postgis_version            
---------------------------------------
 3.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

(They have some ~5 month old PostgeSQL 11 with PostGIS 2.5 images that might work for you: https://hub.docker.com/r/postgis/postgis/tags?page=1&name=11-2.5)