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 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    How should I change the configuration   Edit 1   Here is the Dockerfile used to create the image     Database  http   www cs3c ma    FROM postgres 9 4 MAINTAINER Sabbane  lt contact cs3c ma gt   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   gt  gt   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

User · Answer

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

User · Answer

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

User · Answer

When you run the official entrypoint  A K A  when you launch the container   it runs initdb in  PGDATA   var lib postgresql data by default   and then it stores in that directory these 2 files    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 times 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 already-existing databases  Since configuration is stored in the volume  it will survive rebuilds     Another alternative is to pass -c flag 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

User · Answer

With Docker Compose  When working with Docker Compose  you can use command  postgres -c option value in your docker-compose yml to configure Postgres   For example  this makes Postgres log to a file   command  postgres -c logging collector on -c log destination stderr -c log directory  logs   Adapting Vojtech Vitek s answer  you can use  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   volumes     -   customPostgresql conf  etc postgresql conf     Here s the docker-compose yml of my application  showing how to configure Postgres     Start the app using docker-compose pull  amp  amp  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 amp Hvk3S hqcqebFgo vZi         If on a non-Linux OS  make sure you share the drive used here  Go to Docker s settings - gt  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     FATAL   could not open log file     logs postgresql-2017-02-04 115222 log   Permission denied   I say misleading  since the error message suggests that the directory in the container has the wrong permission  when in reality the directory on the host doesn t permit writing   To fix this  I set the correct permissions on the host using  chgroup   logs docker  amp  amp  chmod 770   logs

User · Answer

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     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    A pleasant side-effect of persisting your configuration is that it also persists your databases  or was it the other way around   -

User · Answer

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 postgres container for the first time  since the docker-entrypoint sh wrapper invokes the initdb step for PGDATA dir initialization   To customize PostgreSQL configuration in Docker consistently  I suggest using 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   Remove the -d  detach option  from docker run command 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

User · Answer

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    Basically we need to   Copy config file in docker container Override postgres start options     Docker file 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 confin your docker-compose yml file proposed by Matthias Braun is the best option

User · Answer

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

User · Answer

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    gt  gt   usr share postgresql postgresql conf sample RUN echo  cron database name  newbie    gt  gt   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

User · Answer

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

User · Answer

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 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   services    postgres                command        -  postgres        -  -c        -  max connections 1000        -  -c        -  shared buffers 3GB        -  -c

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

Examples related to postgresql

Examples related to docker