# Backups



# Full operating-system tar backup of a Linux server

A simple bare-bones backup: one compressed tarball of the whole root filesystem, minus the folders that are virtual, temporary or backed up separately.

## Exclude list
`/etc/backup.exclude`:
```
/proc/*
/sys/*
/dev/*
/run/*
/tmp/*
/var/tmp/*
/usr/tmp/*
/mnt/*
/media/*
/lost+found
/var/cache/*
/swapfile
```
Keep `/etc/fstab` in the backup; you will want it for a restore.

## The backup
```
#!/bin/bash
set -euo pipefail
umask 077                                  # the archive contains /etc/shadow and keys
dst=/mnt/backup/$(hostname -s)/system
mountpoint -q /mnt/backup
mkdir -p "$dst"
nice -n 19 tar --one-file-system -cpjf "$dst/$(date +%F).system.tar.bz2" \
    --exclude-from=/etc/backup.exclude -C / .
find "$dst" -type f -name '*.tar.bz2' -mtime +3 -delete
```
- Use `--one-file-system` to stay on the root filesystem. In modern GNU tar, `-l` means `--check-links`, **not** "one filesystem".
- `umask 077` matters. The archive holds password hashes, SSH host keys and database credential files, so it must not be readable by everyone on the NAS share.
- `zstd` (`tar -I zstd`) is much faster than bzip2 if it is available.

## Schedule
```
0 0 * * 0 root /usr/local/bin/backup_system.sh >/dev/null 2>&1
```

## Restore
Boot a rescue system, mount the new root at `/mnt/sysimage`, then run `tar -xpjf <archive> -C /mnt/sysimage`. Reinstall the boot loader, and fix `/etc/fstab` UUIDs if the disks changed.


# MySQL / MariaDB backups to a NAS

Dump every database to its own compressed file, keep a few days, and fail loudly when something is wrong.

## Requirements
- `mysqldump` or `mariadb-dump`. Newer MariaDB installs may only have `mariadb-dump`, so detect which one exists.
- `gzip`.
- A stored login for root (see *Store MySQL backup credentials safely*).
- A mounted backup share, for example `/mnt/backup`.

## The core loop
```
DUMP=$(command -v mysqldump || command -v mariadb-dump) || exit 1
CLI=$(command -v mysql || command -v mariadb) || exit 1
mountpoint -q /mnt/backup || { logger -t mysql-backup "backup share not mounted"; exit 1; }

dbs=$("$CLI" -N -e 'SHOW DATABASES') || { logger -t mysql-backup "cannot connect"; exit 1; }
for db in $dbs; do
  case $db in information_schema|performance_schema|sys) continue;; esac
  "$DUMP" --single-transaction --routines --triggers --complete-insert "$db" \
    | gzip > "/mnt/backup/$(hostname -s)/$(date +%Y.%m.%d_%H).$db.sql.gz" \
    || logger -t mysql-backup "dump of $db failed"
done
find "/mnt/backup/$(hostname -s)" -maxdepth 1 -type f -name '*.sql.gz' -mtime +7 -delete
```
- `--single-transaction` gives a consistent dump of InnoDB tables without locking them.
- Check the `SHOW DATABASES` step. If the login fails and you don't check, the loop silently backs up nothing.
- Add `set -o pipefail` so a failed dump isn't hidden by a successful `gzip`.

## Schedule
Run it only on servers that actually run MySQL:
```
0 * * * * root /usr/local/bin/backup_mysql.sh >/dev/null 2>&1
```
Hourly dumps kept for 7 days is 168 copies of each database. Daily, or hourly with fewer days kept, is usually plenty.

## Restore
```
gunzip -c 2026.01.01_03.mydb.sql.gz | mysql mydb
```


# Store MySQL / MariaDB backup credentials safely

Backup scripts shouldn't contain a database password. Store the login once for the account that runs the backups (usually root), and let `mysql` and `mysqldump` read it automatically.

