Prepopulating PostGIS with OSM data works with Docker Desktop, but not with Azure pipeline #379

afarber · 2024-03-26T08:49:48Z

afarber
Mar 26, 2024

Hello dear PostGIS developers and users,

when I build the Dockerfile below by running the command manually at a Windows notebook

    docker build -f Dockerfile --no-cache --tag postgis-berlin:16-3.4 \
        --build-arg DOWNLOAD_URLS="https://download.geofabrik.de/europe/germany/berlin-latest.osm.pbf" \
        --build-arg OSM_PASSWORD=osm_password \
        --build-arg POSTGIS_TAG=16-3.4 .

then it works as expected and the prefilled PostgreSQL database is there in the container launched in Docker Desktop:

But when I build the same Dockerfile by an Azure pipeline:

- task: AzureCLI@2
  displayName: Build and push PostGIS image
  inputs:
    azureSubscription: $(ArmConnection)
    scriptType: bash
    scriptLocation: inlineScript
    inlineScript: |
      image_tag='$(ContainerRegistry)/postgis-${{ parameters.OsmRegion }}:${{ parameters.PostGisTag }}'
      # delete old images to avoid the error "no space left on device"
      docker system prune --all --force
      docker build --file $(Build.SourcesDirectory)/src/Services/PostGIS/Dockerfile --no-cache --tag $image_tag --build-arg POSTGIS_TAG=${{parameters.PostGisTag}} --build-arg OSM_PASSWORD=$(OsmPassword) --build-arg DOWNLOAD_URLS="$(DownloadUrls)" $(Build.SourcesDirectory)/src/
      # building the Docker image takes longer time, so be sure to acr login just before pushing the image
      az acr login -n $(ContainerRegistry)
      docker push $image_tag

and then pull the built image from ACR and launch it at Kubernetes, then the database folder is empty:

I understand that it is probably not a recommended practice to launch and stop a service during the "docker build".

The reason I am not populating the database by running a script in /docker-entrypoint-initdb.d folder is that in production we use the 28 GB big europe-latest.osm.pbf (and not the small Berlin file) and thus launching a pod would take several hours.

Below is my Dockerfile, I have created it by looking at the https://github.com/postgis/docker-postgis/blob/master/Dockerfile.alpine.template

Thank you for any insights! I have also asked the same question at Stackoverflow.

ARG POSTGIS_TAG
FROM postgis/postgis:$POSTGIS_TAG
EXPOSE 5432

# Install ps, top, netstat, curl, wget, osmium, osm2pgsql
RUN apt-get update && \
    apt-get upgrade -y && \
    apt install -y procps net-tools curl wget osmium-tool osm2pgsql

WORKDIR /data
RUN chown -R postgres:postgres /data
USER postgres

# Download one or multiple OSM files
ARG DOWNLOAD_URLS
RUN wget --no-verbose $DOWNLOAD_URLS
# Print summaries of the downloaded OSM files
RUN for f in *.osm.pbf; do osmium fileinfo -e "$f"; done
# Merge one or multiple files into a new file map.osm.pbf
RUN osmium merge *.osm.pbf -o map.osm.pbf
# Remove all files except the map.osm.pbf
RUN find . -type f -name '*.osm.pbf' | grep -v 'map.osm.pbf' | xargs rm -f

ARG OSM_PASSWORD
ENV PGPASSWORD=$OSM_PASSWORD
ENV PGUSER=osm_user
ENV PGDATABASE=osm_database
ENV PGDATA=/var/lib/postgresql/data

# create PostgreSQL instance in the $PGDATA folder
# configure PostgreSQL as recommended by osm2pgsql doc
# start PostgreSQL and create database and user
# load data from the map.osm.pbf file into the database
# remove the map.osm.pbf file and stop PostgreSQL
# configure password based access for osm_user
RUN set -eux && \
    pg_ctl init && \
    echo "shared_buffers = 1GB"                >> $PGDATA/postgresql.conf && \
    echo "work_mem = 50MB"                     >> $PGDATA/postgresql.conf && \
    echo "maintenance_work_mem = 10GB"         >> $PGDATA/postgresql.conf && \
    echo "autovacuum_work_mem = 2GB"           >> $PGDATA/postgresql.conf && \
    echo "wal_level = minimal"                 >> $PGDATA/postgresql.conf && \
    echo "checkpoint_timeout = 60min"          >> $PGDATA/postgresql.conf && \
    echo "max_wal_size = 10GB"                 >> $PGDATA/postgresql.conf && \
    echo "checkpoint_completion_target = 0.9"  >> $PGDATA/postgresql.conf && \
    echo "max_wal_senders = 0"                 >> $PGDATA/postgresql.conf && \
    echo "random_page_cost = 1.0"              >> $PGDATA/postgresql.conf && \
    echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \
    pg_ctl start && \ 
    createuser --username=postgres $PGUSER && \
    createdb --username=postgres --encoding=UTF8 --owner=$PGUSER $PGDATABASE && \
    psql --username=postgres $PGDATABASE --command="ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';" && \
    psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS postgis;' && \
    psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS hstore;' && \
    osm2pgsql --username=$PGUSER --database=$PGDATABASE --create --cache=60000 --hstore --latlong /data/map.osm.pbf && \
    rm -f /data/map.osm.pbf && \
    pg_ctl stop && \
    echo '# TYPE DATABASE USER ADDRESS METHOD'                > $PGDATA/pg_hba.conf && \
    echo "local all postgres peer"                           >> $PGDATA/pg_hba.conf && \
    echo "local $PGDATABASE $PGUSER           scram-sha-256" >> $PGDATA/pg_hba.conf && \
    echo "host  $PGDATABASE $PGUSER 0.0.0.0/0 scram-sha-256" >> $PGDATA/pg_hba.conf

