How to set PGOPTIONS when starting Postgres using docker-compose

Question

I want to start a Postgres database using docker-compose and pass some configurations using the PGOPTIONS environment variable. My docker-compose.yml looks like this:

version: '2'

services:
  db:
    image: postgres
    environment:
     - POSTGRES_USER=dbuser
     - POSTGRES_PASSWORD=dbpw
     - PGOPTIONS='-c track_activities=on -c track_counts=on -c track_io_timing=on'

When I start this service using docker-compose up db I get:

db_1   | FATAL:  parameter "track_io_timing" requires a Boolean value
db_1   |  done
db_1   | server started
db_1   | FATAL:  parameter "track_io_timing" requires a Boolean value
db_1   | psql: FATAL:  parameter "track_io_timing" requires a Boolean value

It does not seem to be related to the tracking_io_timing parameter, because when I remove that one from the PGOPTIONS variable I get the same error, but for the track_counts variable. So I suspect there is something wrong with the way I declared PGOPTIONS.

Answer 1

Same result here when using your provided Docker-Compose file. I have tried to use the alternative syntax for defining environment variables and I do not get the errors anymore:

version: '2'
services:
  db:
    image: postgres
    environment:
      POSTGRES_USER: dbuser
      POSTGRES_PASSWORD: dbpw
      PGOPTIONS: "-c track_activities=on -c track_counts=on -c track_io_timing=on"

Answer 2

As of PostgreSQL 16, you can use the environment variable POSTGRES_INITDB_ARGS to set server parameters that will be installed in postgresql.conf using -c name=value or --set name=value (see docs).

This also works for parameters that would normally require a server restart (e.g. max_prepared_transactions).

version: "3.8"

services:
  postgres-db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: my-database-name
      POSTGRES_USER: postgres
      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_INITDB_ARGS: "-c max_prepared_transactions=100"
    ports:
      - "5432:5432"

psql -h localhost -p 5432 -U postgres -w my-database-name -c "SHOW ALL;" | grep 'max_prepared_transactions'
# max_prepared_transactions | 100 | Sets the maximum number of simultaneously prepared transactions.