How to Restore Postgres Data Dump in Docker

Before 2018, I largely use the following technologies. MySQL for database and VirtualBox and Vagrant for running my environments. Starting in mid 2018, I moved towards using Postgres and Docker instead. This is especially the case when I paid to learn about cookiecutter-django. Adding Postgres and Docker to my toolbox has increased my effectiveness in my software business. However, this also means I need to figure out regularly used tasks involving these new technologies. Some frequent tasks includes backing up and restoring database dumps. Therefore, this article is about how to restore database dumps for Postgres running inside Docker containers. I’ll write the counterpart article to the backup process on a separate day and add the link to it when it’s ready.

Key Insights You Need to Know About Docker Containers

Before I go into the step by step about the backup and restore database dumps, you need to first acquire some fundamental insights of the Postgres and Docker technologies.

Firstly, Docker containers have their own volumes. Think of them like the disk volumes in your host system.
Next, realize that executing commands inside the docker container from your host system is possible. Which is to run docker exec <container_name> <your_command>
When you want to run certain commands within the containers and these commands need to interact with certain files, the assumption is that these files are found in the docker container’s own volumes.

Therefore, when you execute the typical Postgrespg_restore commands, the database dumps will need to be in the docker container’s volumes. This is key.

There are several ways to achieve the transfer of files between your host system and the docker container. Similarly, there are several ways to run backup and restore. To keep things simple for beginners, I will only state one way to accomplish this. It doesn’t mean my one way is the best way. But, keeping it to only one way makes it easy for beginners to follow. Moreover, it’s easy for me to update the content here as time goes by.

How to Restore Data Dump Using pg_restore

Step 1: Find the name and id of the Docker container hosting the Postgres instance

Turn on your Docker and run the docker ps command to locate the name and id of the Docker container. Which leads to the following.

$ docker ps

CONTAINER ID   ...                  NAMES
 abc985ddffcf  ...              my_postgres_1

Step 2: Find the volumes available in the Docker container

Run the command docker inspect -f '{{ json .Mounts }}' <container_id> | python -m json.tool

Then, look at the volume paths under the key Destination.
You should get the following:

$ docker inspect -f '{{ json .Mounts }}' abc985ddffcf | python -m json.tool
[
    {
        "Type": "volume",
        "Name": "my_postgres_backup_local",
        "Source": "/var/lib/docker/volumes/my_postgres_backup_local/_data",
        "Destination": "/backups",
        "Driver": "local",
        "Mode": "rw",
        "RW": true,
        "Propagation": ""
    },
    {
        "Type": "volume",
        "Name": "my_postgres_data_local",
        "Source": "/var/lib/docker/volumes/my_postgres_data_local/_data",
        "Destination": "/var/lib/postgresql/data",
        "Driver": "local",
        "Mode": "rw",
        "RW": true,
        "Propagation": ""
    }
]

In this case, we have /backups and /var/lib/postgresql/data as the volume paths.

Step 3: Copy dump into one of the volumes

Pick a volume and copy your dump in. Run docker cp </path/to/dump/in/host> <container_name>:<path_to_volume>

In my case, I pick the volume /backups. Which gives us the following.

$ docker cp my_data.dump my_postgres_1:/backups

Step 4: Get the database owner to run pg_restore command

Execute the pg_restore command via the docker exec command. Which means the generic forms of both commands are the following.

Note: I’m assuming that the destination postgres database already exists.

For pg_restore:

pg_restore -U <database_owner> -d <database_name> <path_to_dump>

For docker exec:

docker exec <container_name> <some_command>

Sometimes, you don’t know who the database owner is. This is purely optional if you already know. In any case, you can find the owner by retrieving the list of databases and their owners. That means a psql -U postgres -lcommand. Which you have to run within the docker exec command as well. Therefore, we get the following.

docker exec my_postgres_1 psql -U postgres -l

          List of databases
        Name        |  Owner   
--------------------+----------
 some_database      | postgres

After I have all the information I need, I’m ready to run pg_restore. Then, this becomes the following.

docker exec my_postgres_1 pg_restore -U postgres -d some_database /backups/my_data.dump

Conclusion

I started by covering some key fundamentals about Docker and Postgres. Following which, I went into code level details about the commands to restore your Postgres data dump in a Docker container for Postgres.

Now, I end with a summary of the four steps to do so.

How to restore Postgres data dump in a Docker container using pg_restore

Find the name and id of the Docker container hosting the Postgres instance
Turn on Docker and run docker ps to see the list of containers and their names and ids.
Find the volumes available in the Docker container
Run docker inspect -f '{{ json .Mounts }}' <container_id> | python -m json.tool
Copy the dump from your host system to one of the volumes
Run docker cp </path/to/dump/in/host> <container_name>:<path_to_volume>
Execute pg_restore via docker exec command
docker exec <container_name> pg_restore -U <database_owner> -d <database_name> <path_to_dump>

Problems? Errors? Leave a comment below to let me know if it works for you.

This is post #4 in my quest for publishing weekly.

Photo from Youtube

How to Restore Database Dumps for Postgres in Docker Container