bash 如何在脚本中为 Docker Postgres 创建用户/数据库

How to create User/Database in script for Docker Postgres

提问by pech0rin

I have been trying to set up a container for a development postgres instance by creating a custom user & database. I am using the official postgres docker image. In the documentation it instructs you to insert a bash script inside of the /docker-entrypoint-initdb.d/folder to set up the database with any custom parameters.

我一直在尝试通过创建自定义用户和数据库来为开发 postgres 实例设置容器。我正在使用官方的 postgres docker 镜像。在文档中，它指示您在/docker-entrypoint-initdb.d/文件夹内插入一个 bash 脚本，以使用任何自定义参数设置数据库。

My bash script: make_db.sh

我的 bash 脚本：make_db.sh

su postgres -c "createuser -w -d -r -s docker"
su postgres -c "createdb -O docker docker"

Dockerfile

文件

FROM library/postgres

RUN ["mkdir", "/docker-entrypoint-initdb.d"]
ADD make_db.sh /docker-entrypoint-initdb.d/

The error I get from the docker logs -f db(db is my container name) is:

我从docker logs -f db（db 是我的容器名称）得到的错误是：

createuser: could not connect to database postgres: could not connect to server: No such file or directory

createuser：无法连接到数据库 postgres：无法连接到服务器：没有那个文件或目录

It seems that the commands inside of the /docker-entrypoint-initdb.d/folder are being executed before postgres is started. My question is, how do I set up a user/database programmatically using the official postgres container? Is there any way to do this with a script?

似乎/docker-entrypoint-initdb.d/在启动 postgres 之前正在执行文件夹内的命令。我的问题是，如何使用官方 postgres 容器以编程方式设置用户/数据库？有没有办法用脚本来做到这一点？

回答by Thomasleveil

EDIT - since Jul 23, 2015

编辑 - 自 2015 年 7 月 23 日起

The official postgres docker imagewill run .sqlscripts found in the /docker-entrypoint-initdb.d/folder.

在正式的Postgres泊坞窗图像将运行.sql脚本中找到的/docker-entrypoint-initdb.d/文件夹中。

So all you need is to create the following sql script:

因此，您只需要创建以下 sql 脚本：

init.sql

初始化程序

CREATE USER docker;
CREATE DATABASE docker;
GRANT ALL PRIVILEGES ON DATABASE docker TO docker;

and add it in your Dockerfile:

并将其添加到您的 Dockerfile 中：

Dockerfile

文件

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

---

---

But since July 8th, 2015, if all you need is to create a user and database, it is easier to just make use to the POSTGRES_USER, POSTGRES_PASSWORDand POSTGRES_DBenvironment variables:

但是从 2015 年 7 月 8 日起，如果您只需要创建一个用户和数据库，那么使用POSTGRES_USER,POSTGRES_PASSWORD和POSTGRES_DB环境变量会更容易：

docker run -e POSTGRES_USER=docker -e POSTGRES_PASSWORD=docker -e POSTGRES_DB=docker library/postgres

or with a Dockerfile:

或使用 Dockerfile：

FROM library/postgres
ENV POSTGRES_USER docker
ENV POSTGRES_PASSWORD docker
ENV POSTGRES_DB docker

---

---

for images older than Jul 23, 2015

对于早于 2015 年 7 月 23 日的图像

From the documentation of the postgres Docker image, it is said that

从postgres Docker 镜像的文档中，据说

[...] it will source any *.sh script found in that directory [/docker-entrypoint-initdb.d] to do further initialization before starting the service

[...] 它将在该目录 [ /docker-entrypoint-initdb.d] 中找到任何 *.sh 脚本以在启动服务之前进行进一步的初始化

What's important here is "before starting the service". This means your script make_db.shwill be executed before the postgres service would be started, hence the error message "could not connect to database postgres".

这里重要的是“在开始服务之前”。这意味着您的脚本make_db.sh将在 postgres 服务启动之前执行，因此错误消息“无法连接到数据库 postgres”。

After that there is another useful piece of information:

之后还有一条有用的信息：

If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended.

如果您需要在初始化过程中执行 SQL 命令，强烈建议使用 Postgres 单用户模式。

Agreed this can be a bit mysterious at the first look. What it says is that your initialization script should start the postgres service in single mode before doing its actions. So you could change your make_db.kshscript as follows and it should get you closer to what you want:

同意这乍一看可能有点神秘。它说的是您的初始化脚本应该在执行其操作之前以单一模式启动 postgres 服务。所以你可以改变你的make_db.ksh脚本如下，它应该让你更接近你想要的：

