Search code examples
postgresqldockersynology

How do I setup entrypoint initdb scripts for postgresql in docker?

I have setup a postgresql database on my Synology NAS using Docker. The container boots up and I can access postgresql using both ssh (psql) and DataGrip. But the issue is that the init scripts that I have created don't work as expected, they don't create any databases.

When I add another init script but with a different user I can see that role being added when checking the roles folder (in DataGrip) but I never see any database with the same name. I can also not see the roles that my init scripts are creating which I see in DataGrip when using psql \du.

So since the users are being created I assume the init scripts in my config folder are being run, but why are the databases not create not the roles being visible when using psql \du?

Note: I have done prune on the posgres image and emptied the data folder before running docker-compose up -d to be sure there are no old things left that give me a problem.

docker-compose.yml

version: '3'
services:
  postgresql:
    container_name: postgresql
    restart: always
    image: postgres:latest
    network_mode: "bridge"
    environment: 
    - TZ=Europe/Stockholm
    - VERSION=latest
    - POSTGRES_USER=synology
    - POSTGRES_PASSWORD=synology
    ports:
    - "3456:5432"

    volumes:
    - /volume1/docker/postgresql/data:/var/lib/postgresql/data
    - /volume1/docker/postgresql/config:/docker-entrypoint-initdb.d

    cap_add:
    - DAC_READ_SEARCH
    - NET_BIND_SERVICE
    - SETGID
    - SETUID
    - SYS_ADMIN
    - SYS_PTRACE

    security_opt:
    - apparmor:unconfined

init.sql

CREATE USER synology with encrypted password 'synology';
CREATE DATABASE synology;
GRANT ALL PRIVILEGES ON DATABASE synology TO synology;

enter image description here

Anyone got any ideas what might be wrong here?

UPDATE: I'm starting to think that when I use the psql command I accesse the Synology local DB and not the one in my container... but still doesn't explain why only the users are created and not my databases.

Solution

  • Ok, so I was being a little stupid here, I was basically accessing the Synology postgres DB when just running the psql command. I first need open up bash in the container docker exec -it CONTAINER_ID bash and then run psql -U postgres, then I can list the databases and users and see that they all got created.