## MySQL: mysql_config_editor
```
sudo mysql_config_editor set --login-path=client --host=localhost --user=backup --password
sudo mysql_config_editor print --all          # passwords are shown masked
```
This writes an obfuscated `~/.mylogin.cnf` with mode 600. `mysql` and `mysqldump` use the `client` login path by default.

## MariaDB: ~/.my.cnf
MariaDB doesn't ship `mysql_config_editor`, so use an option file instead:
```
sudo install -m 600 /dev/null /root/.my.cnf
sudo tee /root/.my.cnf >/dev/null <<'CNF'
[client]
user=backup
password="your password here"
CNF
```
Quote the password if it contains `#` or `;`.

## Create a least-privilege backup account
Send the SQL on stdin rather than with `mysql -e "..."`, so the password doesn't show up in `ps`:
```
sudo mysql <<'SQL'
CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';
SQL
```

## Test
```
sudo mysql -e 'SELECT CURRENT_USER();'
```


# Back up every database on several PostgreSQL versions

On RHEL, Rocky and Alma, PGDG packages let PostgreSQL 16 and 17 (for example) run side by side, each on its own port with its own binaries in `/usr/pgsql-<ver>/bin`. One script can back up both.

## Describe your instances
```
PG_INSTANCES=(
  "5432|/usr/pgsql-16/bin|pg16"
  "5433|/usr/pgsql-17/bin|pg17"
)
```
Use each instance's own `pg_dump`. A newer `pg_dump` can dump an older server, but not the other way round.

## The loop
Run it as the `postgres` user so it connects over the local socket with peer authentication. No password is needed.
```
for inst in "${PG_INSTANCES[@]}"; do
  IFS='|' read -r port bin name <<<"$inst"
  out=/mnt/backup/$(hostname -s)/$name; mkdir -p "$out"
  "$bin/pg_dumpall" -p "$port" --globals-only | gzip > "$out/$(date +%F_%H).globals.sql.gz"
  while read -r db; do
    "$bin/pg_dump" -p "$port" "$db" | gzip > "$out/$(date +%F_%H).$db.sql.gz"
  done < <("$bin/psql" -p "$port" -Atc \
     "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'")
done
find /mnt/backup/$(hostname -s) -type f -name '*.sql.gz' -mtime +7 -delete
```
- **Back up the globals.** `pg_dump` doesn't include roles, passwords or grants. Without `pg_dumpall --globals-only`, a restore to a new server has no logins.
- Check that the backup share is mounted (`mountpoint -q`), and that the `postgres` user can write to it.
- Use `flock` to stop runs from overlapping.

## Schedule
`/etc/cron.d/backup-postgres`:
```
0 * * * * postgres /usr/local/bin/backup_postgres.sh >/dev/null 2>&1
```

## Restore
```
gunzip -c 2026-01-01_03.globals.sql.gz | psql -p 5433 postgres
createdb -p 5433 mydb && gunzip -c 2026-01-01_03.mydb.sql.gz | psql -p 5433 mydb
```


# Set up .pgpass for PostgreSQL backups

If your backups connect over TCP or as a user other than `postgres`, `pg_dump` needs a password. Put it in a `.pgpass` file **in the home folder of the user that runs the backup**, not in the script.

## Peer authentication first
If the backup runs as the `postgres` OS user over the local socket, peer authentication already works and you don't need `.pgpass` at all.

## Format
One line per server, port, database and user:
```
hostname:port:database:username:password
localhost:5432:*:backup:secret
localhost:5433:*:backup:secret
```

## Create it safely
Set a restrictive umask first, so the file is never briefly readable by others:
```
sudo -u postgres bash -c 'umask 077; cat >> ~/.pgpass' <<'EOF2'
localhost:5432:*:backup:secret
EOF2
sudo -u postgres chmod 600 ~/.pgpass
```
libpq ignores the file if its mode is wider than 0600.

## Common mistake
Writing `/root/.pgpass` while the cron job runs as `postgres` (home folder `/var/lib/pgsql`). The backup never sees that file. Check which user and home folder your job actually uses.

## Test
```
sudo -u postgres psql -h localhost -p 5433 -U backup -c 'select 1' postgres
```


# Back up an OpenProject (package install) to a NAS

