First I created a cron job to dump the databases running on production servers in SQL format.

# On the target machine
$ mkdir -p /var/cache/sql
$ cat /etc/cron.d/backups
# postgresql
30 3          * * *           root /usr/bin/pg_dump -U username -w -f /var/cache/sql/database.sql database
# mysql
30 3          * * *           root /usr/bin/mysqldump --databases database -u root --password=rootpasswd > /var/cache/sql/database.sql
$ chmod 600 /etc/cron.d/backups

On another (backup) machine, I ran rsnapshot.

# On the backup machine
$ apt-get install rsnapshot
$ ssh-keygen -q -t rsa -P "" -f /etc/ssl/private/backup_rsa
$ diff -u prev /etc/rsnapshot.conf
-#cmd_cp		/bin/cp
+cmd_cp		/bin/cp
 
-#cmd_ssh	/usr/bin/ssh
+cmd_ssh	/usr/bin/ssh
 
-#ssh_args	-p 22
+ssh_args	-i /etc/ssl/private/backup_rsa

+backup	backup@hostname:/etc/	hostname/
+backup	backup@hostname:/var/log/	hostname/
+backup	backup@hostname:/var/www/	hostname/
+backup	backup@hostname:/var/cache/sql/	hostname/

At first, I was thinking to use a duplicate account for root (uid=0,gid=0) with a shell restricted to scponly as suggested in rsnapshot HOWTO

# On the target machine
$ apt-get install scponly
$ useradd -o --uid 0 --gid 0 --shell /usr/bin/scponly backup
$ mkdir -p /home/backup/.ssh
$ echo "PUBKEYDATA" >>  /home/backup/.ssh/authorized_keys
$ chmod 644 /home/backup/.ssh/authorized_keys
$ chown -R backup /home/backup/.ssh
$ chmod 700 /home/backup/.ssh

Unfortunately the first attempt to connect as backup is rejected because I have disable root logins through sshd for security reasons.

# On the backup machine
$ rsync -Raz --rsh="ssh -i /etc/ssl/private/backup_rsa" \
  		backup@hostname:/etc .
sshd: ROOT LOGIN REFUSED FROM hostname

Since I am not to permit root logins through sshd, I need to find another solution. I decided to enable sudo with no password for backup when executing /usr/bin/rsync. It seems less of a security risk as backup does not have to be a well-known username.

Note: Whenever editing the /etc/sudoers file, always make sure to have two shell connection; one with the /etc/sudoers file open and one where you can test sudo commands. If there are a syntax error in the /etc/sudoers and root logins are disabled, you might just get stuck and resort to safe boot voodoo to get out of the mess.

# On the target machine
$ useradd backup
$ mkdir -p /home/backup/.ssh
$ echo "PUBKEYDATA" >>  /home/backup/.ssh/authorized_keys
$ chmod 644 /home/backup/.ssh/authorized_keys
$ chown -R backup:backup /home/backup/.ssh
$ chmod 700 /home/backup/.ssh

$ diff prev /etc/sudoers
-Defaults requiretty
+#Defaults requiretty

+backup   ALL=(ALL:ALL) NOPASSWD: /usr/bin/rsync

# On the backup machine
$ diff -u prev /etc/rsnapshot.conf
-#rsync_long_args	--delete --numeric-ids --relative --delete-excluded
+rsync_long_args	--delete --numeric-ids --relative \
					--delete-excluded --rsync-path "sudo /usr/bin/rsync"

$ rsnapshot -t hourly