Docker Compose file does not create postgres database

Question

My docker-compose.yml file:

version: '3.8'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: my_db
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - 5432:5432

volumes:
  postgres_data:

The log from the new container:

2023-11-19 14:28:02 The files belonging to this database system will be owned by user "postgres".
2023-11-19 14:28:02 This user must also own the server process.
2023-11-19 14:28:02 
2023-11-19 14:28:02 The database cluster will be initialized with locale "en_US.utf8".
2023-11-19 14:28:02 The default database encoding has accordingly been set to "UTF8".
2023-11-19 14:28:02 The default text search configuration will be set to "english".
2023-11-19 14:28:02 
2023-11-19 14:28:02 Data page checksums are disabled.
2023-11-19 14:28:02 
2023-11-19 14:28:02 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2023-11-19 14:28:02 creating subdirectories ... ok
2023-11-19 14:28:02 selecting dynamic shared memory implementation ... posix
2023-11-19 14:28:02 selecting default max_connections ... 100
2023-11-19 14:28:02 selecting default shared_buffers ... 128MB
2023-11-19 14:28:02 selecting default time zone ... Etc/UTC
2023-11-19 14:28:02 creating configuration files ... ok
2023-11-19 14:28:02 running bootstrap script ... ok
2023-11-19 14:28:02 performing post-bootstrap initialization ... ok
2023-11-19 14:28:02 syncing data to disk ... ok
2023-11-19 14:28:02 
2023-11-19 14:28:02 
2023-11-19 14:28:02 Success. You can now start the database server using:
2023-11-19 14:28:02 
2023-11-19 14:28:02     pg_ctl -D /var/lib/postgresql/data -l logfile start
2023-11-19 14:28:02 
2023-11-19 14:28:02 waiting for server to start....2023-11-19 17:28:02.849 UTC [48] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-11-19 14:28:02 2023-11-19 17:28:02.852 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-19 14:28:02 2023-11-19 17:28:02.861 UTC [51] LOG:  database system was shut down at 2023-11-19 17:28:02 UTC
2023-11-19 14:28:02 2023-11-19 17:28:02.866 UTC [48] LOG:  database system is ready to accept connections
2023-11-19 14:28:02  done
2023-11-19 14:28:02 server started
2023-11-19 14:28:03 CREATE DATABASE
2023-11-19 14:28:03 
2023-11-19 14:28:03 
2023-11-19 14:28:03 /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2023-11-19 14:28:03 
2023-11-19 14:28:03 waiting for server to shut down...2023-11-19 17:28:03.028 UTC [48] LOG:  received fast shutdown request
2023-11-19 14:28:03 .2023-11-19 17:28:03.030 UTC [48] LOG:  aborting any active transactions
2023-11-19 14:28:03 2023-11-19 17:28:03.031 UTC [48] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2023-11-19 14:28:03 2023-11-19 17:28:03.032 UTC [49] LOG:  shutting down
2023-11-19 14:28:03 2023-11-19 17:28:03.033 UTC [49] LOG:  checkpoint starting: shutdown immediate
2023-11-19 14:28:03 2023-11-19 17:28:03.102 UTC [49] LOG:  checkpoint complete: wrote 923 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.014 s, sync=0.051 s, total=0.071 s; sync files=301, longest=0.010 s, average=0.001 s; distance=4257 kB, estimate=4257 kB; lsn=0/1913068, redo lsn=0/1913068
2023-11-19 14:28:03 2023-11-19 17:28:03.108 UTC [48] LOG:  database system is shut down
2023-11-19 14:28:03  done
2023-11-19 14:28:03 server stopped
2023-11-19 14:28:03 
2023-11-19 14:28:03 PostgreSQL init process complete; ready for start up.
2023-11-19 14:28:03 
2023-11-19 14:28:02 initdb: warning: enabling "trust" authentication for local connections
2023-11-19 14:28:02 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.
2023-11-19 14:28:03 2023-11-19 17:28:03.146 UTC [1] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-11-19 14:28:03 2023-11-19 17:28:03.146 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-11-19 14:28:03 2023-11-19 17:28:03.146 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-11-19 14:28:03 2023-11-19 17:28:03.149 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-19 14:28:03 2023-11-19 17:28:03.153 UTC [64] LOG:  database system was shut down at 2023-11-19 17:28:03 UTC
2023-11-19 14:28:03 2023-11-19 17:28:03.158 UTC [1] LOG:  database system is ready to accept connections

The issue is that I can't connect to the created database, and it doesn't show up when connecting through Heidi or Beekeeper, apart from the schemas "information_schema", "pg_catalog", "pg_toast" and "public".

I've tried several different approaches, including creating an init_db.sh file to create the database "my_db", but haven't been able to make it work.

I've made sure to remove all postgres_data volumes and containers, but my_db is never created, nor is any postgres database.

I have also tried not setting up the POSTGRES_USER and/or POSTGRES_DB, but a database simply isn't created.

One last thing I have attempted was to delete the postgres image, no success either.

I have also tried different postgres and docker versions

EDIT: I have now confirmed that the database somehow exists, as I try to run the sql command CREATE DATABASE my_db gives me an error ERROR: database "my_db" already exists, which leads me to think that I'm misunderstanding something. By all means, I still cannot connect to it nor see it no matter what.

Answer 1

I figured the reason the database my_db doesn't show up is because I don't have a clue on how to connect to them correctly through Heidi and Beekeeper, because it displays fine when using DBeaver instead.

dbeaver table

And I couldn't connect to it before because I had required SSL_MODE in my backend.