First you will need to set up your ssh server so that it accepts RSA keys, instead of a passphrase. This is documented elsewhere. Then you will need to set up the account that you plan to use to have the appropriate DSA/RSA key, which will allow that account to auto-login.
* ssh to the production server
This is not necessary. You can directly execute commands on the remote server using the
-C option. For example, the next two steps you mention...
* gzip our daily database dump file
* scp the gzipped dump file over to my computer
...would look similar to this:
ssh email@example.com -C 'gzip dump > dump.gz'
scp firstname.lastname@example.org:/path/to/dump/dump.gz /path/to/destination/dump.gz
Place each command just mentioned, along with the rest of the commands you are manually typing, into a single file. The commands must be in the same sequence that you would normally type them in as. We'll call it
dump-script.sh for this example. Change the ownership and permissions as needed, so that the file will execute.
There is a very high possibility of data loss occuring if you do not perform error checking in your script. Until you add error handling to the script, there is no guarantee that it will work correctly 100% of the time. With the command to drop your database, it is quite easy under the right circumstances to loose all of the data on the machine that is receiving the dump. You will most likely want to expand and embellish your script to allow for error detection, so that it will only DROP DATABASE and import the new data when the transfer is successful. If you are not comfortable with this, do not run it, nor create the crontab entries.
Once you have your script debugged and you can confirm that it will handle errors correctly, you can consider adding it to a regular cron schedule. While logged in as the account you wish to use that will perform these actions, type:
....which should get you an editor. Create an entry like this:
# Nightly Database Dump, at Midnight
0 0 * * * /path/to/my/dump-script.sh
:wq in that order, and press enter (assuming that
vi is your default text editor).
A better solution:
If you have network access to the PostgreSQL database, why not just dump the database over the network using a remote connection? This is much cleaner, and can be combined with a little bit of scripting to make it a direct 'pipe' of data from one to the other.