Updated on February 19, 2019 in #docker

Docker Tip #79: Saving a Postgres Database in a Docker Image

Every once in a while you may want to create a pre-built PostgreSQL Docker image that already has a database.

This is very handy to do if you want to set up a test database that’s let’s say 2 gigs in size. Having to download and / or import that every time your CI pipeline runs is an epic waste of time, bandwidth and computing cycles. This isn’t limited to CI but that’s a popular use case.

Creating a custom Dockerfile:

Replace 001_data.sql with your SQL file.

FROM postgres:11.5-alpine

COPY 001_data.sql /docker-entrypoint-initdb.d/001_data.sql

ENV PGDATA=/data

1. The official PostgreSQL Docker image allows you to place SQL files in the docker-entrypoint-initdb.d folder, and the first time it starts it will import those SQL files.

2. The ENV PGDATA=/data environment variable instructs the PostgreSQL image to use /data instead of /var/lib/postgresql/data as its data directory.

Number 2 is necessary because PostgreSQL sets VOLUME /var/lib/postgresql/data in its Dockerfile which means it will be empty every time the container starts if you don’t persist it to the Docker host. We’re using a custom data path instead to avoid it being empty.

Creating a new image with the preset database:

# Build our image from the Dockerfile and then run it.
docker image build -t example .
docker container run --rm -it --name example example

# [Wait until PostgreSQL is done importing your SQL file]
# [Open a second terminal]

# Delete the SQL file from the running container since it's already imported.
docker container exec example rm -rf /docker-entrypoint-initdb.d/*

# Commit this change to a brand new Docker image.
docker container commit example example-db:latest

# Stop the original container.
docker container stop example

Running your new container and verifying the data exists:

docker container run --rm -it --name example-db example-db

# [Open a second terminal]

docker container exec example-db psql -U postgres -c "\dt;"

You should see a list of your tables. Congrats, your new example-db image is good to go!