How to make PostgresQL use environment variables from .env file after re-creating the container (without removing the volume)

Question

There is a message in Docker PostgresQL docs:

Warning: the Docker specific variables will only have an effect if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup.

So when I recreate my postgres container with docker-compose down and docker-compose up -d --remove-orphans, it throws me errors like:

FATAL: password authentication failed for user "myuser"

It happens because postgres sees that there is the volume, so it skips all scripts and .env files (where I set POSTGRES_USER and POSTGRES_PASSWORD).

How this can be solved? (I mean provide vars from .env file everytime I re-create postgres container)

p.s. It is impossible for me to delete old volume as I have data there.

Here is my docker-compose.yml file:

postgres:
    image: postgres:alpine
    restart: always
    expose:
      - 5432
    volumes:
      - "./init/postgres:/docker-entrypoint-initdb.d"
      - "./data/postgres_data:/var/lib/postgresql/data"
    environment:
      POSTGRES_DB:       ${POSTGRES_DB}
      POSTGRES_USER:     ${POSTGRES_USR}
      POSTGRES_PASSWORD: ${POSTGRES_PWD}

My .env file:

# Postgres
export POSTGRES_USR="someuser"
export POSTGRES_PWD="somepwd"
export POSTGRES_DB="somedb"
export POSTGRES_URL="postgres://${POSTGRES_USR}:${POSTGRES_PWD}@postgres:5432/${POSTGRES_DB}?sslmode=disable"

score 6 · Accepted Answer · answered Dec 18 '19 at 18:54

The POSTGRES_* environment variables are used to define the way that the databases get created and connected. If you have an existing database, you need to define the PG* environment variables so that you can connect to it.

Therefore, you should also include PGPASSWORD in the .env file, so that the password that you have defined for the existing cluster will get added to the environment:

$ cat .env
...
PGPASSWORD="somepwd"

Otherwise, you can include a .pgpass file in /home/postgres

BTW, the .env file should just be a list of key-value pairs. You don't need to export them:

$ cat .env
POSTGRES_USER="someuser"
POSTGRES_PASSWORD="somepwd"
POSTGRES_DB="somedb"
POSTGRES_URL="postgres://${POSTGRES_USR}:${POSTGRES_PWD}@postgres:5432/${POSTGRES_DB}?sslmode=disable"

score 1 · Answer 2 · answered Dec 30 '20 at 08:36

in my problem, I remove " from the Postgres config in .env file , this solved my issues

POSTGRES_USR=someuser
POSTGRES_PWD=somepwd
POSTGRES_DB=somedb

and in docker-compose file

environment:
  POSTGRES_DB:       ${POSTGRES_DB}
  POSTGRES_USER:     ${POSTGRES_USR}
  POSTGRES_PASSWORD: ${POSTGRES_PWD}

How to make PostgresQL use environment variables from .env file after re-creating the container (without removing the volume)

2 Answers2