Restore a Postgres Backup into a Docker container

At times, Docker can be mind-boggling, especially when you strive for doing things “right”, which means following the “everything is a container” approach.

Now and then you will have to get large amounts of data into your container like restoring a database backup into a Docker-container. The Internets are full with  good advice on how to do it (right), but given the combination of an out-of-docker database dump file and the intention to run a dockerized database with a data volume only container, the approach in this post was of help. I assembled the step-wise guide from the links in the footage, kudos goes to these authors.

This How-To is somewhat Postgres specific, but with some tailoring should apply to other Database systems like MySQL too. Let’s go.

(Optional): Copy the backup file to the remote host

docker-machine scp <path_to_local_backup> <machine-name:>

Note the trailing colon after the machine-name. This will copy path_to_local_backup into the home directory of machine-name.

On the host where the database should be restored:

1. Create a data only container

docker create -v /var/lib/postgresql/data --name ogdatdata postgres echo "OGDAT Checker PostgreSQL Data Container"

2. Run a temporary Postgres container to restore the backup. Attach the data only container AND the directory containing the local backup.

docker run -d -P --volumes-from ogdatdata --name ogdatrestore -v /home/ubuntu/backup/:/var/lib/postgresql/backup postgres

3. Attach to the restore container ogdatrestore

docker exec -it ogdatrestore bash

Within the container,

4. Switch to the postgresql user

su postgres

5. Create the superuser and matching password for the database to restore.

This user has to have be the same username and password as the user which has been used to access the database you intend to restore. This requires to attach to database template1, which will be used as the template from which the database will be restored.

psql -d template1
-- create the super user
create role <username> superuser;
-- allow this user to login
-- set a password for this user
ALTER USER <username> WITH PASSWORD '<password>';

6. Exit the psql session

with \q. Still within the container,

7. restore the backup on the command prompt

pg_restore -C -d postgres <backupfile>

This will create the database with the same name and grant access rights as specified in the backup. Once the restore is finished, you can check the name/existence of the restored database by connecting to the postgres default database and list all available databases from within psql with \l . Exit from the restore container to command prompt.

8. Create the final Docker Postgres container. Note to specify the correct name of the database the restore has been made into from the previous step as well as the <username> and <password> of the steps before:

docker run -d -P -p 6002:5432--volumes-from ogdatdata --name ogdat -e POSTGRES_USER=<username> -e POSTGRES_PASSWORD=<password> -e POSTGRES_DB=<databasename> postgres

Note that the previous command also performs a port mapping from the container exposed port 5432 to the host port 6002. Otherwise you might get port clashes if multiple postgres-databases are running on the same host.

9. Check if everything went smooth by checking the logs:

docker logs ogdat

Once the Postgresql-container is up and running you can

10. delete the container used to restore the backup into the data only volume

docker stop ogdatrestore
docker rm ogdatrestore

Helpful References

Exporting and Distributing Docker Images and Data Container Contents!topic/docker-user/UqH-Ggi_1dI



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s