FATAL: database does not exist [docker compose]

[sztwiorok]

We have docker compose.yml

version: '2'
services:
 postgres:
 image: "library/postgres:latest"
 environment:
 - POSTGRES_USER=test
 - POSTGRES_PASSWORD=qwerty
 - POSTGRES_DB=mydb

execute:

docker-compose up
docker exec -it <CONTAINER_ID> /bin/bash
psql -U test

and we get this error:

psql: FATAL: database "test" does not exist

Everything works fine witout compose

docker run -it -e POSTGRES_USER=test -e POSTGRES_PASSWORD=qwerty -e POSTGRES_DATABSE=mydb postgres
docker exec -it <CONTAINER_ID> /bin/bash
psql -U test

results:

root@cb18469bc86e:/# psql -U test
psql (10.1)
Type "help" for help.

test=# 

[yosifkit]

POSTGRES_DB
This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

POSTGRES_DATABSE (from your docker run line) is not used.

[sztwiorok]

thanks a lot! my bad :)

[z4forzubair]

I could not get what @yosifkit has said. Can anyone clarify?

[tianon]

The name of the supported environment variable is POSTGRES_DB, not POSTGRES_DATABSE.

[101Amine]

Even after adding POSTGRES_DB still getting the same error, when i put a default DB it works but a newly generated one doesn't work, anyone got any idea?

Does anyone know to sync it?

thats my configuration :

services:
  postgres:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: klaus
      POSTGRES_PASSWORD: password
      POSTGRES_DB: customer
      PGDATA: /data/postgres
    volumes:
      - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

[MilanObrenovic]

POSTGRES_DB

I just fixed it,

Even after adding POSTGRES_DB still getting the same error, when i put a default DB it works but a newly generated one doesn't work, anyone got any idea?

Does anyone know to sync it?

thats my configuration :

services:
  postgres:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: klaus
      POSTGRES_PASSWORD: password
      POSTGRES_DB: customer
      PGDATA: /data/postgres
    volumes:
      - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

i just fixed it. you dont need to add POSTGRES_DB: customer. make sure to shut down all instances running from localhost postgres if you're on a mac. because theres a conflict with docker's and localhost's db.

[bomenderick]

@MilanObrenovic thanks for your attention in spotting the conflict on Localhost. I'm on Mac and that was actually my case, could not figure out why i couldn't connect to a database i connected some hours ago.

[iagarcia]

Hello, I'd like to share mi solution for deploy postgres with docker compose.

EXPERIMENT

1. docker-compose.yml

• the image is postgres
• the environment variables keep as default user postgres, this avoid some problems
• the volumes are:
  • schema.sql where it defines a username with password and permission over a database
  • postgres.conf it is the default config file for postgres service, here you can set the data_directory, the listen_address or port
  • database volume to store the data_directory, this case use default option /var/lib/postgresql/data
• a command to indicate configuration file location
• restart option according to your needs
• let's expose the port 5432 of the container, this means that only other containers/services can connect to this container via port 5432. If you want to interact with your network interface use ports instead
• at least let's add a healthcheck, this let you configure that a container start after the postgres container has healthy status, in 10 seconds periods the container execute the command pd_isready -h db -p 5432 -d database_name -U username.

Remember that inside docker, the container hostname is that we define in the service section, so in this example the database service it is called db.

services:
  db:
    image: postgres
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql
      - ./postgres.conf:/etc/postgresql/postgresql.conf
      - database:/var/lib/postgresql/data
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    restart: always
    expose:
      - 5432
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "db", "-p", "5432", "-d", "database_name", "-U", "username"]
      interval: 10s

volumes:
  database:

2. schema.sql
   Let's suppose that we want to create a database called database_name owned by a user called username with password secret_password. When the container starts it will try to execute this SQL script. It is important that the declared match with the healthcheck

DROP DATABASE IF EXISTS database_name;
CREATE USER username WITH SUPERUSER PASSWORD 'secret_password';
CREATE DATABASE database_name OWNER username;

3. postgres.conf
   https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/misc/postgresql.conf.sample

RESULTS

1. Run container
   After running docker compose up you will get:

$ docker compose up
[+] Running 2/0
 ✔ Volume "docker_database"  Created                                                                                                                                              0.0s 
 ✔ Container docker-db-1     Created                                                                                                                                              0.0s 