The packaged OpenProject (DEB/RPM) has its own backup command. It writes a database dump plus archives of attachments, configuration and repositories.

## Run the built-in backup
```
sudo openproject run backup
ls /var/db/openproject/backup/
# a database dump (*.pgdump) plus *.tar.gz archives for attachments, config and repositories
```

## Copy it off the server and tidy up
```
#!/bin/bash
set -euo pipefail
SRC=/var/db/openproject/backup
DST=/mnt/backup/$(hostname -s)/openproject
mountpoint -q /mnt/backup
openproject run backup
mkdir -p "$DST"
rsync -a "$SRC"/ "$DST"/
find "$DST" -type f -mtime +7 -delete
# Clean the local copies too, including the big attachment archives
find "$SRC" -type f \( -name '*.pgdump' -o -name '*.tar.gz' \) -mtime +1 -delete
```
Make sure the local clean-up includes the `*.tar.gz` files. Otherwise the attachment archives build up in `/var/db` until the disk is full.

## How often?
A full backup includes every attachment, so daily is usually right:
```
0 2 * * * root /usr/local/bin/backup_openproject.sh >/dev/null 2>&1
```

## Restore
Follow the OpenProject documentation for "Restoring an installation". In short: stop the services, restore the `.pgdump` with `pg_restore`, unpack the attachments and configuration archives, then run `openproject configure`.


# Back up Docker app SQLite databases without stopping containers

Most self-hosted media apps (Plex, Jellyfin, Sonarr, Radarr, Lidarr, Prowlarr, Bazarr, Tautulli, Overseerr/Jellyseerr, SABnzbd, Calibre and others) keep their state in SQLite files under their config volume. Copying those files with `cp` or `tar` while the app is running can give you a corrupt copy. SQLite's **online backup** API makes a consistent copy while the app keeps running.

## The core idea
```
sqlite3 -readonly /data/sonarr/config/sonarr.db ".timeout 60000" ".backup '/tmp/sonarr.db'"
sqlite3 /tmp/sonarr.db "PRAGMA quick_check;"      # should print: ok
gzip -c /tmp/sonarr.db > /mnt/backup/$(hostname -s)/sonarr/$(date +%Y.%m.%d_%H).sonarr.db.gz
```
`.timeout` makes sqlite wait for the app's write locks instead of failing.

## A script around it
Keep a simple list of `app|path|check` entries at the top, for example:
```
DBS=(
  "plex|$DATA/plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db|0"
  "sonarr|$DATA/sonarr/config/sonarr.db|1"
  "radarr|$DATA/radarr/config/radarr.db|1"
)
```
The last field turns the integrity check on or off. Plex's database uses custom tokenizers that make `quick_check` report false errors, so set it to 0 for Plex.

Good habits worth building in:
- **Refuse to run if the backup share isn't mounted:** `mountpoint -q /mnt/backup || exit 1`. Otherwise backups fill the local disk.
- **Check free space** before starting (for example, require 10 GB).
- **Run gently** with `nice -n 19 ionice -c3`.
- **Prevent overlapping runs** with `flock -n`.
- **Write to `file.part`, then `mv`,** so a half-written backup never looks complete.
- **Also copy the app's own backup zips** (the *arr apps write them to `config/Backups`).
- **Add a `--list` option** that shows what would be backed up and writes nothing.
- **Keep N days and delete older copies:** `find ... -name '*.gz' -mtime +14 -delete`.

## Schedule
```
30 1 * * * root /usr/local/bin/backup_docker_dbs.sh >/dev/null 2>&1
```

## Restore
1. `docker stop <container>`
2. Move the live `app.db` aside, and remove `app.db-wal` and `app.db-shm`.
3. `gunzip -c <backup>.gz > app.db`
4. `chown <puid>:<pgid> app.db` (the IDs the container runs as).
5. `docker start <container>`


# Back up Docker container data folders (stop, archive, restart)

For apps whose data can't be copied safely while they are running, the simple, reliable method is: stop the container, tar its data folder, then start it again. Do this one container at a time, so each app is only down for a moment.

