Categories: Programming

A useful discovery I made recently: if you need a relational database for demo or testing purposes, then Postgres publishes a really convenient official Docker image. What it not immediately clear is that this image has a mechanism for running arbitrary SQL scripts on startup - which is great for defining tables.

It is therefore trivial to create an image that others (eg developers or sales) can quickly deploy, that contains not only a database server but also whatever initial schema you need.

Given directory structure

somebasedir
+ docker
  + postgres
    + dockerfile
    + my.sql

where dockerfile contains:

FROM postgres:11-alpine
COPY my.sql /docker-entrypoint-initdb.d/

then

docker build -t simon/mydb docker/postgres

# Start DB as background daemon
# (omit detach option to see output)
docker run --detach -p 5432:5432 --name mydb simon/mydb
docker stop mydb # when not needed
docker start mydb # to resume database without dataloss

# Interactive shell example
docker exec -it mydb /bin/sh
su -c psql postgres
\d
\d SOME_TABLE
select * from SOME_TABLE;
quit

Note however that if the client app happens to be in Java and using Spring-Boot then enabling Flyway might be the easiest way to set up schemas.