Vince Yuan +

Restoring/Backing up Postgres Database in a Docker Container

2015-05-29

In the previous tutorial, I show you how to deploy a web app, Redis, Postgres, Nginx with Docker. This post shows you how to restore and backup Postgres database which is running in a Docker container. You can get all source code at https://github.com/vinceyuan/DockerizingWebAppTutorial.

I use pg_dump to dump a database to a text file. And then zip and upload it to Amazon S3 with a handy command line tool s3cmd. In this tutorial, I can't provide access_key and secret_key of S3. So you may not really run it until you get your own.

Let's install s3cmd on the host.

  1. apt-get install -y s3cmd

Run s3cmd to configure. You should input your access_key and secret_key of S3. It creates .s3cfg at your home directory.

Restore

In the previous tutorial, after running Postgres in a container, we should restore database from the backup. We need to get the backup first.

  1. cd /mydata && mkdir db_restore && cd db_restore

List backup files in your s3.

  1. s3cmd ls s3://your_db_dumps/

Download a backup file and unzip.

  1. s3cmd get s3://db_dumps/dump2015-05-22T09:15:13+0800.txt.gz
  2. gunzip dump2015-05-22T09\:15\:13+0800.txt.gz

Run a container with Postgres client to restore db from dump.

  1. docker run -d --name myredispgclient --link mypostgres:postgres -v /mydata/db_restore/:/tmp/ -i -t myredispgclient /bin/bash

If it does not show the console of the container, run this to get it

  1. # docker exec -i -t myredispgclient bash $ env | grep POSTGRES_
  2. $ psql -h $POSTGRES_PORT_5432_TCP_ADDR -p $POSTGRES_PORT_5432_TCP_PORT -U postgres
  3. > \l # List all databases. Make sure mynodeappdb exists
  4. > \q # Quit
  5. $ psql -h $POSTGRES_PORT_5432_TCP_ADDR -p $POSTGRES_PORT_5432_TCP_PORT -U postgres -d mynodeappdb < /tmp/dump2015-05-22T09\:15\:13+0800.txt
  6. $ exit # Exit the console of the container
  7. # docker stop myredispqclient

Backup

Let's build mys3cmd image which has s3cmd 1.0 installed. The latest version is 1.5.2. But 1.5.2 fails to upload big files for me. So I have to use 1.0. This is the Dockerfile. It is based on myredispgclient image.

  1. FROM myredispgclient
  3. RUN apt-get update \
  4.  && wget -O- -q http://s3tools.org/repo/deb-all/stable/s3tools.key | apt-key add - \
  5.  && wget -O/etc/apt/sources.list.d/s3tools.list http://s3tools.org/repo/deb-all/stable/s3tools.list \
  6.  && apt-get update \
  7.  && apt-get install -y --force-yes --no-install-recommends s3cmd=1.0.0-4 \
  8.  && apt-get clean \
  9.  && apt-get autoremove \
  10.  && rm -rf /var/lib/apt/lists/\*

Build it.

  1. cd /DockerizingWebAppTutorial/dockerfiles/mys3cmd
  2. docker build -t mys3cmd .

Let's build mydbbackup2s3 image for backing up. I tried many times to write a working Dockerfile for mydbbackup2s3. That's why I create mys3cmd image. With mys3cmd image, it doesn't need to download and install s3cmd when I re-build mydbbackup2s3 again and again. In the Dockerfile of mydbbackup2s3, we copied .pgpass and .s3cfg to /root/ (You need to provide .s3cfg yourself). .pgpass stores the password of the database and we have to chmod 600 for it. dump_db_and_upload.sh is a script I wrote to dump, zip, and upload the backup to s3.

  1. FROM mys3cmd
  3. COPY .pgpass /root/
  4. COPY .s3cfg /root/
  5. COPY dump_db_and_upload.sh /root/
  7. RUN chmod 600 /root/.pgpass
  9. VOLUME /db_dumps
  11. CMD ["/root/dump_db_and_upload.sh"]

The first postgres in .pgpass is the linked container.

  1. postgres:5432:mynodeappdb:postgres:postgres

dump_db_and_upload.sh

  1. #!/bin/bash
  2. # A script to dump db and compress it and then upload the file to S3.
  3. # should change mode like 'chmod 777 dump_db_and_sync.sh'
  5. FILENAME=$(TZ=Asia/Hong_Kong date +"dump%Y-%m-%dT%H:%M:%S+0800.txt.gz")
  6. FULLDIR="/db_dumps/"
  7. FULLPATH="$FULLDIR$FILENAME"
  8. S3PATH="s3://db_dumps/"
  9. echo "Begin to dump mynodeappdb to $FULLPATH"
  11. # We don't use $POSTGRES_PORT_5432_TCP_ADDR for host, but use postgres which is linked
  12. # $POSTGRES_PORT_5432_TCP_ADDR will change, but link name postgres does not change.
  13. # We also use the link name postgres in .pgpass
  15. pg_dump -h postgres -U postgres mynodeappdb | gzip > $FULLPATH
  16. echo "Done"
  17. echo "Begin to upload the dump to $S3PATH"
  18. s3cmd put $FULLPATH $S3PATH
  19. echo "Done"
  20. echo "Delete the local dump"
  21. rm $FULLPATH
  22. echo "Finished dump and upload"

Build the image.

  1. cd /DockerizingWebAppTutorial/dockerfiles/mydbbackup2s3
  2. docker build -t mydbbackup2s3 .

Run the container. We don't add --restart=always here.

  1. docker run -d --name mydbbackup2s3 --link mypostgres:postgres -v /mydata/db_dumps:/db_dumps mydbbackup2s3

It should dump database and backup immediately. The container will quit after backing up is done.

Auto-backup

We should backup the database regularly and automatically. Let's create a cron job by running this command:

  1. crontab -e

Input the following lines, save and quit. It will run mydbbackup2s3 container to backup database every Tuesday.

  1. #MIN (0-59)  HOUR (0-23)  DoM (1-31)  MONTH (1-12)  DoW (0-7)     CMD
  2. #Dump pophub db and upload to S3 every Tuesday at 10:00 Hong Kong time
  3.      0            2           *           *            2          docker start mydbbackup2s3

Here are some tips about Docker.

blog comments powered by Disqus

Blogs

Projects