Small Scale Database Backups

The biggest reason I moved away from the cloud is to have control of my own data. However, that meant knowing how to protect it. Backups are the only way to keep the data truly safe.

I have written before about proper backups so I won’t rehash them too much. However, one key thing I did not talk about much is doing a database backup. Below is a short documentation on backing up four engines properly:

1. SQLite
2. PostgreSQL
3. MariaDB
4. MySQL

Note

These are best done if your database is small (I’d say less than 100 GB). For larger databases, you can still do the method I say but it will be slower. Do note this is just the database and does not include assets like images and videos. You may be surprised by how much data 100 GB actually looks like.

For most self-hosters, you will find that the below commands suffice. If you are dealing with larger amounts of data, I recommend you read the documentation of the database engine you are using.

Why a Database Backup needs care

Doing a simple copy of the files in a database seems great and logical. However, that can result in corrupted data if you copied the files while something is writing data.

Thus, do not just copy the files. That can work but it is inconsistent and a backup should be anything but inconsistent.

Backing up SQLite

I actually did the above mistake with SQLite. Since it’s just a file, I just copy the .db or .sqlite depending on what I have. But I just recently learned that the above still applies and I was just getting lucky. If you copy the files when nothing is writing, nothing goes wrong. That being said, the proper way to do it is through this command:

sqlite3 [dbname] .dump | zstd > backup.sql.zst

I use zstd for compression but you can use any other compression method. You can even avoid compression especially if you are using something like Restic which already compresses your data. However, I like doing this in advance as preference.

Restoring a SQLite Database

The SQLite dump is just SQL so you can just execute it. A one liner is shown below:

cat backup.sql.zst | unzstd | sqlite3 "[dbname]"

Backing up Postgres

pg_dump "[dbname]" -U "[dbuser]" | zstd > backup.sql.zst

Restoring a Postgres Backup

cat backup.sql.zst | unzstd | psql -U "[dbuser]"

Backing up MariaDB

mariadb-dump --user="[dbuser]" --password="[password]" --all-databases | zstd > backup.sql.zst

Restoring a MariaDB Backup

cat backup.sql.zst | unzstd | mariadb

Backing up MySQL

mysqldump --user="[dbuser]" --password="[password]" --all-databases | zstd > backup.sql.zst

Restoring a MySQL Database

cat backup.sql.zst | unzstd | mysql

Note

The commands for MySQL technically will also work for MariaDB as of writing. However, I dedicated a separate section to both since MariaDB will spit out a warning that they are moving the API to mariadb. Thus, in the future, the MySQL specific commands will likely break.

Dockerized Databases

If you have a Dockerized database, the commands will still work - however, make sure to follow this format when backing up:

docker exec [container] [backup command]

# EXAMPLE:
# docker exec postgres pg_dump "postgres" -U "postgres" | zstd > ~/backup.sql.zst

For restoration, it is pretty similar:

[restore command] | docker exec -i [container]

# EXAMPLE:
# cat ~/backup.sql.zst | unzstd | docker exec -i postgres psql -U "postgres" -d "postgres"

Automating in Backrest

In my previous post, I mentioned that I did not use Backrest for backups. That was because I didn’t think that Backrest was capable of database backups and needed a cron script to do it. I was wrong there so I migrated to Backrest eventually.

Prerequisites

The first prerequisite is that Backrest should be able to run the commands. If you are running Dockerized databases, it is as simple as mounting the Docker sock into the volumes:

services:
  backrest:
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock

I cannot tell you how to backup non-Dockerized databases in Backrest as I do not have them. However, these are the steps I would likely do:

1. Create a cron job of backing up the database dumps into a directory like /db
2. Mount said directory to Backrest as a read only filesystem /db:/db:ro
3. Back up said directory normally like any other folder in Backrest

Plan Format

Create a plan and then fill out the first few values normally. Set the path to something like /db/backup.sql.zst. It’s okay if nothing exists there yet - we are going to create it when we do the backups.

Then, add a command hook at the Hooks Section with the condition CONDITION_SNAPSHOT_START. Then, add any of the commands above and save it to the proper file.

For example, to backup a Postgres DB of Immich, I do this:

# This generates a /db if it does not exist and ignores if it does
mkdir -p /db
docker exec immich_db pg_dump "immich" -U "immich_user" | zstd > /db/backup.sql.zst

You can optionally add another command hook to remove the generated blob after the snapshot ends. With the condition CONDITION_SNAPSHOT_END, you can then run the following script:

rm /db/backup.sql.zst

The above is optional. I like to do this however since I do have a lot of databases. I don’t want my Paperless NGX database backups to suddenly have my Miniflux database backups.

Golden Rule: Test your Backups

Just recently, I moved from an HDD to an NVMe SSD in my home server. Since I had backups, I did not doubt that it will go smoothly. However, I trusted my backups because I tested them. Just a few days before, I used another old PC to do the test restore. It succeeded so I just used the much later backup to restore to NVMe. If that failed, at least I know there will be a restore that will work.

This is crucial: test your backups. You don’t have to do this all the time. But even doing it once a month will be good.