NOTE, this has changed recently in the following commit. This will work with the latest change:

注意，这最近在以下提交中发生了变化。这将适用于最新的更改：

export PGUSER=postgres
psql <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

Previously, the use of --singlemode was required:

以前，需要使用--single模式：

gosu postgres postgres --single <<- EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

回答by m0meni

You can now put .sql files inside the init directory:

您现在可以将 .sql 文件放在 init 目录中：

From the docs

从文档

If you would like to do additional initialization in an image derived from this one, add one or more *.sql or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files and source any *.sh scripts found in that directory to do further initialization before starting the service.

如果您想在从该映像派生的映像中进行其他初始化，请在 /docker-entrypoint-initdb.d 下添加一个或多个 *.sql 或 *.sh 脚本（如有必要，请创建目录）。在入口点调用 initdb 以创建默认的 postgres 用户和数据库后，它将运行任何 *.sql 文件并在该目录中找到任何 *.sh 脚本以在启动服务之前进行进一步的初始化。

So copying your .sql file in will work.

所以复制你的 .sql 文件就可以了。

回答by Vlad

By using docker-compose:

通过使用docker-compose：

Assuming that you have following directory layout:

假设您有以下目录布局：

$MYAPP_ROOT/docker-compose.yml
           /Docker/init.sql
           /Docker/db.Dockerfile

File: docker-compose.yml

文件： docker-compose.yml

version: "3.3"
services:
  db:
    build:
      context: ./Docker
      dockerfile: db.Dockerfile
    volumes:
      - ./var/pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"

File: Docker/init.sql

文件： Docker/init.sql

CREATE USER myUser;

CREATE DATABASE myApp_dev;
GRANT ALL PRIVILEGES ON DATABASE myApp_dev TO myUser;

CREATE DATABASE myApp_test;
GRANT ALL PRIVILEGES ON DATABASE myApp_test TO myUser;

File: Docker/db.Dockerfile

文件： Docker/db.Dockerfile

FROM postgres:11.5-alpine
COPY init.sql /docker-entrypoint-initdb.d/

Composing and starting services:

编写和启动服务：

docker-compose -f docker-compose.yml up --no-start
docker-compose -f docker-compose.yml start

回答by Rondo

I add custom commands to a environment evoked in a CMD after starting services... I haven't done it with postgres, but with Oracle:

在启动服务后，我将自定义命令添加到在 CMD 中调用的环境中……我没有用 postgres 完成过，而是用 Oracle 完成的：

#set up var with noop command
RUN export POST_START_CMDS=":"
RUN mkdir /scripts
ADD script.sql /scripts
CMD service oracle-xe start; $POST_START_CMDS; tail -f /var/log/dmesg

and start with

并开始

docker run -d ... -e POST_START_CMDS="su - oracle -c 'sqlplus @/scripts/script' " <image>

.

.

回答by Gabriel

You can use this commands:

您可以使用以下命令：

docker exec -it yournamecontainer psql -U postgres -c "CREATE DATABASE mydatabase ENCODING 'LATIN1' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';"

docker exec -it yournamecontainer psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE postgres TO postgres;"

回答by seanmcl

You need to have the database running before you create the users. For this you need multiple processes. You can either start postgres in a subshell (&) in the shell script, or use a tool like supervisord to run postgres and then run any initialization scripts.

在创建用户之前，您需要运行数据库。为此，您需要多个进程。您可以在 shell 脚本的子 shell (&) 中启动 postgres，也可以使用 supervisord 之类的工具运行 postgres，然后运行任何初始化脚本。

A guide to supervisord and docker https://docs.docker.com/articles/using_supervisord/

supervisord 和 docker 指南https://docs.docker.com/articles/using_supervisord/

回答by danigb

With docker compose there's a simple alternative (no need to create a Dockerfile). Just create a init-database.sh:

使用 docker compose 有一个简单的替代方案（无需创建 Dockerfile）。只需创建一个 init-database.sh：

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER docker;
    CREATE DATABASE my_project_development;
    GRANT ALL PRIVILEGES ON DATABASE my_project_development TO docker;
    CREATE DATABASE my_project_test;
    GRANT ALL PRIVILEGES ON DATABASE my_project_test TO docker;
EOSQL

And reference it in the volumes section:

并在卷部分引用它：

version: '3.4'

services:
  postgres:
    image: postgres
    restart: unless-stopped
    volumes:
      - postgres:/var/lib/postgresql/data
      - ./init-database.sh:/docker-entrypoint-initdb.d/init-database.sh
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 5432:5432

volumes:
  postgres: