postgres: Unable to replace postgresql.conf

FYI, you can inject postgresql.conf file using Docker volume + config_file postgres option:

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

Originally posted in StackOverflow question: http://stackoverflow.com/a/40598124/385548

For anyone finding this issue through a search, I ended up doing this:

FROM postgres:9.3

COPY postgresql.conf /postgresql.conf
COPY set-config.sh /docker-entrypoint-initdb.d/set-config.sh

Where set-config.sh is:

#!/bin/sh

mv /postgresql.conf /var/lib/postgresql/data/postgresql.conf

I do hope that this issue is fixed though. One fix that would be backwards compatible with existing images would be for the docker-entrypoint.sh script to look for a configuration file in a specific place and copy it into the correct place if the PGDATA folder has not been initialized.

@yosifkit I figured out the problem though. I set PGDATA to the same location where I put my custom postgres.conf. So inidb doesn’t like it when it encounter a non-empty folder

  postgres-9.6:
    image: postgres:9.6
    restart: always
    environment:
      PGDATA: /postgresql
      POSTGRES_USER: postgres
    command: postgres -c config_file=/postgresql/postgresql.conf
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - ~/Data/postgres-9.6:/postgresql

Move PGDATA 1 level more deep should work

  postgres-9.6:
    image: postgres:9.6
    restart: always
    environment:
      PGDATA: /postgresql/data
      POSTGRES_USER: postgres
    command: postgres -c config_file=/postgresql/postgresql.conf
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - ~/Data/postgres-9.6:/postgresql

+1 for this… For example I would like to turn on logging (log_statement “all”) for dev purposes by overriding default config… FWIW i’ve been using https://github.com/sameersbn/docker-postgresql instead until this (hopefully) lands

So it does read the .sample file and then apply some mostly LANG/locale based changes. The listen address change is us in the entrypoint.

$ diff -u <(curl https://raw.githubusercontent.com/postgres/postgres/REL9_4_STABLE/src/backend/utils/misc/postgresql.conf.sample)  <(docker exec postgres cat /var/lib/postgresql/data/postgresql.conf)
@@ -56,16 +56,16 @@

 # - Connection Settings -

-#listen_addresses = 'localhost'        # what IP address(es) to listen on;
+listen_addresses = '*'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
 #port = 5432               # (change requires restart)
-#max_connections = 100         # (change requires restart)
+max_connections = 100          # (change requires restart)
 # Note:  Increasing max_connections costs ~400 bytes of shared memory per
 # connection slot, plus lock space (see max_locks_per_transaction).
 #superuser_reserved_connections = 3    # (change requires restart)
-#unix_socket_directories = '/tmp'  # comma-separated list of directories
+#unix_socket_directories = '/var/run/postgresql'   # comma-separated list of directories
                    # (change requires restart)
 #unix_socket_group = ''            # (change requires restart)
 #unix_socket_permissions = 0777        # begin with 0 to use octal notation
@@ -112,7 +112,7 @@

 # - Memory -

-#shared_buffers = 32MB         # min 128kB
+shared_buffers = 128MB         # min 128kB
                    # (change requires restart)
 #huge_pages = try          # on, off, or try
                    # (change requires restart)
@@ -127,7 +127,7 @@
 #maintenance_work_mem = 64MB       # min 1MB
 #autovacuum_work_mem = -1      # min 1MB, or -1 to use maintenance_work_mem
 #max_stack_depth = 2MB         # min 100kB
-#dynamic_shared_memory_type = posix    # the default is the first option
+dynamic_shared_memory_type = posix # the default is the first option
                    # supported by the operating system:
                    #   posix
                    #   sysv
@@ -435,7 +435,7 @@
 #log_temp_files = -1           # log temporary files equal or larger
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
-#log_timezone = 'GMT'
+log_timezone = 'UTC'


 #------------------------------------------------------------------------------
@@ -521,9 +521,9 @@

 # - Locale and Formatting -

-#datestyle = 'iso, mdy'
+datestyle = 'iso, mdy'
 #intervalstyle = 'postgres'
-#timezone = 'GMT'
+timezone = 'UTC'
 #timezone_abbreviations = 'Default'     # Select the set of available time zone
                    # abbreviations.  Currently, there are
                    #   Default
@@ -536,14 +536,14 @@
                    # encoding

 # These settings are initialized by initdb, but they can be changed.
-#lc_messages = 'C'         # locale for system error message
+lc_messages = 'en_US.utf8'         # locale for system error message
                    # strings
-#lc_monetary = 'C'         # locale for monetary formatting
-#lc_numeric = 'C'          # locale for number formatting
-#lc_time = 'C'             # locale for time formatting
+lc_monetary = 'en_US.utf8'         # locale for monetary formatting
+lc_numeric = 'en_US.utf8'          # locale for number formatting
+lc_time = 'en_US.utf8'             # locale for time formatting

 # default configuration for text search
-#default_text_search_config = 'pg_catalog.simple'
+default_text_search_config = 'pg_catalog.english'

 # - Other Defaults -

But that means a user can just mount their config to /usr/share/postgresql/9.4/postgresql.conf.sample and it will be sourced into the real config file on database creation.

An alternative way would be to use a Dockerfile:

FROM postgres:9.4
RUN sed -ri '/..../' "/usr/share/postgresql/$PG_MAJOR/postgresql.conf.sample" \
    && echo 'extra config' >> "/usr/share/postgresql/$PG_MAJOR/postgresql.conf.sample"

That is true. However that produces a greater variance between the supported versions as include_dir is not available for 9.1 or 9.2 which are both still supported.

You could use include_dir in the configuration file you use to supplement the settings.

I guess the real problem is that the postgresql.conf file is in the same folder as the database files.

When installing postgres onto a system using apt-get, the configuration files end up in /etc/postgresql/VERSION/main/ and the database files end up in /var/lib/postgresql/VERSION/main/. If this setup was replicated in this docker image then there would be no problem with directly replacing the configuration file without also manually creating all of the database directories.

If I compare the behaviour of this image to mysql I can see that mysql does not alter itself in this way. MySQL allows you to overwrite the /etc/mysql/my.cnf, or supplement it using a file in /etc/mysql/conf.d. Both of these options make it very easy to do some light customization of the image for a specific use case.

Given that this image behaves differently from the default postgres behaviour I have to ask why this was done. I think that it should be possible to overwrite the postgresql.conf file by merely mounting it into the container without having to write a script which is a glorified mv.