Linux – One-line backup, compress, scp database file

backupcronlinuxpostgresqlssh

I support a company with a remotely hosted web site. They use PostgreSQL dbs.

I can't write scripts on the remote server, and I don't want to store any backup files on the remote server, but I can run cron jobs, and I have access to the remote server's SSH public key. I set up a local linux host to accept the remote host's private key for authentication.

I need to make a one-line script to backup, compress, and scp the backup file to my local linux box synchronously.

If my local linux box was reachable via SSH at mylinuxbox.foo.com on port 122, and my database's name was mydatabase, what would this one-liner cron job command look like?

Best Answer

Assuming you're using ident authentication (or a .pgpass) for the database I'd probably do something like the following :

pg_dump -Udbuser -h127.0.0.1 mydatabase |gzip -c > mydatabase.backup.gz; scp -P 122 mydatabase.backup.gz user@mylinuxbox.foo.com:; rm -f mydatabase.backup.gz

Editing to add another option...

If it's really important to not have intermediate storage of the backup file you could modify the above to look more like this:

pg_dump -Udbuser -h127.0.0.1 mydatabase |gzip -c | ssh -p 122 user@mylinuxbox.foo.com "cat >mydatabase.backup.gz"
Related Topic