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?

  • Can you access the database remotely? If so might be easier to write a script on the local box to pull the data instead of trying to push it from the remote side. – Grant Dec 6 '13 at 13:31
  • There are some provisions for shell access, but I'd rather it be a push operation if I can swing it. – Jeremy Holovacs Dec 6 '13 at 15:43
  • Have you read the section of the Postgres manual dealing with backup and restore? If not, stop now and go read it. Then all you need to do is wrap those instruction sin a shell script and run it from cron. – voretaq7 Dec 6 '13 at 16:00

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.