Seed Postgres database with Docker Compose

Provisioning local infrastructure can be a bit cumbersome, at least during the initial setup. Docker to the rescue! If you haven't used Docker yet then get on it ASAP!

With a simple command, you can provision (and seed) different resources like databases, messaging queues and other services. In this article, I'm going to show you how to create a few simple scripts to get you going with Postgres AND seed some data.

When running docker-compose up in your favourite terminal, Docker will look for a docker-compose.yml file to get instructions. Here I have a simple docker-compose file to spin up a Redis and a Postgres server.

version: '3.8'
services:
  redis:
    image: redis
    ports:
      - '6379:6379'
  postgres:
    build:
      context: ./db
    environment:
      - POSTGRES_PASSWORD=mydbpwd
    ports:
      - '5432:5432'

We also map Redis and Postgres ports to the host machine and instead of pulling the Postgres image directly, we point the compose tool to a directory where the Dockerfile sits: ./db. The db folder has several files in it:

db
|- db.dump.sql
|- Dockerfile
|- init-db.sh

The db.dump.sql just contains plain SQL scripts for both DML (Data Definition Language) and DDL (Data Manipulation Language) instructions. Next up is the Dockerfile:

FROM postgres:11
 
ADD ./init-db.sh /docker-entrypoint-initdb.d/
ADD ./db.dump.sql /db-dumps/

Here we pull the official Postgres 11 image and copy the dump and bootstrap scripts into the container. docker-entrypoint-initdb.d is a magical directory where all the .sh and .sql scripts are ran upon the DB initialization. init-db.sh file creates a new database, user and assigns it all the rights. After the database and user have been set up, the seeding SQL script is ran.

#!/bin/bash
 
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER newuser;
    CREATE DATABASE newdb;
    GRANT ALL PRIVILEGES ON DATABASE newdb TO newuser;
EOSQL
 
psql -f /db-dumps/db.dump.sql newdb

The last bit is to run the docker-compose up command and observe how everything beautifully comes to life:

After you're done with the project, you can easily dismantle the resources by running docker-compose down, easy as.