Unable to replace postgresql.conf

[bw-matthew]

Using the dockerfile:

FROM postgres:9.3

COPY postgresql.conf /var/lib/postgresql/data/postgresql.conf

(the content of postgresql.conf doesn't matter - at the moment it is a duplicate of what would be there when the image starts)

I can build the image, but when I run it I get the error:

postgres_1      | initdb: directory "/var/lib/postgresql/data" exists but is not empty
postgres_1      | If you want to create a new database system, either remove or empty
postgres_1      | the directory "/var/lib/postgresql/data" or run initdb
postgres_1      | with an argument other than "/var/lib/postgresql/data".

Why can't I just replace the config file in this way?

[bw-matthew]

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.

[bw-matthew]

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.

[tianon]

Hmm, perhaps it would make sense to add support for something like /docker-entrypoint-initdb.d/postgres.conf, but I'm not exactly clear on whether that should replace outright or append to /var/lib/postgresql/data/postgresql.conf. A lot of times I find myself just wanting to set a few new conf properties, and don't really want to copy all the defaults (since the default file is a bit long).

[tianon]

I think it's also worth noting that we haven't deviated from a standard postgres install in this regard -- it's the Postgres initdb process itself that sets up that config file initially, so maybe there's a template it sources somewhere that we could suggest modifying instead? (I feel like we've had an issue referencing such a file before, but I can't seem to find it now. 😞)

[matthewfranglen]

(This is my home account)

If it could supplement the default file then that would be ideal. Looking quickly at the documentation I can see both individual file includes and directory includes:

http://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-INCLUDES

include 'filename'
include_dir 'directory'

I'll have a little fiddle and see if I can come up with something.

[matthewfranglen]

I'm writing this down in case I'm unable to solve the problem or you have ideas.

In the postgresql source code the conf file is made by initdb as you state. This file is initialized from data read in at this point which comes from a file named here which has this content.

This is where it gets a bit odd though. When I compare the postgresql.conf file that is created in the docker container with the sample one it is clearly different. I am testing this as follows:

docker run -d --name=postgres postgres:9.4
diff <(docker exec postgres cat /var/lib/postgresql/data/postgresql.conf) <(curl https://raw.githubusercontent.com/postgres/postgres/REL9_4_STABLE/src/backend/utils/misc/postgresql.conf.sample)

It shows some 48 lines of difference.

It turns out that the file is altered in the setup_config method. I guess they felt the example wasn't ideal for use in this situation.

At this point I have not seen a way to alter the file through the initdb command options or anything promising in the code. I have only had a brief look. The next thing is to check the entrypoint script to see if I can alter it after initialization.

Actually that looks extremely straightforward.

So 9.5, 9.4 and 9.3 support include_if_exists (as well as include and include_dir). The include_if_exists option looks ideal.

There is no such option in the sample settings file for 9.2 but it is documented.

There is no such option for 9.1 or 9.0. They support the plain include directive only.

[yosifkit]

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"

[matthewfranglen]

Yeah I'm trying to make the change after the call to initdb at the moment. I have a patch I was just testing it.

[alexishuard]

👍 this would be really helpful. Any idea when it will be merged ?

[robertd]

+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

[oppianmatt]

postgres added include_dir in 9.3 which works just like mysql.

https://www.postgresql.org/docs/9.3/static/config-setting.html#CONFIG-INCLUDES

If this was set to /etc/postgresql/conf.d then all you have to do would be to copy just your settings that are different into that dir which could be done in your dockerfile

[matthewfranglen]

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.

[VojtechVitek]

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

[bw-matthew]

Nice stuff @VojtechVitek!

[victorjtfranco]

To extend the default configuration, I appended "include '/postgresql-extend.conf'" to postgresql.conf, and copied postgresql-extend.conf into the container
(Github Gist)

[rkt2spc]

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

run this in daemon mode and it fails

[yosifkit]

@rocketspacer, what is in your config file? Anything useful in docker logs postgres?

[rkt2spc]

@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

[slim-hmidi]

@bw-matthew I tried your solution :

FROM mdillon/postgis:9.6-alpine


COPY ./pldebugger/*  /tmp/pldebugger/
COPY ./postgresql.conf /tmp/postgresql.conf

RUN apk add --update make openssl-dev build-base
RUN cd /tmp/pldebugger \
&& export USE_PGXS=1 \
&& make \
&& make install 

COPY ./scripts/config.sh /docker-entrypoint-initdb.d/config.sh

config.sh:

#!/bin/sh

rm -rf /var/lib/postgresql/data/postgresql.conf
mv /tmp/postgresql.conf /var/lib/postgresql/data/postgresql.conf

then I run these instructions:

docker build repo/postgres-db .
docker run --name postgres-db repo/postgres-db
docker exec -it postgres-db /bin/sh 

Then inside the container postgres-db :

cd /var/lib/postgresql/data
cat postgresql.conf 

The postgresql inside the container was [Postgresql.conf inside the docker container] (https://gist.github.com/slim-hmidi/2dc23850e474f8211ca32b775c6d34d3). However the configuration file in my local machine was Postgresql.conf in the local machine.
I added the extension pldbgapi in pgadmin but when I click on set breakpoint in a trigger function I got this error:

The debugger plugin is not enabled. Please add the plugin to the shared_preload_libraries setting in the postgresql.conf file and restart the database server for indirect debugging.

[indiVar0508]

Hey,
i want to do something similar but in github actions, basically i want to update max_locks_per_transaction value in postgres service, i do have .conf file but i am not sure how i can pass it to postgres or if some direct env-variable can update that my service looks like this

        services:
            postgres:
                image: postgres
                ports:
                    - 5432:5432
                env:
                    POSTGRES_PASSWORD: postgres
                    POSTGRES_DB: my_test
                options: >-
                    --health-cmd pg_isready
                    --health-interval 5s
                    --health-timeout 2s
                    --health-retries 3
            volumes:
                - ${{ github.workspace }}/.github/postgres.conf:/etc/postgresql/postgresql.conf

or where can i directly overwrite the config file from where this is refrenced by mounting at that localtion?

Thanks!

[tianon]

Unfortunately, we do not have the bandwidth to provide in-depth integration/deployment/environment debugging or support here; these sorts of questions/requests would be more appropriately posted to a dedicated support forum, such as the Docker Community Slack, Server Fault, Unix & Linux, or Stack Overflow.