Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Currently, every day before I start work, I complete the following procedure:

  • ssh to the production server
  • gzip our daily database dump file
  • scp the gzipped dump file over to my computer
  • gunzip the dump file
  • dropdb mydatabase
  • createdb mydatabase
  • psql mydatabase < dump.sql

Is it possible (I'm sure it is) to automate this process on Mac OSX? This way it is done by the time I get to work in the morning. If so, what is the quickest and easiest way

share|improve this question
    
What database server? Based on the mention of psql I'm guessing its Postgres but we shouldn't have to guess. Please tag appropriately. –  John Gardeniers May 8 '10 at 2:33

3 Answers 3

up vote 1 down vote accepted

To allow ssh login to your production server without you typing the password you'll need to set up some non-interactive authentication if it's not already set up.
http://www.walkernews.net/2008/06/06/how-to-setup-non-interactive-ssh-login/
http://troy.jdmz.net/rsync/index.html

Then it's basically a matter of adding the commands for each step to a shell script and setting cron to run that shell script every morning.

share|improve this answer
    
as i mentioned in the other answer, I couldnt find a /etc/crontab file on the mac, is it in a different location? –  Russ Bradberry May 7 '10 at 21:08
    
"crontab -e" should hopefully create one for you. –  Loopo May 13 '10 at 15:56

Yeah - script and schedule the gzip/scp (or better yet, make it rsync) portion from your server. If it's also MacOS, I'm not sure of what the detailed steps to do that are, but that's the outline of what you'd want.

Then, if you know it will always be done by a certain time, just schedule a script on your computer to gunzip/dropdb/createdb/psql. If it's not always done by the same time, then you could do something fancy with checking file status, modified times, etc.

What parts of the above are you unsure of, or feel you need more details with? Are you new to scripting, or not sure how to schedule things with cron or what-have-you?

Does PostGRES support any sort of log-shipping or mirroring natively? If so, that might be a better all-in-one solution.

share|improve this answer
    
im not sure if postgres does log shipping or mirroring natively. i couldnt find a crontab on the mac, we use crontabs on our servers and that would seem like an ideal solution but im not sure how to begin scripting that. –  Russ Bradberry May 7 '10 at 21:05

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 user@some.server.net -C 'gzip dump > dump.gz'
scp user@some.server.net:/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.

WARNING:

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:

crontab -e

....which should get you an editor. Create an entry like this:

# Nightly Database Dump, at Midnight
0 0 * * * /path/to/my/dump-script.sh

...then type :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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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