CODEWITHSUNDEEP
postgresqldocker
KMD92
KMD92

Reputation: 41

How to run an *.sql script for a specific database on init with a postgres docker container using multiple databases?

I'm using a postgres service through docker compose with multiple databases in the same container (one for keycloak and a test db for non-identity managment application data). As per the postgres image docs, I've mounted a volume with a shell script to create multiple databases. This part is working fine, and once the containers are up and running I can access the databases.

Inside the volume there's also an .sql file with a query for creating tables and inserting rows into these tables. While the docker logs show that the tables are created and the insertions are performed, I can't see the tables on either of the created databases. How would I go about ensuring this script runs on the testdb?

Docker compose file:

volumes:
  postgres_data:
      driver: local

services:
  test-webportal-db:
      env_file:
        - ./.env
      image: postgres
      volumes:
        - postgres_data:/var/lib/postgresql/data
        - ./db-init:/docker-entrypoint-initdb.d
      environment:
        - POSTGRES_DB=${TEST_DB}
        - POSTGRES_MULTIPLE_DATABASES=${POSTGRES_MULTIPLE_DATABASES}
        - POSTGRES_USER=${POSTGRES_USER}
        - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      ports:
        - 5432:5432

  test-webportal-db-adminer:
      image: adminer:latest
      restart: always
      depends_on:
        - test-webportal-db
      ports:
        - 8090:8080

  test-webportal-identity:
      env_file:
        - ./.env
      image: quay.io/keycloak/keycloak:latest
      environment:
        - DB_VENDOR=${DB_VENDOR}
        - DB_ADDR=${DB_ADDR}
        - DB_DATABASE=${DB_DATABASE}
        - DB_USER=${DB_USER}
        - DB_SCHEMA=${DB_SCHEMA}
        - DB_PASSWORD=${DB_PASSWORD}
        - KEYCLOAK_USER=${KEYCLOAK_USER}
        - KEYCLOAK_PASSWORD=${KEYCLOAK_PASSWORD}
      ports:
        - 8080:8080
      depends_on:
        - test-webportal-db

Shell script:

#!/bin/bash

set -e
set -u

# function to create user and database with POSTGRES_PASSWORD
function create_user_and_database() {
    local database=$1
    echo "  Creating user and database '$database'"
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
        CREATE USER $database WITH PASSWORD '$POSTGRES_PASSWORD';
        CREATE DATABASE $database;
        GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

# create a database for each one listed in the POSTGRES_MULTIPLE_DATABASES env variable
if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
    echo "Multiple database creation requested: $POSTGRES_MULTIPLE_DATABASES"
    for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr ',' ' '); do
        create_user_and_database $db
    done
    echo "Multiple databases created"
fi

Upvotes: 2

Views: 1467

Answers (1)

KMD92
KMD92

Reputation: 41

I managed a workaround by removing the .sql file and placing the queries into the shell script as follows:

psql -U $TEST_DB $TEST_DB << EOF
  <queries here>
EOF

Upvotes: 1

Related Questions