Docker Compose初始化PostgreSQL时SQL表未创建问题排查

问题背景 #

使用docker-compose初始化PostgreSQL容器，编写了SQL初始化文件用于创建数据库和数据表。实际运行后，数据库成功创建，但数据表未生成，同时日志中出现"received fast shutdown request"错误。

错误日志 #

Attaching to pgadmin, postgres
postgres  | The files belonging to this database system will be owned by user "postgres".
postgres  | This user must also own the server process.
postgres  | 
postgres  | The database cluster will be initialized with locale "en_US.utf8".
postgres  | The default database encoding has accordingly been set to "UTF8".
postgres  | The default text search configuration will be set to "english".
postgres  | 
postgres  | Data page checksums are disabled.
postgres  | 
postgres  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres  | creating subdirectories ... ok
postgres  | selecting dynamic shared memory implementation ... posix
postgres  | selecting default max_connections ... 100
postgres  | selecting default shared_buffers ... 128MB
postgres  | selecting default time zone ... Etc/UTC
postgres  | creating configuration files ... ok
postgres  | running bootstrap script ... ok
postgres  | performing post-bootstrap initialization ... ok
postgres  | initdb: warning: enabling "trust" authentication for local connections
postgres  | 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.
postgres  | syncing data to disk ... ok
postgres  | 
postgres  | 
postgres  | Success. You can now start the database server using:
postgres  | 
postgres  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres  | 
postgres  | waiting for server to start....2023-07-28 20:58:38.129 UTC [49] LOG:  starting PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres  | 2023-07-28 20:58:38.130 UTC [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres  | 2023-07-28 20:58:38.133 UTC [52] LOG:  database system was shut down at 2023-07-28 20:58:38 UTC
postgres  | 2023-07-28 20:58:38.136 UTC [49] LOG:  database system is ready to accept connections
postgres  |  done
postgres  | server started
postgres  | CREATE DATABASE
postgres  | 
postgres  | 
postgres  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
postgres  | CREATE DATABASE
postgres  | CREATE SCHEMA
postgres  | psql:/docker-entrypoint-initdb.d/init.sql:13: NOTICE:  schema "public" already exists, skipping
postgres  | COMMENT
postgres  | GRANT
postgres  | GRANT
postgres  | CREATE TABLE
postgres  | ALTER TABLE
postgres  | CREATE TABLE
postgres  | ALTER TABLE
postgres  | CREATE TABLE
postgres  | ALTER TABLE
postgres  | CREATE INDEX
postgres  | CREATE TABLE
postgres  | ALTER TABLE
postgres  | CREATE INDEX
postgres  | CREATE INDEX
postgres  | 
postgres  | 
postgres  | waiting for server to shut down...2023-07-28 20:58:38.338 UTC [49] LOG:  received fast shutdown request
postgres  | .2023-07-28 20:58:38.338 UTC [49] LOG:  aborting any active transactions
postgres  | 2023-07-28 20:58:38.342 UTC [49] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
postgres  | 2023-07-28 20:58:38.344 UTC [50] LOG:  shutting down
postgres  | 2023-07-28 20:58:38.345 UTC [50] LOG:  checkpoint starting: shutdown immediate
postgres  | 2023-07-28 20:58:38.388 UTC [50] LOG:  checkpoint complete: wrote 1847 buffers (11.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.022 s, sync=0.020 s, total=0.044 s; sync files=502, longest=0.006 s, average=0.001 s; distance=8544 kB, estimate=8544 kB
postgres  | 2023-07-28 20:58:38.398 UTC [49] LOG:  database system is shut down
postgres  |  done
postgres  | server stopped
postgres  | 
postgres  | PostgreSQL init process complete; ready for start up.
postgres  | 
postgres  | 2023-07-28 20:58:38.458 UTC [1] LOG:  starting PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres  | 2023-07-28 20:58:38.458 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres  | 2023-07-28 20:58:38.458 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres  | 2023-07-28 20:58:38.459 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres  | 2023-07-28 20:58:38.462 UTC [67] LOG:  database system was shut down at 2023-07-28 20:58:38 UTC
postgres  | 2023-07-28 20:58:38.482 UTC [1] LOG:  database system is ready to accept connections

SQL初始化文件 #

CREATE DATABASE "FleetManagement"
    WITH
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

CREATE SCHEMA IF NOT EXISTS public
    AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public
    IS 'standard public schema';

GRANT USAGE ON SCHEMA public TO PUBLIC;

GRANT ALL ON SCHEMA public TO pg_database_owner;


CREATE TABLE IF NOT EXISTS public."DeliveryPoints"
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "DeliveryPointName" text COLLATE pg_catalog."default" NOT NULL,
    "Value" integer NOT NULL,
    CONSTRAINT "PK_DeliveryPoints" PRIMARY KEY ("Id")
)

    TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."DeliveryPoints"
    OWNER to admin;

CREATE TABLE IF NOT EXISTS public."Logs"
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "Barcode" text COLLATE pg_catalog."default" NOT NULL,
    "State" integer NOT NULL,
    "DeliveryPointValue" integer NOT NULL,
    CONSTRAINT "PK_Logs" PRIMARY KEY ("Id")
)

    TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Logs"
    OWNER to admin;