## Assumptions
Each container's data lives in `<base>/<container-name>/` (for example `/srv/docker/sonarr`), and backups go to a mounted share at `/mnt/backup`.

## The script
```
#!/bin/bash
set -uo pipefail                     # no -e: we must always restart the container
BASE=/srv/docker
DEST=/mnt/backup/docker/$(hostname -s)/$(date +%F)
KEEP=14
mountpoint -q /mnt/backup || { echo "backup share not mounted"; exit 1; }
mkdir -p "$DEST"
for c in $(docker ps --format '{{.Names}}'); do
  [[ -d $BASE/$c ]] || { echo "skip $c (no data folder)"; continue; }
  docker stop -t 120 "$c" >/dev/null || { echo "could not stop $c"; continue; }
  if tar -czf "$DEST/$c.tar.gz.part" -C "$BASE" "$c"; then
    mv "$DEST/$c.tar.gz.part" "$DEST/$c.tar.gz"
  else
    echo "backup of $c failed"; rm -f "$DEST/$c.tar.gz.part"
  fi
  docker start "$c" >/dev/null || echo "WARNING: $c did not restart"
done
find "${DEST%/*}" -type f -name '*.tar.gz' -mtime +$KEEP -delete
find "${DEST%/*}" -mindepth 1 -type d -empty -delete
```

## Why no `set -e`
With `set -e`, a failing `tar` (or tar's exit code 1 for "file changed while reading") ends the script **before `docker start`**, leaving the app down. Handle errors explicitly, or add a `trap` that restarts the current container.

## Skip containers whose folder isn't there
Check that the data folder exists **before** stopping the container. Otherwise a wrong `BASE` path means every container is stopped and started for nothing.

## Stacks with databases
If an app and its database are separate containers, stop the app first, then the database, back up both, and start them in reverse order. Or use a proper database dump; see *Back up Docker app SQLite databases without stopping containers*.

## Schedule
```
0 3 * * * root /usr/local/bin/docker_backup.sh >> /var/log/docker-backup.log 2>&1
```

## Restore
`docker stop app`, then `tar -xzf app.tar.gz -C /srv/docker`, then `docker start app`.


# Pull server backups onto a Synology NAS with rsync over SSH

Instead of letting servers write to the NAS, have the NAS **pull** from them. If a server is compromised, it then has no access to the backups.

## One-time SSH key setup
1. On the NAS, enable SSH (Control Panel → Terminal & SNMP).
2. Create a key for the account that runs the task:
   ```
   ssh-keygen -t ed25519 -f ~/.ssh/backup_pull -N ''
   ```
3. On the server, add the public key for a backup user. Restrict it if you can, for example `command="rrsync -ro /"` (rrsync ships with rsync).

## The script (on the NAS)
```
#!/bin/bash
set -uo pipefail
SRC=backup@<server>
DST=/volume1/<backup-share>/<server>
LOG=/volume1/<backup-share>/logs
mkdir -p "$LOG"
day=$(date +%A)
for path in /etc/httpd /var/www/html /opt; do
  rsync -a --delete -e "ssh -i ~/.ssh/backup_pull" \
    --log-file="$LOG/$(basename "$path")_$day.log" \
    "$SRC:$path/" "$DST$path/" || echo "pull of $path failed"
done
find "$LOG" -type f -name '*.log' -mtime +7 -delete
```
- The first line must be `#!/bin/bash`. Without the `!` it is just a comment, and the script runs in whatever shell starts it.
- Test folders with `[ -d path ]`. A bare `[ path ]` is always true.

## Schedule
Control Panel → Task Scheduler → Create → Scheduled Task → User-defined script. Run it as the user that owns the key.

## A mirror is not a backup
`--delete` makes the NAS copy an exact mirror. A file deleted or encrypted on the server disappears from the NAS on the next run. Choose one of:
- Turn on **Btrfs snapshots** for the shared folder (Snapshot Replication); this is the easiest fix.
- Use `rsync --link-dest=<yesterday> ...` into dated folders.
- Use Hyper Backup on top of the mirror.