DEV Community

Cristian Angulo
Cristian Angulo

Posted on • Edited on

Multiples Postgres databases in one service with Docker Compose

Image description

Sometimes, we need to have multiple database instances to test our projects. Until now, Docker and Docker Compose don't have an easy alternative to resolve this.

Some people (and I) have gotten around this by creating services for each database they need:



### docker-compose.yaml
services:
  postgres_1:
    image: postgres:latest
  postgres_2:
    image: postgres:latest


Enter fullscreen mode Exit fullscreen mode

I resolved this by adding a new entrypoint and setting a environment var in the Docker Compose Service for Postgres:

The container entrypoint:



### entrypoint.sh
#!/bin/bash

set -e
set -u

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;
        CREATE DATABASE $database;
        GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

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


Enter fullscreen mode Exit fullscreen mode

And the docker-compose service:



### docker-compose.yaml

  postgres:
    image: postgres:alpine
    container_name: postgres
    restart: unless-stopped
    ports:
      - 5432:5432
    environment:
        POSTGRES_PASSWORD: "postgres"
        POSTGRES_MULTIPLE_DATABASES: users, products, orders
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5
   volumes:
     - ./<path-of-the-entrypoint>/multiple-databases.sh:/docker-entrypoint-initdb.d/multiple-databases.sh



Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
jonathanpalma profile image
Jonathan Palma • Edited

I updated the entrypoint.sh file you shared to prevent an error I noticed on postgres:16-alpine (didn't really tried on other versions)

These are the small adjustments I made:

  • Specify Default Database for Initial Connection:

The psql command explicitly connects to the postgres database (--dbname "postgres"). This is a key change because it ensures that the initial connection is always made to a database that is guaranteed to exist (postgres).

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "postgres" <<-EOSQL
Enter fullscreen mode Exit fullscreen mode
  • Improved Environment Variable Handling:

The script uses ${POSTGRES_MULTIPLE_DATABASES:-} to handle the case where POSTGRES_MULTIPLE_DATABASES might not be set, avoiding potential unbound variable errors.

if [ -n "${POSTGRES_MULTIPLE_DATABASES:-}" ]; then
Enter fullscreen mode Exit fullscreen mode

This is how the file ended up looking:

#!/bin/bash

set -e
set -u

function create_user_and_database() {
  local database=$1
  echo "  Creating user and database '$database'"
  psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "postgres" <<-EOSQL
        CREATE USER $database;
        CREATE DATABASE $database;
        GRANT ALL PRIVILEGES ON DATABASE $database TO $database;
EOSQL
}

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
Enter fullscreen mode Exit fullscreen mode