nstanger / nix

0 stars 0 forks source link

Database backups (launchd) #2

Open nstanger opened 5 months ago

nstanger commented 5 months ago

Original cron jobs:

# Run database backup scripts.
30  1   *   *   *   $HOME/bin/automysqlbackup.sh > /dev/null
30  1   *   *   *   $HOME/bin/autopgsqlbackup.sh > /dev/null

# Once a day rsync the contents of ~/Backups/Databases with the server.
# Note: -p doesn't seem to work any more as of 2016-03-31, but it's not essential anyway.
# (Implication: can't use -a.)
45  1   *   *   *   /usr/bin/rsync -rltgoDv --delete /Users/<localuser>/Documents/Backups/Databases/ /System/Volumes/Data/mnt/shares/<serveruser>/Backup/Databases

Containerised database servers will complicate the initial backup step somewhat. They will need to run within the container and expose the file(s) via a shared volume.

automysqlbackup is in nixpkgs, but autopgsqlbackup was my own personal hack to make it work for PostgreSQL. Someone has now created autopostgresqlbackup, but it’s not in nixpkgs: https://github.com/k0lter/autopostgresqlbackup.

The rsync to the server will need some modification as there will potentially be multiple sources to rsync. It shouldn’t be too difficult, though.

nstanger commented 5 months ago

Actually the initial step should be fairly straightforward, just point the scripts at the correct connection details. May need separate script instances for different database containers, though.

But thinking about it further, both scripts are available as Debian packages, so they could just be installed inside each container and output to a shared volume.

nstanger commented 5 months ago

Hmm…

root@8bf563428758:/# apt install autopostgresqlbackup
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  biabam ca-certificates cron cron-daemon-common exim4-base exim4-config exim4-daemon-light gsasl-common guile-3.0-libs libevent-2.1-7 libfribidi0
  libgc1 libgnutls-dane0 libgpm2 libgsasl18 libgssglue1 libidn12 libltdl7 libmailutils9 libmariadb3 libncurses6 libntlm0 libunbound8 mailutils
  mailutils-common mariadb-common mysql-common psmisc sharutils
Suggested packages:
  bzip2 file anacron logrotate checksecurity exim4-doc-html | exim4-doc-info eximon4 spf-tools-perl swaks dns-root-data gpm mailutils-mh
  mailutils-doc sharutils-doc
The following NEW packages will be installed:
  autopostgresqlbackup biabam ca-certificates cron cron-daemon-common exim4-base exim4-config exim4-daemon-light gsasl-common guile-3.0-libs
  libevent-2.1-7 libfribidi0 libgc1 libgnutls-dane0 libgpm2 libgsasl18 libgssglue1 libidn12 libltdl7 libmailutils9 libmariadb3 libncurses6 libntlm0
  libunbound8 mailutils mailutils-common mariadb-common mysql-common psmisc sharutils
0 upgraded, 30 newly installed, 0 to remove and 8 not upgraded.
Need to get 14.2 MB of archives.
nstanger commented 5 months ago

But it’s potentially more complex to extract the backups when running the script inside the container because the container runs inside a VM on macOS. That pretty much necessitates bind mounts rather than just accessing the Docker volume directly.

nstanger commented 5 months ago

Aha, autopostgresqlbackup supports PostgreSQL, MariaDB and MySQL, and you can specify the config at runtime (https://github.com/k0lter/autopostgresqlbackup/blob/master/Documentation.md). It’s also under more active development.

Problem(s) solved, or at least rendered irrelevant. Run on the host instead of the container with a separate launchd job for each server with corresponding configs (which can probably go into the nix store). Just need to figure out the structure of the outputs so that they can be rsync’d elsewhere.

nstanger commented 5 months ago

Need version 2.1 (not yet released) for MySQL.