postgresql - docker-entrypoint-initdb.d 中的 Docker PostgreSQL 初始化脚本无法导入文件

Question

我正在尝试在 Docker 容器中设置 PostgreSQL。我需要创建两个 用户和多个 数据库。

目录结构

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

Dockerfile 内容

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

初始化.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;

我能够成功构建图像。当我运行 docker run -it ` 时，这里是控制台输出的一个片段:

****************************************************
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

为什么找不到文件 db_init_data_globals.sql？它与 init.sql 在同一个文件夹中!

此外，为什么每次我运行命令时都会创建数据库、表等？我以为初始化是在构建容器期间完成的？

Answer 1

我怀疑你的错误是由 \i 命令的行为引起的:

\i or \include filename

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

从library/postgres:9.6 docker-entrypoint.sh file可以看出, .sql 文件使用 psql -f(作为脚本)执行。每个包含的文件 is expected to be in psql's current working directory .

你应该使用\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.

来源:Postgres doc .

如果您需要使它与旧版本的 Postgres 一起使用，check this answer .