Schedule PostgreSQL and MySQL dumps with systemd timers

A systemd timer does what a cron line does, with three advantages: the output lands in the journal with the service name attached, Persistent=true runs a missed job after a reboot, and systemctl list-timers shows you exactly when the next dump will happen. This guide builds a single script that dumps every PostgreSQL and MySQL database on the host, then wires it to a nightly timer.

Step 1: credentials without passwords in the script

Run the service as root and let each database authenticate the usual way. PostgreSQL on Debian and Ubuntu trusts the local postgres system user through peer authentication, so the script switches to that user with sudo -u postgres. For MySQL and MariaDB put the credentials in /root/.my.cnf, readable only by root:

[client]
user=backup
password=change-me

sudo chmod 600 /root/.my.cnf
sudo mysql -e "CREATE USER 'backup'@'localhost' IDENTIFIED BY 'change-me'; GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';"

A dedicated read-only backup user means a leaked dump credential cannot modify data. Test both logins before going further: sudo -u postgres psql -c 'select 1' and sudo mysql -e 'select 1'.

Step 2: the dump script

Save this as /usr/local/bin/db-dump.sh and make it executable with chmod 755. It writes one file per database plus the PostgreSQL globals (roles and tablespaces), compresses them, and removes dumps older than 14 days.

#!/usr/bin/env bash
set -euo pipefail

DEST=/var/backups/db
KEEP_DAYS=14
STAMP=$(date +%F_%H%M)
mkdir -p "$DEST/postgresql" "$DEST/mysql"

if command -v pg_dump >/dev/null && systemctl is-active --quiet postgresql; then
  sudo -u postgres pg_dumpall --globals-only | gzip > "$DEST/postgresql/globals_$STAMP.sql.gz"
  for db in $(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
    sudo -u postgres pg_dump --format=custom --file "$DEST/postgresql/${db}_$STAMP.dump" "$db"
    echo "postgresql: $db dumped"
  done
fi

if command -v mysqldump >/dev/null && (systemctl is-active --quiet mysql || systemctl is-active --quiet mariadb); then
  for db in $(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
    mysqldump --single-transaction --routines --triggers --events "$db" | gzip > "$DEST/mysql/${db}_$STAMP.sql.gz"
    echo "mysql: $db dumped"
  done
fi

find "$DEST" -type f -mtime +"$KEEP_DAYS" -print -delete
echo "dump finished: $(du -sh "$DEST" | cut -f1) in $DEST"

• pg_dump --format=custom produces a compressed archive that pg_restore can restore selectively (one table, schema only, data only).
• pg_dumpall --globals-only saves roles and passwords, which per-database dumps do not contain.
• mysqldump --single-transaction gives a consistent snapshot of InnoDB tables without locking them; --routines --triggers --events include stored code that is skipped by default.
• Careful: the find ... -delete line permanently removes every file under $DEST older than KEEP_DAYS. Run the script once with -print only (remove -delete) if you are unsure what it will match.

Run it by hand once: sudo /usr/local/bin/db-dump.sh. Fix any authentication error now, while you can see it.

Step 3: the service and the timer

Create /etc/systemd/system/db-dump.service:

[Unit]
Description=Dump PostgreSQL and MySQL databases
After=postgresql.service mysql.service mariadb.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-dump.sh
Nice=10
IOSchedulingClass=best-effort
IOSchedulingPriority=7

And /etc/systemd/system/db-dump.timer:

[Unit]
Description=Nightly database dump

[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=15min
Persistent=true

[Install]
WantedBy=timers.target

• Type=oneshot tells systemd the service runs to completion rather than staying up.
• OnCalendar=*-*-* 02:30:00 is every day at 02:30; check any expression with systemd-analyze calendar '*-*-* 02:30:00'.
• Persistent=true runs the job at boot if the machine was off at 02:30.
• RandomizedDelaySec spreads the start so several hosts do not hit shared storage in the same second.
• Nice and the IO scheduling settings keep the dump from starving the database it is dumping.

Step 4: enable it and check

sudo systemctl daemon-reload
sudo systemctl enable --now db-dump.timer
systemctl list-timers db-dump.timer
sudo systemctl start db-dump.service
journalctl -u db-dump.service -n 50 --no-pager
ls -lh /var/backups/db/postgresql /var/backups/db/mysql

list-timers shows NEXT and LAST; starting the service (not the timer) runs the dump immediately so you do not have to wait until 02:30. The journal shows every line the script printed, and a non-zero exit marks the unit as failed, which the Server health check script and most monitoring agents pick up through systemctl --failed.

Keep the dumps off the server

Dumps on the same disk as the database protect you from a bad DELETE, not from a dead disk. Add a second timer, or a second line in the script, that pushes /var/backups/db to another machine with rsync, rclone or a restic repository. Then verify the newest file with the verify-latest-backup script and restore one dump a month as described in the restore drill article.

Official documentation

• systemd.timer
• PostgreSQL: pg_dump
• MySQL: mysqldump