CREATE TABLE IF NOT EXISTS public."Sacks"
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "Barcode" text COLLATE pg_catalog."default" NOT NULL,
    "State" integer NOT NULL,
    "DeliveryPointId" integer NOT NULL,
    CONSTRAINT "PK_Sacks" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Sacks_DeliveryPoints_DeliveryPointId" FOREIGN KEY ("DeliveryPointId")
        REFERENCES public."DeliveryPoints" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

    TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Sacks"
    OWNER to admin;
-- Index: IX_Sacks_DeliveryPointId

-- DROP INDEX IF EXISTS public."IX_Sacks_DeliveryPointId";

CREATE INDEX IF NOT EXISTS "IX_Sacks_DeliveryPointId"
    ON public."Sacks" USING btree
        ("DeliveryPointId" ASC NULLS LAST)
    TABLESPACE pg_default;


-- Table: public.Packages

-- DROP TABLE IF EXISTS public."Packages";

CREATE TABLE IF NOT EXISTS public."Packages"
(
    "Id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "Desi" integer NOT NULL,
    "Barcode" text COLLATE pg_catalog."default" NOT NULL,
    "State" integer NOT NULL,
    "DeliveryPointId" integer NOT NULL,
    "SackId" integer NOT NULL DEFAULT 0,
    CONSTRAINT "PK_Packages" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Packages_DeliveryPoints_DeliveryPointId" FOREIGN KEY ("DeliveryPointId")
        REFERENCES public."DeliveryPoints" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT "FK_Packages_Sacks_SackId" FOREIGN KEY ("SackId")
        REFERENCES public."Sacks" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

    TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Packages"
    OWNER to admin;
-- Index: IX_Packages_DeliveryPointId

-- DROP INDEX IF EXISTS public."IX_Packages_DeliveryPointId";

CREATE INDEX IF NOT EXISTS "IX_Packages_DeliveryPointId"
    ON public."Packages" USING btree
        ("DeliveryPointId" ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: IX_Packages_SackId

-- DROP INDEX IF EXISTS public."IX_Packages_SackId";

CREATE INDEX IF NOT EXISTS "IX_Packages_SackId"
    ON public."Packages" USING btree
        ("SackId" ASC NULLS LAST)
    TABLESPACE pg_default;

docker-compose配置文件 #

services:

postgres:
  container_name: postgres
  image: postgres
  environment:
    POSTGRES_USER: ${POSTGRES_USER:-admin}
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-fatih}
    PGDATA: /data/postgres
  volumes:
    - ./Distribute.Api/init.sql:/docker-entrypoint-initdb.d/init.sql
  ports:
    - "5432:5432"
  restart: on-failure:10

pgadmin:
  container_name: pgadmin
  image: dpage/pgadmin4
  environment:
    PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-admin@admin.com}
    PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-fatih}
    PGADMIN_CONFIG_SERVER_MODE: 'False'
  ports:
    - "${PGADMIN_PORT:-5050}:80"
  depends_on:
    - postgres

问题分析与解决 #

1. "received fast shutdown request"错误原因 #

该日志是PostgreSQL官方镜像的正常初始化流程，并非异常：

• 容器启动时会先启动临时数据库服务，执行/docker-entrypoint-initdb.d/目录下的初始化脚本
• 脚本执行完成后，临时服务会被关闭，随后启动正式的数据库服务对外提供连接
  因此这个日志无需处理，属于正常流程环节。

2. 数据表未生成的原因 #

从日志可以看到SQL脚本的CREATE TABLE语句已执行，但数据表未出现在FleetManagement数据库中，核心原因是初始化脚本默认在postgres默认数据库中执行，所有表都被创建在了postgres数据库的public schema下，而非目标数据库FleetManagement。

解决方法 #

有两种可行方案：

方案一：修改SQL脚本切换数据库

在CREATE DATABASE语句之后，添加切换数据库的命令，确保后续表创建操作在目标库中执行：

\c "FleetManagement";

方案二：通过环境变量指定默认数据库

在docker-compose的postgres服务环境变量中添加POSTGRES_DB，让容器直接以FleetManagement作为默认数据库，这样初始化脚本会直接在该库中执行，无需手动切换：

environment:
  POSTGRES_USER: ${POSTGRES_USER:-admin}
  POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-fatih}
  PGDATA: /data/postgres
  POSTGRES_DB: FleetManagement

这种情况下，SQL脚本中可以删除CREATE DATABASE语句，避免重复创建数据库的提示。

内容的提问来源于stack exchange，提问作者Fatih