postgresql - 从 initdb.d 启动 Postgres docker image SQL 时，执行但未创建 db/schema/table

Question

我正在通过 dokcer-compose 启动 postgresql 12.3 图像我扩展了图像以在启动时执行简单的 SQL（创建模式、表）。从日志中我可以看到 SQL 命令已执行，但是当我登录到数据库时，更改丢失了。

我的 Dockerfile：

FROM postgres

ENV INITDB_DIR /docker-entrypoint-initdb.d
ENV POSTGRES_PASSWORD=...
ENV POSTGRES_DB mydb

COPY initdb.d ${INITDB_DIR}

我的 docker-compose.yml：

version: "3.7"
services:
  postgres:
    image: postgres
    build: ./postgres
    restart: on-failure
    ports:
      - "5432:5432"
    networks:
      - postgres
    environment:
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:...}
      - POSTGRES_DB=mydb

networks:
  postgres:
    driver: bridge

create_schema.sql（位于 ./postgres/initdb.d）

create schema "foo";
create table "foo"."message"(
  "id" BIGINT NOT NULL,
  "value" TEXT
);
alter table "foo"."message" add constraint "foo_message_pk" primary key("id");

最后是执行时产生的日志： docker-compose up --build postgres

postgres_1                | The files belonging to this database system will be owned by user "postgres".
postgres_1                | This user must also own the server process.
postgres_1                |
postgres_1                | The database cluster will be initialized with locale "en_US.utf8".
postgres_1                | The default database encoding has accordingly been set to "UTF8".
postgres_1                | The default text search configuration will be set to "english".
postgres_1                |
postgres_1                | Data page checksums are disabled.
postgres_1                |
postgres_1                | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres_1                | creating subdirectories ... ok
postgres_1                | selecting dynamic shared memory implementation ... posix
postgres_1                | selecting default max_connections ... 100
postgres_1                | selecting default shared_buffers ... 128MB
postgres_1                | selecting default time zone ... Etc/UTC
postgres_1                | creating configuration files ... ok
postgres_1                | running bootstrap script ... ok
postgres_1                | performing post-bootstrap initialization ... ok
postgres_1                | syncing data to disk ... ok
postgres_1                |
postgres_1                |
postgres_1                | Success. You can now start the database server using:
postgres_1                |
postgres_1                |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres_1                |
postgres_1                | initdb: warning: enabling "trust" authentication for local connections
postgres_1                | You can change this by editing pg_hba.conf or using the option -A, or
postgres_1                | --auth-local and --auth-host, the next time you run initdb.
postgres_1                | waiting for server to start....2020-05-27 07:34:00.098 UTC [47] LOG:  starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
postgres_1                | 2020-05-27 07:34:00.112 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1                | 2020-05-27 07:34:00.143 UTC [48] LOG:  database system was shut down at 2020-05-27 07:33:59 UTC
postgres_1                | 2020-05-27 07:34:00.151 UTC [47] LOG:  database system is ready to accept connections
postgres_1                |  done
postgres_1                | server started
postgres_1                | CREATE DATABASE
postgres_1                |
postgres_1                |
postgres_1                | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_schema.sql
postgres_1                | CREATE SCHEMA
postgres_1                | CREATE TABLE
postgres_1                | ALTER TABLE
postgres_1                |
postgres_1                |
postgres_1                | waiting for server to shut down....2020-05-27 07:34:00.588 UTC [47] LOG:  received fast shutdown request
postgres_1                | 2020-05-27 07:34:00.593 UTC [47] LOG:  aborting any active transactions
postgres_1                | 2020-05-27 07:34:00.595 UTC [47] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
postgres_1                | 2020-05-27 07:34:00.595 UTC [49] LOG:  shutting down
postgres_1                | 2020-05-27 07:34:00.643 UTC [47] LOG:  database system is shut down
postgres_1                |  done
postgres_1                | server stopped
postgres_1                |
postgres_1                | PostgreSQL init process complete; ready for start up.
postgres_1                |
postgres_1                | 2020-05-27 07:34:00.708 UTC [1] LOG:  starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
postgres_1                | 2020-05-27 07:34:00.709 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres_1                | 2020-05-27 07:34:00.709 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres_1                | 2020-05-27 07:34:00.719 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres_1                | 2020-05-27 07:34:00.744 UTC [74] LOG:  database system was shut down at 2020-05-27 07:34:00 UTC
postgres_1                | 2020-05-27 07:34:00.753 UTC [1] LOG:  database system is ready to accept connections

当我连接到数据库时，只存在默认数据库（postgres）。我也在没有创建 mydb 数据库的情况下尝试了上述方法，但架构或表仍然不存在。

标签： postgresqldockerdocker-compose