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:
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
The official PostgreSQL Docker image allows you to place SQL files in the
docker-entrypoint-initdb.dfolder, and the first time it starts it will import those SQL files.
ENV PGDATA=/dataenvironment variable instructs the PostgreSQL image to use
/var/lib/postgresql/dataas 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!