Skip to content

Database Backups Docker

Databases have a special treatment when doing backups, for them we have to dump them.

Mongo

In the case of a wekan application the following is the procedure assuming we have wekan's docker compose file:

The container wekan-app doesn't contain any data itself, all the data to backup is inside the database, in this case is mongodb, for which we have to access the container: 

sudo docker exec -it wekan-db bash

Once inside the container we have to go to /data and dump the database: 

# Inside the container
cd /data
mongodump
exit

Now we can go back to the host and locate the dump in a better place and/or back it up with restic.

I located the dump in /backups/wekan in my machine, in which also inside this folder I have the restic repository so: 

cd /home/sysadmin/backups/wekan/
sudo restic -r path/to/restic/repo backup dump --tag wekan_db_dump

In the previous command we create the snapshot for the dump in which we also add the tag wekan_db_dump, this is going to help us differentiate from other snapshots when restoring. And that would be it for the backup of a database (mongo version)

Restore

Now for restoring we first restore the snapshot from restic: 

sudo restic -r path/to/restic/repo restore --tag wekan_db_dump latest --target /home/sysadmin/backups/

Here we use the --tag wekan_db_dump latest in order to get the specific snapshot that we want (because using id's is not so intuitive). The location of this folder is not very important since we are going to copy it inside the wekan-db container.

Now we create our new wekan app with docker compose up -d. This instance is supposed to be new, meaning no users, no tables, nothing.

We stop the wekan-app container: 

sudo docker stop wekan-app

Go inside the database container, cd to /data and remove previous dump (if there's one): 

sudo docker exec -it wekan-db bash
cd /data
rm -rf dump

Exit the db container: 

exit

Copy the dump to inside the docker container: 

sudo docker cp /path/to/dump wekan-db:data/

Go inside the database to /data and restore the database: 

sudo docker exec -it wekan-db bash

# Inside the container
cd /data
mongorestore --drop
exit

And start wekan again sudo docker start wekan-app

Postgres

The process to backup databases is similar across DBs, the only thing that might change is the utility used to dump which depends on the DB used. For example for PG is pg_dump, for mysql/MariaDB is mysqldump and for MongoDB is mongodump.

Of course as a first step is to stop whatever container that might be writing to the database. In this example I created a postgres container and created a pagila database.

Then we access the container and dump the database: 

sudo docker exec postgres \
  pg_dump -U postgres pagila > mydb_$(date +%F).sql

In general the process is the same, we create the dump, delete the db, create a new empty one, copy the dump from the host to the docker container, create the empty database and execute the dump from inside the container with psql:

sudo docker stop postgres && sudo docker rm postgres
sudo docker run -d \
--name postgres \
-e POSTGRES_PASSWORD=paspas \
postgres:16

sudo docker cp /path/to/dump postgres:/dump.sql

sudo docker exec -it postgres psql -U postgres -c 'CREATE DATABASE pagila'
sudo docker exec -it postgres psql -U postgres pagila -f /dump.sql

By default the user and database names are set to postgres. If we were to have different users we will have to use pg_dumpall --roles-only -U postgres > roles.sql. By default POSTGRESS_USER and DB are set to postgres. This also applies if we have more than one database created in our pg container, we would make use of pg_dumpall.

TL;DR: - When we have several users and or databases better use pg_dumpall. - If we have several different database owners then better do: 

sudo docker exec -it <pg-container> pg_dumpall --roles-only -U postgres > /backups/postgres/roles.sql
# create a dump for each db
sudo docker exec -it <pg-container> pg_dump -U postgres db1 > /backups/postgres/db1.sql
sudo docker exec -it <pg-container> pg_dump -U postgres db2 > /backups/postgres/db2.sql


# Then restore as superuser. After creating the container, copy the dumps to the container and then first create the roles and then manually create the dbs and recreate with the dump and the respective owner, something like:

sudo docker cp path/to/roles <pg-container>:/roles.sql
sudo docker cp path/to/dump <pg-container>:/db1.sql
sudo docker cp path/to/dump <pg-container>:/db2.sql

sudo docker exec -it pg-dump-test psql -U postgres -f roles.sql


sudo docker exec -it pg-dump-test psql -U postgres -c 'CREATE DATABASE db1 WITH OWNER = 'owner_name''

sudo docker exec -it pg-dump-test psql -U postgres -c 'CREATE DATABASE db2 WITH OWNER = 'owner_name''

sudo docker exec -it pg-dump-test psql -U postgres db1 -f /db1.sql
sudo docker exec -it pg-dump-test psql -U postgres db2 -f /db2.sql

Or maybe a simpler approach: 

sudo docker exec -it <pg-container> pg_dumpall --roles-only -U postgres > roles.sql

sudo docker exec -it <pg-container> pg_dumpall -U postgres > all.sql

# Copy from host to container
sudo docker cp roles.sql <pg-container>:/roles.sql
sudo docker cp all.sql <pg-container>:/all.sql

# Execute first the roles.sql then the all.sql all as the superuser
sudo docker exec -it <pg-container> psql -U postgres -f roles.sql
sudo docker exec -it <pg-container> psql -U postgres -f all.sql

# Check roles, databases, ownerships and contents
sudo docker exec -it pg-dump-test psql -U postgres -c '\du'
sudo docker exec -it pg-dump-test psql -U postgres -c '\list'

# Here demo is a db I previously created for testing, that could be perfectly be replaced for whatever other db was created.
sudo docker exec -it pg-dump-test psql -U postgres demo -c 'SELECT * FROM USERS;'

At this point everything should like same as with the previous db.