Attaching to docker-db-1
docker-db-1  | The files belonging to this database system will be owned by user "postgres".
docker-db-1  | This user must also own the server process.
docker-db-1  | 
docker-db-1  | The database cluster will be initialized with locale "en_US.utf8".
docker-db-1  | The default database encoding has accordingly been set to "UTF8".
docker-db-1  | The default text search configuration will be set to "english".
docker-db-1  | 
docker-db-1  | Data page checksums are disabled.
docker-db-1  | 
docker-db-1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
docker-db-1  | creating subdirectories ... ok
docker-db-1  | selecting dynamic shared memory implementation ... posix
docker-db-1  | selecting default max_connections ... 100
docker-db-1  | selecting default shared_buffers ... 128MB
docker-db-1  | selecting default time zone ... Etc/UTC
docker-db-1  | creating configuration files ... ok
docker-db-1  | running bootstrap script ... ok
docker-db-1  | performing post-bootstrap initialization ... ok
docker-db-1  | syncing data to disk ... ok
docker-db-1  | 
docker-db-1  | 
docker-db-1  | Success. You can now start the database server using:
docker-db-1  | 
docker-db-1  |     pg_ctl -D /var/lib/postgresql/data -l logfile start
docker-db-1  | 
docker-db-1  | initdb: warning: enabling "trust" authentication for local connections
docker-db-1  | initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
docker-db-1  | waiting for server to start....2023-10-25 19:54:29.715 GMT [47] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
docker-db-1  | 2023-10-25 19:54:29.717 GMT [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
docker-db-1  | 2023-10-25 19:54:29.722 GMT [50] LOG:  database system was shut down at 2023-10-25 19:54:29 GMT
docker-db-1  | 2023-10-25 19:54:29.727 GMT [47] LOG:  database system is ready to accept connections
docker-db-1  |  done
docker-db-1  | server started
docker-db-1  | 
docker-db-1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/schema.sql
docker-db-1  | DROP DATABASE
docker-db-1  | psql:/docker-entrypoint-initdb.d/schema.sql:3: NOTICE:  database "backend_01_db" does not exist, skipping
docker-db-1  | CREATE ROLE
docker-db-1  | CREATE DATABASE
docker-db-1  | 
docker-db-1  | 
docker-db-1  | waiting for server to shut down....2023-10-25 19:54:29.914 GMT [47] LOG:  received fast shutdown request
docker-db-1  | 2023-10-25 19:54:29.915 GMT [47] LOG:  aborting any active transactions
docker-db-1  | 2023-10-25 19:54:29.917 GMT [47] LOG:  background worker "logical replication launcher" (PID 53) exited with exit code 1
docker-db-1  | 2023-10-25 19:54:29.917 GMT [48] LOG:  shutting down
docker-db-1  | 2023-10-25 19:54:29.918 GMT [48] LOG:  checkpoint starting: shutdown immediate
docker-db-1  | 2023-10-25 19:54:29.949 GMT [48] LOG:  checkpoint complete: wrote 932 buffers (5.7%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.015 s, sync=0.013 s, total=0.033 s; sync files=308, longest=0.004 s, average=0.001 s; distance=4261 kB, estimate=4261 kB; lsn=0/1913FC0, redo lsn=0/1913FC0
docker-db-1  | 2023-10-25 19:54:29.955 GMT [47] LOG:  database system is shut down
docker-db-1  |  done
docker-db-1  | server stopped
docker-db-1  | 
docker-db-1  | PostgreSQL init process complete; ready for start up.
docker-db-1  | 
docker-db-1  | 2023-10-25 19:54:30.068 GMT [1] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
docker-db-1  | 2023-10-25 19:54:30.068 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
docker-db-1  | 2023-10-25 19:54:30.068 GMT [1] LOG:  listening on IPv6 address "::", port 5432
docker-db-1  | 2023-10-25 19:54:30.071 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
docker-db-1  | 2023-10-25 19:54:30.076 GMT [63] LOG:  database system was shut down at 2023-10-25 19:54:29 GMT
docker-db-1  | 2023-10-25 19:54:30.082 GMT [1] LOG:  database system is ready to accept connections

No error must be prompted. As you can see the database start from zero, execute schema.sql and then restart, now let's try connect to the database service.

2. Test Inside

Find CONTAINER ID

$ docker container ls
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS                            PORTS      NAMES
068cd444eb00   postgres   "docker-entrypoint.s…"   4 minutes ago   Up 8 seconds (health: starting)   5432/tcp   docker-db-1

Enter the container

$ docker container exec -it 068cd444eb00 bash

Now execute from inside the container to test connect to postgresql service, remember that we define as environment variables in docker-compose.yml POSTGRES_USER=postgres and POSTGRES_PASSWORD=postgres

$ docker container exec -it 068cd444eb00 bash
root@068cd444eb00:/# psql -U postgres -W
Password: 
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

3. Test Outside

To test the connection from outside the container with a user we can use the psql command for it, first install the client, something like sudo yum install postgresql. For this try to connect from the docker host, so lets find the container IP for connect (use schema.sql data):

$ docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 068cd444eb00
172.21.0.2
$ psql -h 172.21.0.2 -p 5432 -d database_name -U username -W
Password: 
psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1), server 16.0 (Debian 16.0-1.pgdg120+1))
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
Type "help" for help.

backend_01_db=# 

I know it came out a little long, but it works well, greetings
IAGARCIA

[vagetablechicken]

Even after adding POSTGRES_DB still getting the same error, when i put a default DB it works but a newly generated one doesn't work, anyone got any idea?

Does anyone know to sync it?

thats my configuration :

services:
  postgres:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: klaus
      POSTGRES_PASSWORD: password
      POSTGRES_DB: customer
      PGDATA: /data/postgres
    volumes:
      - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

psql -U klaus will use the database klaus(the same with user name), but you set another database customer, so it should be psql customer [-U] klaus.

  db:
     image: postgres:16.1-alpine3.19
     restart: always
     volumes:
       - db-data:/var/lib/postgresql/data
     environment:
       - POSTGRES_DB=postgres
       - POSTGRES_USER=postgres
       - PGUSER=postgres
       - POSTGRES_PASSWORD=postgres
     expose:
       - 5432
     healthcheck:
       test: [ "CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"]
       interval: 10s
       timeout: 5s
       retries: 5

[gorgon]

@Drytimor , thank you very much!
Your line test: [ "CMD-SHELL", "pg_isready -d $${POSTGRES_DB} -U $${POSTGRES_USER}"] saved my day!!
I was using test: [ "CMD-SHELL", "pg_isready -U $${POSTGRES_USER}"] and it was giving me fatal errors, coz it was trying to connect to a DB equal to my username...