postgresql 如何自定义PostgreSQL Docker 官方镜像的配置文件？

Question

提问by Sakr

I'm using the official Postgres Docker imagetrying to customize its configuration. For this purpose, I use the command sedto change max_connectionsfor example:

我正在使用官方 Postgres Docker 映像尝试自定义其配置。为此，我使用命令sed进行更改max_connections，例如：

sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf

I tried two methods to apply this configuration. The first is by adding the commands to a script and copying it within the init folder "/docker-entrypoint-initdb.d". The second method is by running them directly within my Dockerfile with "RUN" command (this method worked fine with a non-official Postgresql image with a different path to the configuration file "/etc/postgres/..."). In both cases the changes fail because the configuration file is missing (I think it's not created yet).

我尝试了两种方法来应用此配置。第一种是将命令添加到脚本并将其复制到 init 文件夹“/docker-entrypoint-initdb.d”中。第二种方法是使用“RUN”命令直接在我的 Dockerfile 中运行它们（此方法适用于非官方 Postgresql 映像，该映像具有不同的配置文件“/etc/postgres/...”路径）。在这两种情况下，更改都失败了，因为缺少配置文件（我认为它还没有创建）。

How should I change the configuration?

我应该如何更改配置？

Edit 1:

编辑1：

Here is the Dockerfile used to create the image:

这是用于创建映像的 Dockerfile：

# Database (http://www.cs3c.ma/)

FROM postgres:9.4
MAINTAINER Sabbane <[email protected]>

ENV TERM=xterm

RUN apt-get update
RUN apt-get install -y nano

ADD scripts /scripts
# ADD scripts/setup-my-schema.sh /docker-entrypoint-initdb.d/

# Allow connections from anywhere.
RUN sed -i -e"s/^#listen_addresses =.*$/listen_addresses = '*'/" /var/lib/postgresql/data/postgresql.conf
RUN echo "host    all    all    0.0.0.0/0    md5" >> /var/lib/postgresql/data/pg_hba.conf

# Configure logs
RUN sed -i -e"s/^#logging_collector = off.*$/logging_collector = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_directory = 'pg_log'.*$/log_directory = '\/var\/log\/postgresql'/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_filename = 'postgresql-\%Y-\%m-\%d_\%H\%M\%S.log'.*$/log_filename = 'postgresql_\%a.log'/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_file_mode = 0600.*$/log_file_mode = 0644/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_truncate_on_rotation = off.*$/log_truncate_on_rotation = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_rotation_age = 1d.*$/log_rotation_age = 1d/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_min_duration_statement = -1.*$/log_min_duration_statement = 0/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_checkpoints = off.*$/log_checkpoints = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_connections = off.*$/log_connections = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_disconnections = off.*$/log_disconnections = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^log_line_prefix = '\%t \[\%p-\%l\] \%q\%u@\%d '.*$/log_line_prefix = '\%t \[\%p\]: \[\%l-1\] user=\%u,db=\%d'/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_lock_waits = off.*$/log_lock_waits = on/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#log_temp_files = -1.*$/log_temp_files = 0/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#statement_timeout = 0.*$/statement_timeout = 1800000        # in milliseconds, 0 is disabled (current 30min)/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^lc_messages = 'en_US.UTF-8'.*$/lc_messages = 'C'/" /var/lib/postgresql/data/postgresql.conf

# Performance Tuning
RUN sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^shared_buffers =.*$/shared_buffers = 16GB/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#effective_cache_size = 128MB.*$/effective_cache_size = 48GB/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#work_mem = 1MB.*$/work_mem = 16MB/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#maintenance_work_mem = 16MB.*$/maintenance_work_mem = 2GB/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#checkpoint_segments = .*$/checkpoint_segments = 32/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#checkpoint_completion_target = 0.5.*$/checkpoint_completion_target = 0.7/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#wal_buffers =.*$/wal_buffers = 16MB/" /var/lib/postgresql/data/postgresql.conf
RUN sed -i -e"s/^#default_statistics_target = 100.*$/default_statistics_target = 100/" /var/lib/postgresql/data/postgresql.conf


VOLUME ["/var/lib/postgresql/data", "/var/log/postgresql"]

CMD ["postgres"]

With this Dockerfile the build process shows the error: sed: can't read /var/lib/postgresql/data/postgresql.conf: No such file or directory

使用此 Dockerfile，构建过程会显示错误： sed: can't read /var/lib/postgresql/data/postgresql.conf: No such file or directory

Answer 1

采纳答案by Adrian Mouat

The postgres:9.4image you've inherited from declares a volume at /var/lib/postgresql/data. This essentially means you can't copy any files to that path in your image; the changes will be discarded.

postgres:9.4您继承的图像在处声明了一个卷/var/lib/postgresql/data。这实质上意味着您无法将任何文件复制到图像中的该路径；更改将被丢弃。

You have a few choices:

你有几个选择：

You could just add your own configuration files as a volume at run-time with docker run -v postgresql.conf:/var/lib/postgresql/data/postgresql.conf .... However, I'm not sure exactly how that will interact with the existing volume.
You could copy the file over when the container is started. To do that, copy your file into the build at a location which isn't underneath the volume then call a script from the entrypoint or cmd which will copy the file to correct location and start postgres.
Clone the project behind the Postgres official image and edit the Dockerfile to add your own config file in before the VOLUME is declared (anything added before the VOLUME instruction is automatically copied in at run-time).
Pass all config changes in command option in docker-compose file

您可以在运行时使用docker run -v postgresql.conf:/var/lib/postgresql/data/postgresql.conf .... 但是，我不确定这将如何与现有卷进行交互。
您可以在容器启动时复制文件。为此，请将您的文件复制到不在卷下方的位置的构建中，然后从入口点或 cmd 调用脚本，该脚本会将文件复制到正确的位置并启动 postgres。
在声明 VOLUME 之前克隆 Postgres 官方镜像后面的项目并编辑 Dockerfile 以添加您自己的配置文件（在 VOLUME 指令之前添加的任何内容都会在运行时自动复制）。
在 docker-compose 文件中的命令选项中传递所有配置更改

like:

喜欢：

services:
  postgres:
    ...  
    command:
      - "postgres"
      - "-c"
      - "max_connections=1000"
      - "-c"
      - "shared_buffers=3GB"
      - "-c"
      ...

Answer 2

回答by Matthias Braun

With Docker Compose

使用 Docker Compose

When working with Docker Compose, you can use command: postgres -c option=valuein your docker-compose.ymlto configure Postgres.

使用 Docker Compose 时，您可以使用command: postgres -c option=value它docker-compose.yml来配置 Postgres。

For example, this makes Postgres log to a file:

例如，这使 Postgres 登录到一个文件：

command: postgres -c logging_collector=on -c log_destination=stderr -c log_directory=/logs

Adapting Vojtech Vitek's answer, you can use

改编Vojtech Vitek 的答案，您可以使用

command: postgres -c config_file=/etc/postgresql.conf

to change the config file Postgres will use. You'd mount your custom config file with a volume:

更改 Postgres 将使用的配置文件。您将使用卷挂载自定义配置文件：

volumes:
   - ./customPostgresql.conf:/etc/postgresql.conf

Here's the docker-compose.ymlof my application, showing how to configure Postgres:

这docker-compose.yml是我的应用程序的，展示了如何配置 Postgres：

# Start the app using docker-compose pull && docker-compose up to make sure you have the latest image
version: '2.1'
services:
  myApp:
    image: registry.gitlab.com/bullbytes/myApp:latest
    networks:
      - myApp-network
  db:
     image: postgres:9.6.1
     # Make Postgres log to a file.
     # More on logging with Postgres: https://www.postgresql.org/docs/current/static/runtime-config-logging.html
     command: postgres -c logging_collector=on -c log_destination=stderr -c log_directory=/logs
     environment:
       # Provide the password via an environment variable. If the variable is unset or empty, use a default password
       - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-4WXUms893U6j4GE&Hvk3S*hqcqebFgo!vZi}
     # If on a non-Linux OS, make sure you share the drive used here. Go to Docker's settings -> Shared Drives
     volumes:
       # Persist the data between container invocations
       - postgresVolume:/var/lib/postgresql/data
       - ./logs:/logs
     networks:
       myApp-network:
         # Our application can communicate with the database using this hostname
         aliases:
           - postgresForMyApp
