Learn Docker With My Newest Course

Dive into Docker takes you from "What is Docker?" to confidently applying Docker to your own projects. It's packed with best practices and examples. Start Learning 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

  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!

Free Intro to Docker Email Course

Over 5 days you'll get 1 email per day that includes video and text from the premium Dive Into Docker course. By the end of the 5 days you'll have hands on experience using Docker to serve a website.