How to customize the configuration file of the official PostgreSQL Docker image?

Question

I'm using the official Postgres Docker image, trying to customize its configuration. For this purpose, I use the command sed to change max_connections for example:

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 the commands directly within my Dockerfile with the "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).

How should I change the configuration?

Here is the Dockerfile used to create the image:

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

FROM postgres:9.4
MAINTAINER Sabbane <contact@cs3c.ma>

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 produces an error:

sed: can't read /var/lib/postgresql/data/postgresql.conf: No such file or directory

Answer 1

TLDR a step by step guide:

1. start the postgres container

docker run -d \
  --name postgresql-foo \
  -e POSTGRES_PASSWORD=postgres \
  --publish "${POSTGRES_DB_PORT:-5432}:5432" \
  --volume postgres_volume:/var/lib/postgresql/data \
  postgres:15 \
  postgres -c 'max_connections=1000'

2. copy the postgresql.conf file to the host:

docker cp postgresql-foo:/var/lib/postgresql/data/postgresql.conf ./postgresql.conf

3. edit the postgresql.conf file to set the pg_stat_statements
4. copy the postgresql.conf file back to the container:

docker cp ./postgresql.conf postgresql-foo:/var/lib/postgresql/data/postgresql.conf

5. fix the file permissions if your container does not run as root:

docker exec -u 0 -it postgresql-foo chown postgres:postgres /var/lib/postgresql/data/postgresql.conf

6. reload the configuration:

docker exec -it postgresql-foo psql -U postgres -c "SELECT pg_reload_conf();"

7. sometimes reloading the configuration is not enough, so restart the container:

docker restart postgresql-foo

8. connect to the database and create the extension:

-- check if the extension is available:
SELECT * 
FROM pg_available_extensions 
WHERE 
    name = 'pg_stat_statements' and 
    installed_version is not null;

-- if the extension is not available, run the following command:
CREATE EXTENSION pg_stat_statements;

-- now you can query the pg_stat_statements view:
SELECT * FROM pg_stat_statements;

-- to reset the statistics:
select pg_stat_statements_reset();

Answer 2

With Docker Compose

When working with Docker Compose, you can use command: postgres -c option=value in your docker-compose.yml to configure Postgres.

Adapting Vojtech Vitek's answer, you can use

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

to change the config file Postgres will use.

As per the comment by johnthagen, the command can be shortened to

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

You'd mount your custom config file with a volume:

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

---

Here's the docker-compose.yml of a demo application, showing how to configure Postgres:

services:
  db:
    image: postgres:16.2
    command: -c config_file=/etc/postgresql.conf
    environment:
      POSTGRES_USER: postgres
      # Provide the password via an environment variable. If the variable is unset or empty, use a default password
      # Explanation of this shell feature: https://unix.stackexchange.com/questions/122845/using-a-b-for-variable-assignment-in-scripts/122848#122848
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-4WXUms893U6j4GE&Hvk3S*hqcqebFgo!vZi}
      POSTGRES_DB: test_db
    # Optionally, expose the database port to the host. Not necessary for communication between the app and the database
    ports:
      - "5432:5432"
    volumes:
      - ./customPostgresql.conf:/etc/postgresql.conf
      # Add the database files to the host
      - ./postgres_data:/var/lib/postgresql/data
      # The directory "./logs" is created by run.sh on the host. Postgres is configured via customPostgresql.conf to write log messages to "/logs"
      - ./logs:/logs
      # The container should use the user and group IDs from the host. When we set the owner of /logs to the user "postgres" in the host (via run.sh), the ID of the container's user "postgres" will match.
      # From https://stackoverflow.com/questions/23544282/what-is-the-best-way-to-manage-permissions-for-docker-shared-volumes#45640469
      - /etc/passwd:/etc/passwd:ro
      - /etc/group:/etc/group:ro
    networks:
      myApp-network:
        # Our application can communicate with the database using this hostname
        aliases:
          - myPostgres
  my_app:
    networks:
      - myApp-network
    build: .
    depends_on:
      - db

networks:
  myApp-network:

---

The example custom Postgres config makes Postgres log to a directory on the host. Mind that the directory on the host needs to be owned by user "postgres", the script run.sh takes care of that.

Answer 3