networks:
  myApp-network:
    driver: bridge
# Creates a named volume to persist our data. When on a non-Linux OS, the volume's data will be in the Docker VM
# (e.g., MobyLinuxVM) in /var/lib/docker/volumes/
volumes:
  postgresVolume:

Permission to write to the log directory

写入日志目录的权限

Note that when on Linux, the log directory on the host must have the right permissions. Otherwise you'll get the slightly misleading error

请注意，在 Linux 上时，主机上的日志目录必须具有正确的权限。否则你会得到稍微误导性的错误

FATAL: could not open log file "/logs/postgresql-2017-02-04_115222.log": Permission denied

致命：无法打开日志文件“/logs/postgresql-2017-02-04_115222.log”：权限被拒绝

I say misleading, since the error message suggests that the directory in the containerhas the wrong permission, when in reality the directory on the hostdoesn't permit writing.

我说误导，因为错误消息表明容器中的目录具有错误的权限，而实际上主机上的目录不允许写入。

To fix this, I set the correct permissions on the host using

为了解决这个问题，我在主机上设置了正确的权限

chgroup ./logs docker && chmod 770 ./logs

Answer 3

回答by Vojtech Vitek

Inject custom postgresql.conf into postgres Docker container

将自定义 postgresql.conf 注入 postgres Docker 容器

