I found my solution and post it here for the case someone else needs something similar:
I created a crontab job like this:
0 0,6,12,18 * * * rm -rf /home/cockroach/cockroach-data/extern/* && /usr/local/bin/cockroach sql -e "BACKUP DATABASE vaccinator INTO 'backup' IN 'nodelocal://1/';" --insecure --host=10.0.0.10 && zip -q -r /backups/crdb-$(date +\%F-\%H\%M).zip /home/cockroach/cockroach-data/extern/backup/
5 * * * * find /backups/*.zip -mtime +1 -delete 2> /dev/null
As you can see this is using my node ID 1 at 10.0.0.10. Adapt to your needs.
This is what it is doing at 00:00, 06:00, 12:00 and 18:00:
- Cleaning up previous backup data in
/home/cockroach/cockroach-data/extern/ (Warning: Modify this if you need to use the extern folder for other purposes, too).
- Making a full backup into a “backup” folder in your
- Zip this backup folder into a single file (with date/time) to a local
/backups mount (or folder).
- Remove all zip files from
/backups that are older than 1 day (backup retention).
By this, the
/backups mount always contains the last four backups.
To restore such, you can do like this:
rm -rf /home/cockroach/cockroach-data/extern/*
unzip /backups/<yourBackupFile>.zip -d /
chown -R cockroach:cockroach /home/cockroach/cockroach-data/extern/
Then you can RESTORE the backup like this:
cockroach sql -e "RESTORE DATABASE <db_name> FROM 'backup' IN 'nodelocal://1/';" --insecure --host=10.0.0.10
Please note that I did all with --insecure for my test environment. You may adapt to use certificates for production.
Please also note that you might need to drop the database <db_name> first, if it already exists. Otherwise you get an error because you can’t restore a database that is already there.