One more answer, for completeness — manually edit the file!

Things you're already doing at this point:

1. You're mounting a host directory into the container at runtime
2. You've initialized the database

You must be mounting the data directory as a volume, otherwise the database would be ephemeral, which ruins the point of running a database. Since you're already mounting it, the file already exists on the host machine (e.g. your macos laptop), so just edit it.

# you're mounting this into the container anyway, just edit it
vim data/pg_hba.conf

# Mount a volume and run, like you're already doing
docker run -v ./data:/var/lib/postgresql/data image cmd

This works well for pg_hba.conf, where you often need to append a line but don't want that line repeated every time the server starts (which is what might happen if you use the /docker-entrypoint-initdb.d/00-init.sh approach.

The upsides of doing it this way:

1. Simple: No need to think about the lifecycle of files & volumes. Your edited copy will overwrite any other changes that would've been made elsewhere.
2. Configure every environment differently

The downsides:

1. No automation
2. It breaks infrastructure-as-code paradigm

On the other hand, you can mitigate these downsides fairly easily too.

Answer 4

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

Basically we need to:

• Copy the config file into the Docker container
• Override Postgres start options

---

Dockerfile example:

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.conf in your docker-compose.yml file as proposed by Matthias Braun is the best option.

Answer 5

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

To do that:

• Copy your custom postgresql.conf inside your container
• Copy the updateConfig.sh file in /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 your custom one.

Answer 6

When you run the official entrypoint (i.e, when you launch the container), it runs initdb in $PGDATA (/var/lib/postgresql/data by default), and then it stores two files in that directory:

• postgresql.conf with default manual settings.
• postgresql.auto.conf with settings overriden automatically with ALTER SYSTEM commands.

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

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 time you boot the container).

Example:

conf.sql file:

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

Dockerfile file:

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.sql manually in the already existing databases. Since configuration is stored in the volume, it will survive rebuilds.

---

An alternative is to pass the -c option as many times as you wish:

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 7

The postgres:9.4 image 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.

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

Like this:

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

Answer 8

Inject custom postgresql.conf into Postgres Docker container

The default postgresql.conf file lives within the PGDATA dir (/var/lib/postgresql/data), which makes things more complicated especially when running the Postgres container for the first time, since the docker-entrypoint.sh wrapper invokes the initdb step for PGDATA dir initialization.

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

Production database (PGDATA dir as Persistent Volume)

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)

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

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

2. Connect to the Postgres server with the 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;
   

Answer 9

I was also using the official image (FROM postgres) and I was able to change the config by executing the following commands.

The first thing is to locate the PostgreSQL config file. This can be done by executing this command in your running database.

SHOW config_file;

I my case it returns /data/postgres/postgresql.conf.

The next step is to find out what is the hash of your running PostgreSQL docker container.

docker ps -a

This should return a list of all the running containers. In my case it looks like this.

...
0ba35e5427d9    postgres    "docker-entrypoint.s…" ....
...

Now you have to switch to the bash inside your container by executing:

docker exec -it 0ba35e5427d9 /bin/bash

Inside the container check if the config is at the correct path and display it.

cat /data/postgres/postgresql.conf

I wanted to change the max connections from 100 to 1000 and the shared buffer from 128MB to 3GB. With the sed command I can do a search and replace with the corresponding variables ins the config.

sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /data/postgres/postgresql.conf
sed -i -e"s/^shared_buffers = 128MB.*$/shared_buffers = 3GB/" /data/postgres/postgresql.conf

The last thing we have to do is to restart the database within the container. Find out which version you of PostGres you are using.

cd /usr/lib/postgresql/
ls 

In my case its 12 So you can now restart the database by executing the following command with the correct version in place.

su - postgres -c "PGDATA=$PGDATA /usr/lib/postgresql/12/bin/pg_ctl -w restart"

Answer 10

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

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 11

My solution is for colleagues who needs to make changes in config before launching 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.

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

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/:

CREATE EXTENSION pg_cron;

Answer 12

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).

  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"

1. The db comes up as initialized with the image default settings.
2. 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
3. stop the running container (or scale the service down to zero and then back to one)
4. the swarm spawns a new container, which this time around picks up your persisted configuration settings and merrily applies them.

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