The default postgresql.conffile lives within the PGDATAdir (/var/lib/postgresql/data), which makes things more complicated especially when running postgres container for the first time, since the docker-entrypoint.shwrapper invokes the initdbstep for PGDATAdir initialization.

默认postgresql.conf文件位于PGDATAdir ( /var/lib/postgresql/data) 中，这使事情变得更加复杂，尤其是在第一次运行 postgres 容器时，因为docker-entrypoint.sh包装器会调用dir 初始化initdb步骤PGDATA。

To customize PostgreSQL configuration in Docker consistently, I suggest using config_filepostgres option together with Docker volumes like this:

为了在 Docker 中一致地自定义 PostgreSQL 配置，我建议将config_filepostgres 选项与 Docker 卷一起使用，如下所示：

Production database (PGDATA dir as Persistent Volume)

生产数据库（PGDATA 目录作为持久卷）

docker run -d \
-v $CUSTOM_CONFIG:/etc/postgresql.conf \
-v $CUSTOM_DATADIR:/var/lib/postgresql/data \
-e POSTGRES_USER=postgres \
-p 5432:5432 \
--name postgres \
postgres:9.6 postgres -c config_file=/etc/postgresql.conf

Testing database (PGDATA dir will be discarded after `docker rm`)

测试数据库（PGDATA 目录之后将被丢弃`docker rm`）

docker run -d \
-v $CUSTOM_CONFIG:/etc/postgresql.conf \
-e POSTGRES_USER=postgres \
--name postgres \
postgres:9.6 postgres -c config_file=/etc/postgresql.conf

Debugging

调试

Remove the -d(detach option) from docker runcommand to see the server logs directly.

Connect to the postgres server with psql client and query the configuration:

docker run -it --rm --link postgres:postgres postgres:9.6 sh -c 'exec psql -h $POSTGRES_PORT_5432_TCP_ADDR -p $POSTGRES_PORT_5432_TCP_PORT -U postgres'

psql (9.6.0)
Type "help" for help.

postgres=# SHOW all;

-d从docker run命令中删除（分离选项）以直接查看服务器日志。

用psql客户端连接postgres服务器，查询配置：

docker run -it --rm --link postgres:postgres postgres:9.6 sh -c 'exec psql -h $POSTGRES_PORT_5432_TCP_ADDR -p $POSTGRES_PORT_5432_TCP_PORT -U postgres'

psql (9.6.0)
Type "help" for help.

postgres=# SHOW all;

Answer 4

回答by Yajo

When you run the official entrypoint (A.K.A. when you launch the container), it runs initdbin $PGDATA(/var/lib/postgresql/databy default), and then it stores in that directory these 2 files:

当您运行官方入口点（启动容器时也称为）时，它会initdb在$PGDATA（/var/lib/postgresql/data默认情况下）运行，然后将这两个文件存储在该目录中：

postgresql.confwith default manual settings.
postgresql.auto.confwith settings overriden automatically with ALTER SYSTEMcommands.

postgresql.conf使用默认手动设置。
postgresql.auto.conf使用ALTER SYSTEM命令自动覆盖设置。

