I have database servers outside the homelab, as in not in my basement. They are in datacenters. I don’t let them push the backups into the basement. Instead, I let them call home asking for the backups to be picked up. I prefer it that way. As I describe it, it may seem complex to do multiple steps when one step will do. However, this solution promises that the backups are ready and you’re not pulling back a half-completed backup.The process is:
- backup the database
- call home for pickup
- the pickup (pull) happens
- profit
In this post:
- FreeBSD 15.0
- rsync-3.4.1
- you’ll learn how I use passphrase ssh keys in a secure manner to accomplish backups on hosts and copy them to another
- r720-02 is the jail host
- pg01 runs a PostgreSQL instance with databases we will backup
- dbclone is the destination of the backups – that’s where they are pulled to
Some background details
For these examples, you might be able to skip over this section. In it, I talk about where the database is, there the backup goes, and who initiates the backup. You don’t have to do it my way, but I find it more convenient.
I usually run the database software in a jail, completely separate from any other components. I run the backups from the jail host, not within the jail. I find this makes it easier to script the backup rsyncs. Mostly that is because my jails cannot contact the home lab in the basement, but the host can. While this sounds more complex, I find it easier to do it this way. Add another jail to be backed up, modify the rsyncer host on the jail host. No need for each jail to have its own rsyncer user.
With more detail provided later in this post, that jail host will have more than one ssh key which is used to initiate a database pull. You might wonder why do it that way? Each passphrase ssh key is tied to a specific single task. That’s the only thing that ssh key can do. That restriction is part security and part using authorized_keys to the fullest.
It is because there are multiple-keys-per host that the keys have non-trivial names. It makes it easier to confirm their intended use.
Hope that helps to clarify what I’m doing and does not add confusion.
The rsyncer user
For both security and simplicity, I create a new user, rsyncer, which:
- has read-only access to the database – how I did that
- Can ssh to the backup destination host
Creation of this user is outside scope. There are many guides available.
The database dump
How the database is dumped to disk is outside scope and is not the primary purpose of this post. That’s something you have to create or pull from an example. That said, my simple script appears later (search for The backup script).
In my case, the database dumps are at ${HOME}/backups/database-backup for the user in question (rsyncer).
In this example, it’s PostgreSQL. It works with MySQL too (if you must use it).
ssh keys
For my solution, the local user (where the database backup is created) and the remote user (where the database backup is copied to) both need their own respective passphrase-less ssh keys. In general, a key without a passphrase are a bad idea. Except for when that’s exactly what you need. In my case, the keys will be tightly restricted as to what they can do. See anvil – copying the certificates to the website for similar uses.
The following paste shows the creation of the local user ssh-key. Note that I’m using the new-to-me -C option (thanks to https://jpmens.net/2026/04/03/ssh-certificates-the-better-ssh-experience/). The value shows up in the public key file. That precise comment will be useful when it appears in the ~/.ssh/authorized_keys file in other jails.
I’m also using a very specific file name, helpful when used in scripts, etc.
[rsyncer@r720-02 ~/.ssh]$ ssh-keygen -C "r720-02-pg01 copy to dbclone" -f id_ed25519.rsync.r720-02-pg01.copy.to.dbclone Generating public/private ed25519 key pair. Enter passphrase for "/home/rsyncer/.ssh/id_ed25519.rsync.r720-02-pg01.copy.to.dbclone" (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/rsyncer/.ssh/id_ed25519.rsync.r720-02-pg01.copy.to.dbclone Your public key has been saved in /home/rsyncer/.ssh/id_ed25519.rsync.r720-02-pg01.copy.to.dbclone.pub The key fingerprint is: SHA256:Sdu5fsmd/sJ5VJftW348aGG/EZxiSdAgRBtHnEBQxMM r720-02-pg01 copy to dbclone The key's randomart image is: +--[ED25519 256]--+ | .XX+=+ | | E=o.. | | ... . o| | . + .. o.=| | S o + =o| | ..o. =| | ...++Bo| | . +o*+*| | .....==| +----[SHA256]-----+ [rsyncer@r720-02 ~/.ssh]$ ls -l total 27 -rw------- 1 rsyncer rsyncer 419 Apr 11 21:02 id_ed25519.rsync.r720-02-pg01.copy.to.dbclone -rw-r--r-- 1 rsyncer rsyncer 110 Apr 11 21:02 id_ed25519.rsync.r720-02-pg01.copy.to.dbclone.pub -rw------- 1 rsyncer rsyncer 1405 Apr 5 2024 known_hosts [rsyncer@r720-02 ~/.ssh]$ cat id_ed25519.rsync.r720-02-pg01.copy.to.dbclone.pub ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIBpQDHQdyau7iWqrf1HjJwTG1CcnfGtZrra/48bsyW1z r720-02-pg01 copy to dbclone
I won’t show the creation of the remote user ssh key, mostly because it already exists.
The backup script
Initially, I wasn’t going to show the backup script. I now think it is better to show the pg_dump and the rsync to help demonstrate all the steps.
[rsyncer@r720-02 ~]$ cat ~/bin/backup-pg01.sh
#!/bin/sh
PGDUMP='/usr/local/bin/pg_dump -h 127.163.54.32'
PGDUMPALL='/usr/local/bin/pg_dumpall -h 127.163.54.32'
BACKUPROOT=${HOME}/backups/pg01/database-backup
cd ${BACKUPROOT}/postgresql
DATABASES="freshports.org"
for database in ${DATABASES}
do
echo dumping $database
if [ -f ${database}.dump ]
then
mv ${database}.dump archive
fi
${PGDUMP} -Fc ${database} -f ${database}.dump
if [ $? -ne 0 ]
then
echo backup of ${database} failed
fi
done
#
# Now dump the globals
#
if [ -f globals.sql ]
then
mv globals.sql archive
fi
$PGDUMPALL -g -f globals.sql
if [ $? -ne 0 ]
then
echo backup of globals failed
fi
# now call home to start the database pickup (pull)
#
/usr/bin/ssh -i ${HOME}/.ssh/id_ed25519.rsync.r720-02-pg01.copy.to.dbclone dbclone.int.unixathome.org
That last line is the call home. That is the line which causes the database dump to be pulled into the basement. We’ll learn more about that in the next section.
The call home
The call home occurs after the database backup has completed. The backup job is initiated by this cron job.
[rsyncer@r720-02 ~]$ cat /usr/local/etc/cron.d/rsyncer
# use /bin/sh to run commands, overriding the default set by cron
SHELL=/bin/sh
# mail any output to here, no matter whose crontab this is
MAILTO=dan@langille.org
# take a local copy of the bacula stuff
#minute hour mday month wday who command
2 2 * * * rsyncer /usr/bin/lockf -t 0 /tmp/.rsync.backup-pg01.sh ${HOME}/bin/backup-pg01.sh >> /var/log/rsync-backup-pg01.log
Of note:
- the use of lockf ensures one backup job completes before the next one starts – that scenario is unlikely, and this simple step prevents it from happening
- the last line of ~/bin/backup.sh does the call home
That last line looks like this:
[rsyncer@r720-02 ~]$ tail -1 ~/bin/backup-pg01.sh
/usr/bin/ssh -i ${HOME}/.ssh/id_ed25519.rsync.r720-02-pg01.copy.to.dbclone dbclone.int.unixathome.org
I call that line the call home step.
That line says: ssh, using the indicated key (-i), to dbclone.int.unixathome.org. That’s it. Just ssh. Nothing else. Just ssh into the host. Don’t run a command when you get there. Just connect.
So what use it that? You’re connected to the server. How does the pull happen?
In the next section, I’ll show you what what happens and how it invokes a script to pull the database to the remote host.
For my own notes, that r720-02 host needs this entry in /etc/hosts because the required DNS is not available here:
10.55.0.140 dbclone.int.unixathome.org
The remote host
On the remote host (in this case, dbclone.int.unixathome.org), the ~/.ssh/authorized_keys file has this distinct entry. By distinct, I mean specific to one purpose: copying the backups from the pg01 jail to the remote host.
In the following, I will grep for the ssh key created above in the ssh keys section.
[rsyncer@dbclone ~]$ grep AAAAC3NzaC1lZDI1NTE5AAAAIBpQDHQdyau7iWqrf1HjJwTG1CcnfGtZrra ~/.ssh/authorized_keys from="r720-02.startpoint.vpn.unixathome.org,10.8.1.140",command="/home/rsyncer/bin/rsync.pg01.from.r720-02.sh" ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIBpQDHQdyau7iWqrf1HjJwTG1CcnfGtZrra/48bsyW1z r720-02-pg01 copy to dbclone
This entry allows the sending host to connect. It also tightly controls what that sending host can do. Let’s examine that line closely.
- r720-02.startpoint.vpn.unixathome.org,10.8.1.140 – For a connection from that hostname or IP address (see from=”pattern-list” in the man page)
- command=”/home/rsyncer/bin/rsync.pg01.from.r720-02.sh – invoke the shown command if, and only if…
- ssh-ed25519 … – the presented ssh key matches the public key at the end of the line
This is why the sending host does not specify a command on the ssh connection. It lets the other host do that.
That is the only thing that ssh-key can do (on this host, with that authorized_keys configuration): run that command. Nothing else. That is why / how the source host only needs to invoke ssh and connect. The destination host then takes over and runs the required command. This is how the risk of using a passphrase ssh-key is tightly controlled. I will use that key only with this host and, thus, only with that command. Nothing else. That reduces the attack vector to an acceptable-to-me level of risk.
For more information on this stringent use of an ssh-key, perhaps Using ~/.ssh/authorized keys to decide what the incoming connection can do will help.
The remote command
Now we’re still on dbclone, the destination / remote host.
This is the remote command (remote, relative to the source of the backup) which is run when the call home step is invoked. This script is referenced in the authorized_keys file referenced above.
NOTE: The rsyncer user is used at both ends of the process: on one host, it does the call home. On the other host, it does the pull. This is that latter host. This is how that pull occurs.
[rsyncer@dbclone ~]$ cat /home/rsyncer/bin/rsync.pg01.from.r720-02.sh
#!/bin/sh
#
# This file obtains a backup of each database on the server.
# It relies upon a file on the server to do the actual backup,
# then uses rsync to copy the files from the server to here.
#
# the ~/.ssh/authorized keys entry on the other server must look like this:
#
# from="10.55.0.140",command="/usr/local/sbin/rrsync /usr/home/backups/" [ssh key goes here]
#
# invoking rrsync ensures the incoming rsync process is chrooted to /usr/home/backups/
# BACKUPDIR is used
BACKUPDIR=${HOME}/backups/r720-02-pg01
IDENTITY_FILE_RSYNC=${HOME}/.ssh/id_ed25519.rsync.pg01.from.r720-02
SERVER_TO_RSYNC=r720-02.vpn.unixathome.org
# I bet this cd is not necessary
cd ${BACKUPDIR}
#
# use rsync to get local copies
# from / on the remote host
# put them into ${BACKUPDIR} here
#
/usr/local/bin/rsync -e "/usr/bin/ssh -i ${IDENTITY_FILE_RSYNC}" --recursive -av --stats --progress --exclude 'archive' ${SERVER_TO_RSYNC}:/ ${BACKUPDIR}
That last line, in short, says:
- use rsync …
- over an ssh session invoked using that ssh-key
- to pull data from the server
- into our backup directory
That’s it. The backup is pulled down onto the host.
But what, the real magic is back on the source host, the one with the backup waiting to be pulled.
The ssh-key referred to by that script
In the previous section, I presented a script which pulls the data from the source host to the remote host (whose name is dbclone). In that script, it refers to this newly-created ssh-key.
[rsyncer@dbclone ~/.ssh]$ ssh-keygen -C "dblone copy pg01 from r720-02" -f ~/.ssh/id_ed25519.rsync.pg01.from.r720-02 Generating public/private ed25519 key pair. Enter passphrase for "/home/rsyncer/.ssh/id_ed25519.rsync.pg01.from.r720-02" (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/rsyncer/.ssh/id_ed25519.rsync.pg01.from.r720-02 Your public key has been saved in /home/rsyncer/.ssh/id_ed25519.rsync.pg01.from.r720-02.pub The key fingerprint is: SHA256:deD49CKs08YoLcxjQD7qwftx50+h0UqpAK8svQ/sRzU dblone copy pg01 from r720-02 The key's randomart image is: +--[ED25519 256]--+ | . | | o . | | .. . + . | | oo E.o+ o | | +o . =Soo . | |o+.=o.o=+... | |o=+oBo=++. | |o.o+++oo. | | o++. ... | +----[SHA256]-----+ [rsyncer@dbclone ~/.ssh]$ cat /home/rsyncer/.ssh/id_ed25519.rsync.pg01.from.r720-02.pub ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAILEDbqveNb7IWGFQmRBEpoA3J1zL5txsimWucPY3+J9m dblone copy pg01 from r720-02
This key will be used on the source host to allow the remote host to pull down the backup.
What if you need to do something different on a call home?
This section is an aside and explains I use different ssh keys when connecting to a given host depending upon the task at hand.
Consider this scenario. I already have an ssh key for rsyncer, and it is tied to another task (rsyncing my Bacula database backup). How can I easily create another call home which does something else?
Solution: create another ssh key and use that one in the call home. Give it a unique entry in the ~/.ssh/authorized_keys file, with a different value for command.
Here’s my example, which I created just now, on the sending / source host.
On the receiving host, I have two entries for the source host:
[rsyncer@dbclone ~]$ grep r720-02 ~/.ssh/authorized_keys from="r720-02.startpoint.vpn.unixathome.org,10.8.1.140",command="/usr/local/sbin/rrsync -ro /usr/home/rsyncer/backups/bacula-database/postgresql/" ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAILb6Z+Gt6wN8YP8XSQPFp2KgyNcd7CZiW+s4ghMbKnk4 rsyncer@r720-02.unixathome.org from="r720-02.startpoint.vpn.unixathome.org,10.8.1.140",command="command="/home/rsyncer/bin/rsync-backup-for-r720-02-pg01.sh ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIBpQDHQdyau7iWqrf1HjJwTG1CcnfGtZrra/48bsyW1z r720-02-pg01 copy to dbclone
That’s straight forward. Now, back to our backup pull.
Back on the source host
Back on the source host (r720-02), we have another ~/.ssh/authorized_keys file with magic even more exotic than the above.
It looks like this:
[rsyncer@r720-02 ~/.ssh]$ cat authorized_keys from="dbclone.int.unixathome.org,10.55.0.140",command="/usr/local/sbin/rrsync /usr/home/rsyncer/backups/database-backup/pg01" ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAILEDbqveNb7IWGFQmRBEpoA3J1zL5txsimWucPY3+J9m dblone copy pg01 from r720-02
Here’s my explanation of this fantastical directive. It is similar to the entry on the destination ~/.ssh/authorized_keys, except that this one invokes the command /usr/local/sbin/rrsync.
Note: that is rrsync, with two r’s, not the rsync command.
That command is also installed by the rsync package, or a flavor thereof:
[rsyncer@r720-02-pg01 ~]$ pkg which /usr/local/sbin/rrsync /usr/local/sbin/rrsync was installed by package rsync-python-3.4.1_6
Effectively, it chroots the rsync command to the supplied directly. This means the remote user can only pull files from within that directory, and not outside. Very nice..
The test
Now that I’ve written this post while setting up a new host (r720-02-pg01), it’s time to see if it works. If it does, I’ll use this post to set up the next host (r720-02-pg02) and see if that works. With the corrections and additions from those two run-throughs, this blog post will be ready to publish.
… yes, it’s ready now. I’ve deployed this approach to two different hosts covering four database servers. These are those files.
[17:29 dbclone dvl ~rsyncer/backups] % find r720-02-pg0? zuul-pg0? r720-02-pg01 r720-02-pg01/database-backup r720-02-pg01/database-backup/postgresql r720-02-pg01/database-backup/postgresql/freshports.org.dump r720-02-pg01/database-backup/postgresql/globals.sql r720-02-pg02 r720-02-pg02/database-backup r720-02-pg02/database-backup/postgresql r720-02-pg02/database-backup/postgresql/globals.sql r720-02-pg02/database-backup/postgresql/freshports.org.dump zuul-pg01 zuul-pg01/database-backup zuul-pg01/database-backup/postgresql zuul-pg01/database-backup/postgresql/globals.sql zuul-pg02 zuul-pg02/database-backup zuul-pg02/database-backup/postgresql zuul-pg02/database-backup/postgresql/globals.sql zuul-pg02/database-backup/postgresql/pgcon.dump zuul-pg02/database-backup/postgresql/bsdcan.dump zuul-pg02/database-backup/postgresql/bsdcan.beta.dump zuul-pg02/database-backup/postgresql/postgres.dump zuul-pg02/database-backup/postgresql/postgresqleu.dump zuul-pg02/database-backup/postgresql/postgresqleu_bsdcan.dump zuul-pg02/database-backup/postgresql/pgcon.beta.dump zuul-pg02/database-backup/postgresql/bugtracker.dump zuul-pg02/database-backup/postgresql/postgresqleu_pg02.dump zuul-pg02/database-backup/postgresql/freebsddiary.org.dump
Hope this helps. Worst case, this is here for me when I do it again. :)
