Docker PostgreSQL initialisation script laced in docker-entrypoint-initdb.d’ fails to import file

Question

I am trying to set up PostgreSQL in a Docker container. I need to create two users and multiple databases.

Directory structure

pgdb/
   Dockerfile
   sql/
      ext_cfuncs/
      sprocs/
      init.sql
      db_init_data_globals.sql
      ...

Dockerfile contents

FROM library/postgres:9.6
COPY sql /docker-entrypoint-initdb.d/

init.sql

-- ##############################################
-- #                                            #
-- #  Create users                              #
-- #                                            #
-- ##############################################

-- Create superuser
-- need to be a superuser n order to create functions in C etc
CREATE ROLE dbuser1 ENCRYPTED PASSWORD '<redacted>' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

-- Create dbuser2
CREATE ROLE dbuser2 ENCRYPTED PASSWORD '<redacted';
CREATE DATABASE refglobals WITH ENCODING 'UTF8' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE refglobals TO dbuser2;
GRANT ALL PRIVILEGES ON DATABASE refglobals TO dbuser1;

-- Import refglobals and news initial data
\c refglobals;
\i db_init_data_globals.sql;


# Create database to be used as template for other databases
CREATE DATABASE foobar WITH ENCODING 'UTF8' TEMPLATE template0;
\c foobar;

CREATE LANGUAGE 'plpgsql';
CREATE EXTENSION quantile;

-- # enable python in database
-- # http://www.vertabelo.com/blog/technical-articles/playing-around-with-python-in-postgresql
-- # /github.com/ihuston/plpython_examples/blob/master/simple_examples.sql
CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler;
UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpythonu';

\i db_schema_foobar.sql;
\i ext_cfuncs/funcs_scanners_internal.sql;

I am able to successfully build the image. When I run docker run -it `, here is a snippet of the console output:

****************************************************
WARNING: No password has been set for the database.
         This will allow anyone with access to the
         Postgres port to access your database. In
         Docker's default configuration, this is
         effectively any other container on the same
         system.

         Use "-e POSTGRES_PASSWORD=password" to set
         it in "docker run".
****************************************************
waiting for server to start....LOG:  could not bind IPv6 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was shut down at 2017-09-27 20:37:08 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
 done
server started
ALTER ROLE

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_databases.sql
CREATE ROLE
CREATE ROLE
CREATE DATABASE
GRANT
GRANT
You are now connected to database "refglobals" as user "postgres".
psql:/docker-entrypoint-initdb.d/init.sql:19: db_init_data_globals.sql: No such file or directory

Why is the file db_init_data_globals.sql not found? It's in the same folder as init.sql!

Also, why are the databases, tables etc being created every time I run the command? I thought the initialisation was done during the building of the container?

I can't see db_init_data_globals.sql in your sql folder, where is it located? — lifeisfoo
– lifeisfoo, Commented Sep 27, 2017 at 21:10
Sorry, silly omission on my part when posting the question. Yes the files is there, along with about twenty others. I've modified by question — Homunculus Reticulli
– Homunculus Reticulli, Commented Sep 27, 2017 at 21:24
Did you try adding the full path to your sql file? \i /docker-entrypoint-initdb.d/db_init_data_globals.sql; — BMitch
– BMitch, Commented Sep 27, 2017 at 21:40

lifeisfoo · Accepted Answer · 2017-09-27 21:42:53Z

1

I suspect that your error is caused by the behavior of the \i command:

\i or \include filename

Reads input from the file filename and executes it as though it had been typed on the keyboard.

As you can see from library/postgres:9.6 docker-entrypoint.sh file, .sql files are executed using psql -f (as scripts). Every included file is expected to be in psql's current working directory.

You should use \ir:

\ir or \include_relative filename

The \ir command is similar to \i, but resolves relative file names differently. When executing in interactive mode, the two commands behave identically. However, when invoked from a script, \ir interprets file names relative to the directory in which the script is located, rather than the current working directory.

Source: Postgres doc.

If you need to make this working with older versions of Postgres, check this answer.

answered Sep 27, 2017 at 21:42

lifeisfoo

16.6k6 gold badges80 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

Or \i with the full path to the files, /docker-entrypoint-initdb.d/$file

Collectives™ on Stack Overflow

Docker PostgreSQL initialisation script laced in docker-entrypoint-initdb.d’ fails to import file

Directory structure

Dockerfile contents

init.sql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Directory structure

Dockerfile contents

init.sql

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related