The entrypoint also executes any /docker-entrypoint-initdb.d/*.{sh,sql}files.

入口点还执行任何/docker-entrypoint-initdb.d/*.{sh,sql}文件。

All this means you can supply a shell/SQL script in that folder that configures the server for the next boot (which will be immediately after the DB initialization, or the next times you boot the container).

所有这一切意味着您可以在该文件夹中提供一个 shell/SQL 脚本，用于为下次启动配置服务器（这将在数据库初始化之后立即进行，或者下次启动容器时）。

Example:

例子：

conf.sqlfile:

conf.sql文件：

ALTER SYSTEM SET max_connections = 6;
ALTER SYSTEM RESET shared_buffers;

Dockerfilefile:

Dockerfile文件：

FROM posgres:9.6-alpine
COPY *.sql /docker-entrypoint-initdb.d/
RUN chmod a+r /docker-entrypoint-initdb.d/*

And then you will have to execute conf.sqlmanually in already-existing databases. Since configuration is stored in the volume, it will survive rebuilds.

然后您将不得不conf.sql在已经存在的数据库中手动执行。由于配置存储在卷中，因此它将在重建后继续存在。

Another alternative is to pass -cflag as many times as you wish:

另一种选择是-c根据需要多次传递标志：

docker container run -d postgres -c max_connections=6 -c log_lock_waits=on

This way you don't need to build a new image, and you don't need to care about already-existing or not databases; all will be affected.

这样你就不需要构建一个新的镜像，你也不需要关心已经存在或不存在的数据库；都会受到影响。

Answer 5

回答by alphayax

You can put your custom postgresql.confin a temporary file inside the container, and overwrite the default configuration at runtime.

您可以将自定义postgresql.conf放在容器内的临时文件中，并在运行时覆盖默认配置。

To do that :

要做到这一点：

Copy your custom postgresql.confinside your container
Copy the updateConfig.shfile in /docker-entrypoint-initdb.d/

postgresql.conf在容器内复制您的自定义
将updateConfig.sh文件复制到/docker-entrypoint-initdb.d/

`Dockerfile`

FROM postgres:9.6

COPY postgresql.conf      /tmp/postgresql.conf
COPY updateConfig.sh      /docker-entrypoint-initdb.d/_updateConfig.sh

`updateConfig.sh`

#!/usr/bin/env bash

cat /tmp/postgresql.conf > /var/lib/postgresql/data/postgresql.conf

At runtime, the container will execute the script inside /docker-entrypoint-initdb.d/and overwrite the default configuration with yout custom one.

在运行时，容器将在内部执行脚本/docker-entrypoint-initdb.d/并用您自定义的配置覆盖默认配置。

Answer 6

回答by Dr. Botwing

I looked through all answers and there is another option left. You can change your CMD value in docker file (it is not the best one, but still possible way to achieve your goal).

我浏览了所有答案，还有另一个选择。您可以在 docker 文件中更改 CMD 值（这不是最好的方法，但仍然可以实现您的目标）。

Basically we need to

基本上我们需要

Copy config file in docker container
Override postgres start options

在docker容器中复制配置文件
覆盖 postgres 启动选项

Docker file example:

Docker 文件示例：

FROM postgres:9.6
USER postgres

# Copy postgres config file into container
COPY postgresql.conf /etc/postgresql

# Override default postgres config file
CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

Though I think using command: postgres -c config_file=/etc/postgresql/postgresql.confin your docker-compose.ymlfile proposed by Matthias Braunis the best option.

虽然我认为command: postgres -c config_file=/etc/postgresql/postgresql.conf在您的docker-compose.yml文件中使用由Matthias Braun提出是最好的选择。

Answer 7

回答by Eoan

A fairly low-tech solution to this problem seems to be to declare the service (I'm using swarm on AWS and a yaml file) with your database files mounted to a persisted volume (here AWS EFS as denoted by the cloudstor:aws driver specification).

这个问题的一个相当低技术的解决方案似乎是声明服务（我在 AWS 上使用 swarm 和一个 yaml 文件），并将您的数据库文件安装到持久卷（这里是 AWS EFS，由 cloudtor:aws 驱动程序表示）规格）。

  version: '3.3'
  services:
    database:
      image: postgres:latest
      volumes:
        - postgresql:/var/lib/postgresql
        - postgresql_data:/var/lib/postgresql/data
    volumes:
       postgresql:
         driver: "cloudstor:aws" 
       postgresql_data:
         driver: "cloudstor:aws"

The db comes up as initialized with the image default settings.
You edit the conf settings inside the container, e.g if you want to increase the maximum number of concurrent connections that requires a restart
stop the running container (or scale the service down to zero and then back to one)
the swarm spawns a new container, which this time around picks up your persisted configuration settings and merrily applies them.

数据库以图像默认设置初始化。
您编辑容器内的 conf 设置，例如，如果您想增加需要重新启动的最大并发连接数
停止正在运行的容器（或将服务缩小到零，然后再缩小到一）
swarm 产生一个新的容器，这一次它会选择你的持久化配置设置并愉快地应用它们。

A pleasant side-effect of persisting your configuration is that it also persists your databases (or was it the other way around) ;-)

持久化配置的一个令人愉快的副作用是它也持久化了你的数据库（或者相反）;-)

Answer 8

回答by 40min

My solution is for colleagues who needs to make changes in config before launching docker-entrypoint-initdb.d

我的解决方案适用于需要在启动 docker-entrypoint-initdb.d 之前更改配置的同事

I was needed to change 'shared_preload_libraries' setting so during it's work postgres already has new library preloaded and code in docker-entrypoint-initdb.d can use it.

我需要更改“shared_preload_libraries”设置，因此在工作期间 postgres 已经预加载了新库，并且 docker-entrypoint-initdb.d 中的代码可以使用它。

So I just patched postgresql.conf.sample file in Dockerfile:

所以我只是在 Dockerfile 中修补了 postgresql.conf.sample 文件：

RUN echo "shared_preload_libraries='citus,pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample
RUN echo "cron.database_name='newbie'" >> /usr/share/postgresql/postgresql.conf.sample

And with this patch it become possible to add extension in .sql file in docker-entrypoint-initdb.d/:

有了这个补丁，就可以在 docker-entrypoint-initdb.d/ 中的 .sql 文件中添加扩展名：

CREATE EXTENSION pg_cron;

Answer 9

回答by Bald

Using docker compose you can mount a volume with postgresql.auto.conf. Example:

使用 docker compose 您可以使用postgresql.auto.conf. 例子：

version: '2'

services:
  db:
    image: postgres:10.9-alpine
    volumes:
      - postgres:/var/lib/postgresql/data:z
      - ./docker/postgres/postgresql.auto.conf:/var/lib/postgresql/data/postgresql.auto.conf
    ports:
      - 5432:5432

Answer 10

回答by Guillermo Solis

This works for me:

这对我有用：

FROM postgres:9.6
USER postgres

# Copy postgres config file into container
COPY postgresql.conf /etc/postgresql

# Override default postgres config file
CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

postgresql 如何自定义PostgreSQL Docker 官方镜像的配置文件？

提问by Sakr

采纳答案by Adrian Mouat

回答by Matthias Braun

With Docker Compose

使用 Docker Compose

Permission to write to the log directory

写入日志目录的权限

回答by Vojtech Vitek

Inject custom postgresql.conf into postgres Docker container

将自定义 postgresql.conf 注入 postgres Docker 容器

Production database (PGDATA dir as Persistent Volume)

生产数据库（PGDATA 目录作为持久卷）

Testing database (PGDATA dir will be discarded after `docker rm`)

测试数据库（PGDATA 目录之后将被丢弃`docker rm`）

Debugging

调试

回答by Yajo

回答by alphayax

`Dockerfile`

`Dockerfile`

`updateConfig.sh`

`updateConfig.sh`

回答by Dr. Botwing

回答by Eoan

回答by 40min

回答by Bald

回答by Guillermo Solis

相关推荐

最近更新

标签

postgresql 如何自定义PostgreSQL Docker 官方镜像的配置文件？

提问by Sakr

采纳答案by Adrian Mouat

回答by Matthias Braun

With Docker Compose

使用 Docker Compose

Permission to write to the log directory

写入日志目录的权限

回答by Vojtech Vitek

Inject custom postgresql.conf into postgres Docker container

将自定义 postgresql.conf 注入 postgres Docker 容器

Production database (PGDATA dir as Persistent Volume)

生产数据库（PGDATA 目录作为持久卷）

Testing database (PGDATA dir will be discarded after docker rm)

测试数据库（PGDATA 目录之后将被丢弃docker rm）

Debugging

调试

回答by Yajo

回答by alphayax

Dockerfile

Dockerfile

updateConfig.sh

updateConfig.sh

回答by Dr. Botwing

回答by Eoan

回答by 40min

回答by Bald

回答by Guillermo Solis

相关推荐

postgresql 当端口 5432 被阻塞时，来自远程服务器的 pg_dump postgres 数据库

Windows TraceRt 报告的数字是什么意思

订阅到期后，MSDN Windows 许可证是否仍然有效？

postgresql 真空分析模式 postgres 中的所有表

相关推荐

最近更新

标签

Testing database (PGDATA dir will be discarded after `docker rm`)

测试数据库（PGDATA 目录之后将被丢弃`docker rm`）

`Dockerfile`

`Dockerfile`

`updateConfig.sh`

`updateConfig.sh`