[GIS] Docker PostGIS .sql files not loading into database at build

dockerpostgis

I’ve recently started using docker and I’m trying to create a postgis docker image with raster and vector data inserted to a database at initialisation. I have no need to change or add to the data after this. I just want this to be available for requests from a website and geoserver.

I have made the image using the following Dockerfile:

    FROM mdillon/postgis

COPY ./nga_bth.sql /docker-entrypoint-initdb.d/
COPY ./nga_preg.sql /docker-entrypoint-initdb.d/
COPY ./nga_adm1.sql /docker-entrypoint-initdb.d/

EXPOSE 5432

The first 2 sql files are raster, the last is a vector.

I build the image using:

docker build -t dillon .

And I start a container with the following:

docker run -d -p 4000:5432 --name postgis_test -e POSTGRES_DB=gis -e POSTGRES_USER=postgres -it -e POSTGRES_PASSWORD=postgres dillon:latest

The container starts and then immediately disappears. I’ve tried this with different combinations of the above sql files. I checked the log on the container and it showed the following when I tried to load shapefiles:

server started
CREATE DATABASE

ALTER ROLE


/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/nga_adm1.sql
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
2017-11-29 19:07:35.043 UTC [73] ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist at character 8
2017-11-29 19:07:35.043 UTC [73] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2017-11-29 19:07:35.043 UTC [73] STATEMENT:  SELECT AddGeometryColumn('public','nga_adm1','geom','4326','MULTIPOLYGON',2);
psql:/docker-entrypoint-initdb.d/nga_adm1.sql:18: ERROR:  function addgeometrycolumn(unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('public','nga_adm1','geom','4326','...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

And the following when trying to load rasters:

server started
CREATE DATABASE

ALTER ROLE


/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/nga_bth.sql
BEGIN
psql:/docker-entrypoint-initdb.d/nga_bth.sql:2: NOTICE:  table "nga_bth" does not exist, skipping
DROP TABLE
2017-11-29 19:03:39.043 UTC [73] ERROR:  type "raster" does not exist at character 66
2017-11-29 19:03:39.043 UTC [73] STATEMENT:  CREATE TABLE "public"."nga_bth" ("rid" serial PRIMARY KEY,"rast" raster,"filename" text);
psql:/docker-entrypoint-initdb.d/nga_bth.sql:3: ERROR:  type "raster" does not exist
LINE 1: ...ublic"."nga_bth" ("rid" serial PRIMARY KEY,"rast" raster,"fi…

I made the sql files from shp2pgsql and raster2pgsql for the vectors and rasters respectively. The above errors suggest to me that PostGIS isn’t enabled, which I don’t think is the case.

Could someone please tell me how I might create a postgis container with pre-inserted data?

Best Answer

I have a hunch about this, but you’ll need to try this out to see if it’s correct :-)

As user30184 pointed out, it looks like you’re running a PostGIS query on a database without the PostGIS extension installed.

The mdillon/postgis dockerfile copies a file (as postgis.sh) into the same directory as your .sql files.

postgis.sh sets up a Postgres template, then it installs extensions (including PostGIS) on all databases.

This dockerfile doesn’t have a CMD or ENTRYPOINT directive. So when you run the container, the start point is really implemented in a base image (the standard docker postgres image). That entrypoint is docker-entrypoint.sh .

When docker-entrypoint.sh runs, it finds .sql scripts and .sh scripts in the /docker-entrypoint-initdb.d/ directory and executes them. You can see the code for that here

Your scripts start with an “n”, so they run before the “postgis.sh” script. That may explain your errors.

You could try renaming your scripts to go after “postgis.sh” in the alphabet, e.g. “z_myscript.sql”. Or even “postgis_myscript.sql”

If that works it’s of a bit of a kludge, there are probably (several) better ways to do this :-)

Related Question