Answered by afarber

Mar 29, 2024

Thank you for your suggestions, they have helped me to find the solution for the empty /var/lib/postgresql/data folder, when building a postgis/postgis:16.3 based Docker image with prefilled OSM database and I would like to share it here with other users -

After changing just a single line my Dockerfile has built successfully:

ENV PGDATA=/postgis

The reason seems to be that the parent official postgres Dockerfile contains this conflicting line:

VOLUME /var/lib/postgresql/data

and thus the folder I was trying to use originally was cleared.

Below is my working Dockerfile (and yes I understand that the built image is too big, especially with Europe data and thus I have to improve it further …

View full answer

ImreSamu · 2024-03-26T17:07:36Z

ImreSamu
Mar 26, 2024
Maintainer

I'm not entirely sure I understand the issue...

However, I believe the root of the problem is unlikely to be related to PostGIS,
and I would definitely recommend reducing your test case to a minimal PostgreSQL image,
removing anything unnecessary, as it's currently too complex.

other suggestions:

Add more logging at the end of Dockerfile . For example, RUN ls -la /var/lib/postgresql/data
Build using docker build --pull --no-cache to ensure it's based on the latest image.
- https://stackoverflow.com/questions/58488535/whats-the-purpose-of-docker-build-pull
check the docker version is the latest ..

other comment:

azureSubscription: $(ArmConnection) : no arm postgis image yet .. only x86-64

In other words, create a minimal test case based on postgres:16
and compare the two environments to see how they might differ.

1 reply

afarber Mar 26, 2024
Author

I have added RUN ls -al $PGDATA and the dir is empty... not sure why, how to interpret this

ImreSamu · 2024-03-27T12:55:03Z

ImreSamu
Mar 27, 2024
Maintainer

I have created a similarly OSM-preconfigured image as an inspiration, and I will likely add it to the examples later.

with docker-ensure-initdb.sh postgres + default initialisation ..
with additional vaccumdb and logging
with multi-layering to minimize the final docker image size.

ARG POSTGRES_PASSWORD=mysecretpassword
ARG POSTGRES_INITDB_ARGS="--locale-provider=icu --icu-locale=en-US " 
FROM postgis/postgis:16-3.4 as base
ARG POSTGRES_PASSWORD
ARG POSTGRES_INITDB_ARGS

RUN set -eux \
    && apt-get update \
    && apt-get install -y --no-install-recommends \
       osm2pgsql wget

RUN mkdir -p /osmdata \
    && wget https://download.geofabrik.de/europe/monaco-latest.osm.pbf -P /osmdata

# https://github.com/docker-library/docs/blob/master/postgres/README.md#initialization-scripts
RUN set -eux \
  && echo "CREATE EXTENSION IF NOT EXISTS postgis;" >  /docker-entrypoint-initdb.d/11_hstore_init.sql \
  && echo "CREATE EXTENSION IF NOT EXISTS hstore;"  >> /docker-entrypoint-initdb.d/11_hstore_init.sql  

# remove the default postgis initialisation 
# postgis_tiger_geocoder is not need!  
RUN rm /docker-entrypoint-initdb.d/10_postgis.sh

RUN set -eux \  
  && echo 'osm2pgsql --username=postgres --database=$PGDATABASE --create --cache=2000 --hstore --latlong /osmdata/monaco-latest.osm.pbf' > /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh \
  && echo 'vacuumdb --all --full --echo --verbose --analyze' >> /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh \
  && chmod +x /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh

RUN set -eux \
 && echo "SELECT PostGIS_Full_Version(); SELECT version();"  > /docker-entrypoint-initdb.d/99_QA.sql \
 && echo '\dx' >> /docker-entrypoint-initdb.d/99_QA.sql \ 
 && echo '\l+' >> /docker-entrypoint-initdb.d/99_QA.sql \ 
 && echo '\dtiv+' >> /docker-entrypoint-initdb.d/99_QA.sql \
 && cat /docker-entrypoint-initdb.d/99_QA.sql

# list initialisation files
# see: https://github.com/docker-library/docs/blob/master/postgres/README.md#initialization-scripts
RUN ls -la /docker-entrypoint-initdb.d/* 

RUN set -eux \
    # https://github.com/docker-library/postgres/pull/1150
    # https://github.com/docker-library/postgres/blob/master/16/bullseye/docker-ensure-initdb.sh  
    # https://github.com/docker-library/postgres/blob/master/16/bullseye/docker-entrypoint.sh    
    && docker-ensure-initdb.sh postgres \
    && ls -la /var/lib/postgresql/data \
    && du -sh /var/lib/postgresql/data

FROM postgis/postgis:16-3.4
COPY --from=base /var/lib/postgresql/data /var/lib/postgresql/data

log

$ docker build --network=host  --progress=plain  -t test .
#0 building with "default" instance using docker driver

#1 [internal] load build definition from Dockerfile
#1 transferring dockerfile: 2.50kB done
#1 DONE 0.0s

#2 [internal] load metadata for docker.io/postgis/postgis:16-3.4
#2 DONE 0.0s

#3 [internal] load .dockerignore
#3 transferring context: 2B done
#3 DONE 0.0s

#4 [base 1/9] FROM docker.io/postgis/postgis:16-3.4
#4 CACHED

#5 [base 2/9] RUN set -eux     && apt-get update     && apt-get install -y --no-install-recommends        osm2pgsql wget
#5 0.078 + apt-get update
#5 0.181 Get:1 http://deb.debian.org/debian bullseye InRelease [116 kB]
#5 0.224 Get:2 http://deb.debian.org/debian-security bullseye-security InRelease [48.4 kB]
#5 0.240 Get:3 http://deb.debian.org/debian bullseye-updates InRelease [44.1 kB]
#5 0.252 Get:4 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg InRelease [123 kB]
#5 0.426 Get:5 http://deb.debian.org/debian bullseye/main amd64 Packages [8,068 kB]
#5 0.835 Get:6 http://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 Packages [309 kB]
#5 0.889 Get:7 http://deb.debian.org/debian-security bullseye-security/main amd64 Packages [270 kB]
#5 0.904 Get:8 http://deb.debian.org/debian bullseye-updates/main amd64 Packages [18.8 kB]
#5 2.023 Fetched 8,997 kB in 2s (4,680 kB/s)
#5 2.023 Reading package lists...
#5 2.555 + apt-get install -y --no-install-recommends osm2pgsql wget
#5 2.567 Reading package lists...
#5 3.125 Building dependency tree...
#5 3.264 Reading state information...
#5 3.451 The following additional packages will be installed:
#5 3.452   libboost-filesystem1.74.0 liblua5.3-0
#5 3.453 Recommended packages:
#5 3.453   postgis
#5 3.522 The following NEW packages will be installed:
#5 3.523   libboost-filesystem1.74.0 liblua5.3-0 osm2pgsql wget
#5 3.612 0 upgraded, 4 newly installed, 0 to remove and 0 not upgraded.
#5 3.612 Need to get 1,853 kB of archives.
#5 3.612 After this operation, 7,924 kB of additional disk space will be used.
#5 3.612 Get:1 http://deb.debian.org/debian bullseye/main amd64 wget amd64 1.21-1+deb11u1 [964 kB]
#5 3.702 Get:2 http://deb.debian.org/debian bullseye/main amd64 libboost-filesystem1.74.0 amd64 1.74.0-9 [283 kB]
#5 3.716 Get:3 http://deb.debian.org/debian bullseye/main amd64 liblua5.3-0 amd64 5.3.3-1.1+deb11u1 [123 kB]
#5 3.722 Get:4 http://deb.debian.org/debian bullseye/main amd64 osm2pgsql amd64 1.4.1+ds-2 [484 kB]
#5 3.904 debconf: delaying package configuration, since apt-utils is not installed
#5 3.940 Fetched 1,853 kB in 0s (8,505 kB/s)
#5 3.961 Selecting previously unselected package wget.
(Reading database ... 14186 files and directories currently installed.)
#5 3.976 Preparing to unpack .../wget_1.21-1+deb11u1_amd64.deb ...
#5 3.978 Unpacking wget (1.21-1+deb11u1) ...
#5 4.108 Selecting previously unselected package libboost-filesystem1.74.0:amd64.
#5 4.111 Preparing to unpack .../libboost-filesystem1.74.0_1.74.0-9_amd64.deb ...
#5 4.120 Unpacking libboost-filesystem1.74.0:amd64 (1.74.0-9) ...
#5 4.185 Selecting previously unselected package liblua5.3-0:amd64.
#5 4.187 Preparing to unpack .../liblua5.3-0_5.3.3-1.1+deb11u1_amd64.deb ...
#5 4.190 Unpacking liblua5.3-0:amd64 (5.3.3-1.1+deb11u1) ...
#5 4.224 Selecting previously unselected package osm2pgsql.
#5 4.227 Preparing to unpack .../osm2pgsql_1.4.1+ds-2_amd64.deb ...
#5 4.229 Unpacking osm2pgsql (1.4.1+ds-2) ...
#5 4.300 Setting up wget (1.21-1+deb11u1) ...
#5 4.308 Setting up libboost-filesystem1.74.0:amd64 (1.74.0-9) ...
#5 4.314 Setting up liblua5.3-0:amd64 (5.3.3-1.1+deb11u1) ...
#5 4.321 Setting up osm2pgsql (1.4.1+ds-2) ...
#5 4.328 Processing triggers for libc-bin (2.31-13+deb11u8) ...
#5 DONE 4.4s

#6 [base 3/9] RUN mkdir -p /osmdata     && wget https://download.geofabrik.de/europe/monaco-latest.osm.pbf -P /osmdata
#6 0.096 --2024-03-27 12:48:03--  https://download.geofabrik.de/europe/monaco-latest.osm.pbf
#6 0.105 Resolving download.geofabrik.de (download.geofabrik.de)... 2a01:4f9:5a:2797::2, 2a01:4f9:5a:25c3::2, 65.109.50.43, ...
#6 0.150 Connecting to download.geofabrik.de (download.geofabrik.de)|2a01:4f9:5a:2797::2|:443... connected.
#6 0.348 HTTP request sent, awaiting response... 200 OK
#6 0.407 Length: 539858 (527K) [application/octet-stream]
#6 0.407 Saving to: ‘/osmdata/monaco-latest.osm.pbf’
#6 0.407 
#6 0.407      0K .......... .......... .......... .......... ..........  9%  512K 1s
#6 0.504     50K .......... .......... .......... .......... .......... 18%  843K 1s
#6 0.564    100K .......... .......... .......... .......... .......... 28% 1.39M 0s
#6 0.599    150K .......... .......... .......... .......... .......... 37% 2.48M 0s
#6 0.618    200K .......... .......... .......... .......... .......... 47% 3.96M 0s
#6 0.631    250K .......... .......... .......... .......... .......... 56% 2.42M 0s
#6 0.651    300K .......... .......... .......... .......... .......... 66% 4.55M 0s
#6 0.662    350K .......... .......... .......... .......... .......... 75% 4.34M 0s
#6 0.673    400K .......... .......... .......... .......... .......... 85% 6.56M 0s
#6 0.680    450K .......... .......... .......... .......... .......... 94% 6.45M 0s
#6 0.688    500K .......... .......... .......                         100% 7.30M=0.3s
#6 0.692 
#6 0.692 2024-03-27 12:48:04 (1.81 MB/s) - ‘/osmdata/monaco-latest.osm.pbf’ saved [539858/539858]
#6 0.692 
#6 DONE 0.7s

#7 [base 4/9] RUN set -eux   && echo "CREATE EXTENSION IF NOT EXISTS postgis;" >  /docker-entrypoint-initdb.d/11_hstore_init.sql   && echo "CREATE EXTENSION IF NOT EXISTS hstore;"  >> /docker-entrypoint-initdb.d/11_hstore_init.sql
#7 0.091 + echo CREATE EXTENSION IF NOT EXISTS postgis;
#7 0.091 + echo CREATE EXTENSION IF NOT EXISTS hstore;
#7 DONE 0.1s

#8 [base 5/9] RUN rm /docker-entrypoint-initdb.d/10_postgis.sh
#8 DONE 0.1s

#9 [base 6/9] RUN set -eux   && echo 'osm2pgsql --username=postgres --database=$PGDATABASE --create --cache=2000 --hstore --latlong /osmdata/monaco-latest.osm.pbf' > /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh   && echo 'vacuumdb --all --full --echo --verbose --analyze' >> /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh   && chmod +x /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh
#9 0.083 + echo osm2pgsql --username=postgres --database=$PGDATABASE --create --cache=2000 --hstore --latlong /osmdata/monaco-latest.osm.pbf
#9 0.083 + echo vacuumdb --all --full --echo --verbose --analyze
#9 0.083 + chmod +x /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh
#9 DONE 0.1s

#10 [base 7/9] RUN set -eux  && echo "SELECT PostGIS_Full_Version(); SELECT version();"  > /docker-entrypoint-initdb.d/99_QA.sql  && echo '\dx' >> /docker-entrypoint-initdb.d/99_QA.sql  && echo '\l+' >> /docker-entrypoint-initdb.d/99_QA.sql  && echo '\dtiv+' >> /docker-entrypoint-initdb.d/99_QA.sql  && cat /docker-entrypoint-initdb.d/99_QA.sql
#10 0.091 + echo SELECT PostGIS_Full_Version(); SELECT version();
#10 0.091 + echo \dx
#10 0.091 + echo \l+
#10 0.091 + echo \dtiv+
#10 0.091 + cat /docker-entrypoint-initdb.d/99_QA.sql
#10 0.092 SELECT PostGIS_Full_Version(); SELECT version();
#10 0.092 \dx
#10 0.092 \l+
#10 0.092 \dtiv+
#10 DONE 0.1s

#11 [base 8/9] RUN ls -la /docker-entrypoint-initdb.d/*
#11 0.091 -rw-r--r-- 1 root root  79 Mar 27 12:48 /docker-entrypoint-initdb.d/11_hstore_init.sql
#11 0.091 -rwxr-xr-x 1 root root 174 Mar 27 12:48 /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh
#11 0.091 -rw-r--r-- 1 root root  64 Mar 27 12:48 /docker-entrypoint-initdb.d/99_QA.sql
#11 DONE 0.1s

#12 [base 9/9] RUN set -eux     && docker-ensure-initdb.sh postgres     && ls -la /var/lib/postgresql/data     && du -sh /var/lib/postgresql/data
#12 0.088 + docker-ensure-initdb.sh postgres
#12 0.137 The files belonging to this database system will be owned by user "postgres".
#12 0.137 This user must also own the server process.
#12 0.137 
#12 0.137 Using language tag "en-US" for ICU locale "en-US".
#12 0.137 The database cluster will be initialized with this locale configuration:
#12 0.137   provider:    icu
#12 0.137   ICU locale:  en-US
#12 0.137   LC_COLLATE:  en_US.utf8
#12 0.137   LC_CTYPE:    en_US.utf8
#12 0.137   LC_MESSAGES: en_US.utf8
#12 0.137   LC_MONETARY: en_US.utf8
#12 0.137   LC_NUMERIC:  en_US.utf8
#12 0.137   LC_TIME:     en_US.utf8
#12 0.137 The default database encoding has accordingly been set to "UTF8".
#12 0.137 The default text search configuration will be set to "english".
#12 0.137 
#12 0.137 Data page checksums are disabled.
#12 0.137 
#12 0.137 fixing permissions on existing directory /var/lib/postgresql/data ... ok
#12 0.137 creating subdirectories ... ok
#12 0.138 selecting dynamic shared memory implementation ... posix
#12 0.138 selecting default max_connections ... 100
#12 0.154 selecting default shared_buffers ... 128MB
#12 0.171 selecting default time zone ... Etc/UTC
#12 0.206 creating configuration files ... ok
#12 0.207 running bootstrap script ... ok
#12 0.355 performing post-bootstrap initialization ... ok
#12 0.742 syncing data to disk ... ok
#12 1.516 
#12 1.516 
#12 1.516 Success. You can now start the database server using:
#12 1.516 
#12 1.516     pg_ctl -D /var/lib/postgresql/data -l logfile start
#12 1.516 
#12 1.516 initdb: warning: enabling "trust" authentication for local connections
#12 1.516 initdb: hint: 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.
#12 1.561 waiting for server to start....2024-03-27 12:48:06.171 UTC [49] LOG:  starting PostgreSQL 16.2 (Debian 16.2-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
#12 1.586 2024-03-27 12:48:06.173 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
#12 1.590 2024-03-27 12:48:06.177 UTC [52] LOG:  database system was shut down at 2024-03-27 12:48:05 UTC
#12 1.595 2024-03-27 12:48:06.182 UTC [49] LOG:  database system is ready to accept connections
#12 1.661  done
#12 1.661 server started
#12 1.731 
#12 1.731 /usr/local/bin/docker-ensure-initdb.sh: running /docker-entrypoint-initdb.d/11_hstore_init.sql
#12 2.148 CREATE EXTENSION
#12 2.162 CREATE EXTENSION
#12 2.163 
#12 2.163 
#12 2.163 /usr/local/bin/docker-ensure-initdb.sh: running /docker-entrypoint-initdb.d/21_osm2pgsql_init.sh
#12 2.176 2024-03-27 12:48:06  osm2pgsql version 1.4.1
#12 2.182 2024-03-27 12:48:06  Database version: 16.2 (Debian 16.2-1.pgdg110+2)
#12 2.182 2024-03-27 12:48:06  PostGIS version: 3.4
#12 2.228 2024-03-27 12:48:06  Node-cache: cache=2000MB, maxblocks=32000*65536, allocation method=3
#12 2.232 2024-03-27 12:48:06  Setting up table 'planet_osm_point'
#12 2.254 2024-03-27 12:48:06  Setting up table 'planet_osm_line'
#12 2.277 2024-03-27 12:48:06  Setting up table 'planet_osm_polygon'
#12 2.304 2024-03-27 12:48:06  Setting up table 'planet_osm_roads'
2024-03-27 12:48:07  Reading input files done in 1s.                          
#12 2.432 2024-03-27 12:48:07    Processed 30653 nodes in 0s - 31k/s
#12 2.432 2024-03-27 12:48:07    Processed 4852 ways in 0s - 5k/s
#12 2.432 2024-03-27 12:48:07    Processed 290 relations in 1s - 290/s
#12 2.520 2024-03-27 12:48:07  node cache: stored: 30653(100.00%), storage efficiency: 44.12% (dense blocks: 1, sparse nodes: 30641), hit rate: 100.00%
#12 2.520 2024-03-27 12:48:07  Clustering table 'planet_osm_point' by geometry...
#12 2.520 2024-03-27 12:48:07  Clustering table 'planet_osm_line' by geometry...
#12 2.520 2024-03-27 12:48:07  Clustering table 'planet_osm_roads' by geometry...
#12 2.520 2024-03-27 12:48:07  Clustering table 'planet_osm_polygon' by geometry...
#12 2.555 2024-03-27 12:48:07  Creating geometry index on table 'planet_osm_roads'...
#12 2.558 2024-03-27 12:48:07  Analyzing table 'planet_osm_roads'...
#12 2.558 2024-03-27 12:48:07  Creating geometry index on table 'planet_osm_line'...
#12 2.559 2024-03-27 12:48:07  Creating geometry index on table 'planet_osm_polygon'...
#12 2.559 2024-03-27 12:48:07  Creating geometry index on table 'planet_osm_point'...
#12 2.566 2024-03-27 12:48:07  Analyzing table 'planet_osm_polygon'...
#12 2.568 2024-03-27 12:48:07  All postprocessing on table 'planet_osm_roads' done in 0s.
#12 2.572 2024-03-27 12:48:07  Analyzing table 'planet_osm_point'...
#12 2.572 2024-03-27 12:48:07  Analyzing table 'planet_osm_line'...
#12 2.586 2024-03-27 12:48:07  All postprocessing on table 'planet_osm_polygon' done in 0s.
#12 2.603 2024-03-27 12:48:07  All postprocessing on table 'planet_osm_point' done in 0s.
#12 2.610 2024-03-27 12:48:07  All postprocessing on table 'planet_osm_line' done in 0s.
#12 2.628 2024-03-27 12:48:07  osm2pgsql took 1s overall.
#12 2.681 SELECT pg_catalog.set_config('search_path', '', false);
#12 2.681 SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;
#12 2.681 SELECT pg_catalog.set_config('search_path', '', false);
#12 2.681 vacuumdb: vacuuming database "postgres"
#12 2.690 INFO:  vacuuming "public.spatial_ref_sys"
#12 2.711 INFO:  "public.spatial_ref_sys": found 0 removable, 8500 nonremovable row versions in 862 pages
#12 2.711 DETAIL:  0 dead row versions cannot be removed yet.
#12 2.711 CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s.
#12 2.729 INFO:  analyzing "public.spatial_ref_sys"
#12 2.736 INFO:  "spatial_ref_sys": scanned 862 of 862 pages, containing 8500 live rows and 0 dead rows; 8500 rows in sample, 8500 estimated total rows
#12 2.791 INFO:  vacuuming "public.planet_osm_line"
#12 2.808 INFO:  "public.planet_osm_line": found 0 removable, 3194 nonremovable row versions in 192 pages
#12 2.808 DETAIL:  0 dead row versions cannot be removed yet.
#12 2.808 CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s.
#12 2.830 INFO:  analyzing "public.planet_osm_line"
#12 2.832 INFO:  "planet_osm_line": scanned 146 of 146 pages, containing 3194 live rows and 0 dead rows; 3194 rows in sample, 3194 estimated total rows
#12 2.854 INFO:  vacuuming "public.planet_osm_polygon"
#12 2.859 INFO:  "public.planet_osm_polygon": found 0 removable, 1758 nonremovable row versions in 128 pages
#12 2.859 DETAIL:  0 dead row versions cannot be removed yet.
#12 2.859 CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
#12 2.868 INFO:  analyzing "public.planet_osm_polygon"
#12 2.869 INFO:  "planet_osm_polygon": scanned 76 of 76 pages, containing 1758 live rows and 0 dead rows; 1758 rows in sample, 1758 estimated total rows
.....
#12 4.347 
#12 4.347 /usr/local/bin/docker-ensure-initdb.sh: running /docker-entrypoint-initdb.d/99_QA.sql
#12 4.422                                                                                                                                                     postgis_full_version                                                                                                                                                     
#12 4.422 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#12 4.422  POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" GEOS="3.9.0-CAPI-1.16.2" 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" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
#12 4.422 (1 row)
#12 4.422 
#12 4.422                                                            version                                                           
#12 4.422 -----------------------------------------------------------------------------------------------------------------------------
#12 4.422  PostgreSQL 16.2 (Debian 16.2-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
#12 4.422 (1 row)
#12 4.422 
#12 4.424                                 List of installed extensions
#12 4.424   Name   | Version |   Schema   |                        Description                         
#12 4.424 ---------+---------+------------+------------------------------------------------------------
#12 4.424  hstore  | 1.8     | public     | data type for storing sets of (key, value) pairs
#12 4.424  plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
#12 4.424  postgis | 3.4.2   | public     | PostGIS geometry and geography spatial types and functions
#12 4.424 (3 rows)
#12 4.424 
#12 4.431                                                                                         List of databases
#12 4.431    Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description                 
#12 4.431 -----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------+---------+------------+--------------------------------------------
#12 4.431  postgres  | postgres | UTF8     | icu             | en_US.utf8 | en_US.utf8 | en-US      |           |                       | 18 MB   | pg_default | default administrative connection database
#12 4.431  template0 | postgres | UTF8     | icu             | en_US.utf8 | en_US.utf8 | en-US      |           | =c/postgres          +| 7337 kB | pg_default | unmodifiable empty database
#12 4.431            |          |          |                 |            |            |            |           | postgres=CTc/postgres |         |            | 
#12 4.431  template1 | postgres | UTF8     | icu             | en_US.utf8 | en_US.utf8 | en-US      |           | =c/postgres          +| 6569 kB | pg_default | default template for new databases
#12 4.431            |          |          |                 |            |            |            |           | postgres=CTc/postgres |         |            | 
#12 4.431 (3 rows)
#12 4.431 
#12 4.437                                                          List of relations
#12 4.437  Schema |            Name            | Type  |  Owner   |       Table        | Persistence | Access method |  Size   | Description 
#12 4.437 --------+----------------------------+-------+----------+--------------------+-------------+---------------+---------+-------------
#12 4.437  public | geography_columns          | view  | postgres |                    | permanent   |               | 0 bytes | 
#12 4.437  public | geometry_columns           | view  | postgres |                    | permanent   |               | 0 bytes | 
#12 4.437  public | planet_osm_line            | table | postgres |                    | permanent   | heap          | 1464 kB | 
#12 4.437  public | planet_osm_line_way_idx    | index | postgres | planet_osm_line    | permanent   | gist          | 136 kB  | 
#12 4.437  public | planet_osm_point           | table | postgres |                    | permanent   | heap          | 480 kB  | 
#12 4.437  public | planet_osm_point_way_idx   | index | postgres | planet_osm_point   | permanent   | gist          | 128 kB  | 
#12 4.437  public | planet_osm_polygon         | table | postgres |                    | permanent   | heap          | 632 kB  | 
#12 4.437  public | planet_osm_polygon_way_idx | index | postgres | planet_osm_polygon | permanent   | gist          | 104 kB  | 
#12 4.437  public | planet_osm_roads           | table | postgres |                    | permanent   | heap          | 296 kB  | 
#12 4.437  public | planet_osm_roads_way_idx   | index | postgres | planet_osm_roads   | permanent   | gist          | 24 kB   | 
#12 4.437  public | spatial_ref_sys            | table | postgres |                    | permanent   | heap          | 6904 kB | 
#12 4.437  public | spatial_ref_sys_pkey       | index | postgres | spatial_ref_sys    | permanent   | btree         | 208 kB  | 
#12 4.437 (12 rows)
#12 4.437 
#12 4.438 
#12 4.438 
#12 4.439 2024-03-27 12:48:09.026 UTC [49] LOG:  received fast shutdown request
#12 4.439 waiting for server to shut down....2024-03-27 12:48:09.027 UTC [49] LOG:  aborting any active transactions
#12 4.442 2024-03-27 12:48:09.028 UTC [49] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
#12 4.443 2024-03-27 12:48:09.030 UTC [50] LOG:  shutting down
#12 4.444 2024-03-27 12:48:09.031 UTC [50] LOG:  checkpoint starting: shutdown immediate
#12 4.598 2024-03-27 12:48:09.185 UTC [50] LOG:  checkpoint complete: wrote 478 buffers (2.9%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.006 s, sync=0.129 s, total=0.156 s; sync files=641, longest=0.003 s, average=0.001 s; distance=37078 kB, estimate=37078 kB; lsn=0/391CCB8, redo lsn=0/391CCB8
#12 4.602 2024-03-27 12:48:09.189 UTC [49] LOG:  database system is shut down
#12 4.640  done
#12 4.640 server stopped
#12 4.641 + ls -la /var/lib/postgresql/data
#12 4.643 total 136
#12 4.643 drwx------ 1 postgres postgres  4096 Mar 27 12:48 .
#12 4.643 drwxr-xr-x 1 postgres postgres  4096 Mar 12 02:00 ..
#12 4.643 drwx------ 5 postgres postgres  4096 Mar 27 12:48 base
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 global
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_commit_ts
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_dynshmem
#12 4.643 -rw------- 1 postgres postgres  5743 Mar 27 12:48 pg_hba.conf
#12 4.643 -rw------- 1 postgres postgres  2640 Mar 27 12:48 pg_ident.conf
#12 4.643 drwx------ 4 postgres postgres  4096 Mar 27 12:48 pg_logical
#12 4.643 drwx------ 4 postgres postgres  4096 Mar 27 12:48 pg_multixact
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_notify
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_replslot
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_serial
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_snapshots
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_stat
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_stat_tmp
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_subtrans
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_tblspc
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_twophase
#12 4.643 -rw------- 1 postgres postgres     3 Mar 27 12:48 PG_VERSION
#12 4.643 drwx------ 3 postgres postgres  4096 Mar 27 12:48 pg_wal
#12 4.643 drwx------ 2 postgres postgres  4096 Mar 27 12:48 pg_xact
#12 4.643 -rw------- 1 postgres postgres    88 Mar 27 12:48 postgresql.auto.conf
#12 4.643 -rw------- 1 postgres postgres 29770 Mar 27 12:48 postgresql.conf
#12 4.643 -rw------- 1 postgres postgres   105 Mar 27 12:48 postmaster.opts
#12 4.643 + du -sh /var/lib/postgresql/data
#12 4.650 81M	/var/lib/postgresql/data
#12 DONE 4.7s

#13 [stage-1 2/2] COPY --from=base /var/lib/postgresql/data /var/lib/postgresql/data
#13 DONE 0.2s

#14 exporting to image
#14 exporting layers
#14 exporting layers 0.4s done
#14 writing image sha256:b792838e8159edfb309f5cd840658a6b47fecebc3fc51df5424188630835bc5d done
#14 naming to docker.io/library/test done
#14 DONE 0.4s

0 replies

afarber · 2024-03-29T11:40:13Z

afarber
Mar 29, 2024
Author

Thank you for your suggestions, they have helped me to find the solution for the empty /var/lib/postgresql/data folder, when building a postgis/postgis:16.3 based Docker image with prefilled OSM database and I would like to share it here with other users -

After changing just a single line my Dockerfile has built successfully:

ENV PGDATA=/postgis

The reason seems to be that the parent official postgres Dockerfile contains this conflicting line:

VOLUME /var/lib/postgresql/data

and thus the folder I was trying to use originally was cleared.

Below is my working Dockerfile (and yes I understand that the built image is too big, especially with Europe data and thus I have to improve it further and possibly use VOLUME or some Azure storage there):

ARG POSTGIS_TAG
FROM postgis/postgis:$POSTGIS_TAG
EXPOSE 5432

# Install ps, top, netstat, curl, wget, osmium, osm2pgsql
RUN apt-get update && \
    apt-get upgrade -y && \
    apt install -y procps net-tools curl wget osmium-tool osm2pgsql

# place PostGIS database into /postgis folder
WORKDIR /postgis
RUN chown -R postgres:postgres /postgis
# download OSM data files into /data folder
WORKDIR /data
RUN chown -R postgres:postgres /data
# drop root privilleges
USER postgres

# Download one or multiple OSM files
ARG DOWNLOAD_URLS
RUN wget --no-verbose $DOWNLOAD_URLS
# Print summaries of the downloaded OSM files
RUN for f in *.osm.pbf; do osmium fileinfo -e "$f"; done
# Merge one or multiple files into a new file map.osm.pbf
RUN osmium merge *.osm.pbf -o map.osm.pbf
# Remove all files except the map.osm.pbf
RUN find . -type f -name '*.osm.pbf' | grep -v 'map.osm.pbf' | xargs rm -f

ARG OSM_PASSWORD
ENV PGPASSWORD=$OSM_PASSWORD
ENV PGUSER=osm_user
ENV PGDATABASE=osm_database
# do not use the /var/lib/postgresql/data folder here!
ENV PGDATA=/postgis

# create PostgreSQL instance in the $PGDATA folder
# configure PostgreSQL as recommended by osm2pgsql doc
# start PostgreSQL and create database and user
# load data from the map.osm.pbf file into the database
# remove the map.osm.pbf file and stop PostgreSQL
# configure password based access for osm_user
RUN set -eux && \
    pg_ctl init && \
    echo "shared_buffers = 1GB"                >> $PGDATA/postgresql.conf && \
    echo "work_mem = 50MB"                     >> $PGDATA/postgresql.conf && \
    echo "maintenance_work_mem = 10GB"         >> $PGDATA/postgresql.conf && \
    echo "autovacuum_work_mem = 2GB"           >> $PGDATA/postgresql.conf && \
    echo "wal_level = minimal"                 >> $PGDATA/postgresql.conf && \
    echo "checkpoint_timeout = 60min"          >> $PGDATA/postgresql.conf && \
    echo "max_wal_size = 10GB"                 >> $PGDATA/postgresql.conf && \
    echo "checkpoint_completion_target = 0.9"  >> $PGDATA/postgresql.conf && \
    echo "max_wal_senders = 0"                 >> $PGDATA/postgresql.conf && \
    echo "random_page_cost = 1.0"              >> $PGDATA/postgresql.conf && \
    echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \
    pg_ctl start && \ 
    createuser --username=postgres $PGUSER && \
    createdb --username=postgres --encoding=UTF8 --owner=$PGUSER $PGDATABASE && \
    psql --username=postgres $PGDATABASE --command="ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';" && \
    psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS postgis;' && \
    psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS hstore;' && \
    osm2pgsql --username=$PGUSER --database=$PGDATABASE --create --cache=60000 --hstore --latlong /data/map.osm.pbf && \
    rm -f /data/map.osm.pbf && \
    pg_ctl stop && \
    echo '# TYPE DATABASE USER ADDRESS METHOD'                > $PGDATA/pg_hba.conf && \
    echo "local all postgres peer"                           >> $PGDATA/pg_hba.conf && \
    echo "local $PGDATABASE $PGUSER           scram-sha-256" >> $PGDATA/pg_hba.conf && \
    echo "host  $PGDATABASE $PGUSER 0.0.0.0/0 scram-sha-256" >> $PGDATA/pg_hba.conf

Example build command:

docker build -f Dockerfile --no-cache --tag my-postgis:16-3.4 \
    --build-arg DOWNLOAD_URLS="https://download.geofabrik.de/europe/germany/berlin-latest.osm.pbf https://download.geofabrik.de/europe/germany/hamburg-latest.osm.pbf" \
    --build-arg OSM_PASSWORD=osm_password \
    --build-arg POSTGIS_TAG=16-3.4 .

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Prepopulating PostGIS with OSM data works with Docker Desktop, but not with Azure pipeline #379

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 3 comments 1 reply

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Select a reply

Prepopulating PostGIS with OSM data works with Docker Desktop, but not with Azure pipeline #379

afarber Mar 26, 2024

Replies: 3 comments · 1 reply

ImreSamu Mar 26, 2024 Maintainer

afarber Mar 26, 2024 Author

ImreSamu Mar 27, 2024 Maintainer

log

afarber Mar 29, 2024 Author

afarber
Mar 26, 2024

Replies: 3 comments 1 reply

ImreSamu
Mar 26, 2024
Maintainer

afarber Mar 26, 2024
Author

ImreSamu
Mar 27, 2024
Maintainer

afarber
Mar 29, 2024
Author