This is my crontab:

crontab -e
30 5 * * * sh /home/donato/Documents/pg_bak.sh

Every day at 5:30am I want to execute a shell script I created called pg_bak.sh.

This is the contents of the shell script:


DATE=$(date +"%m_%d_%y")
# pass @ .pgpass

  pg_dump -h $SERVER -U $PG_USER $DATABASE -f $FILE.sql  

echo "Ready to dump to $FILE" >> "$HOME/pg_status" 

cd $DIR
if [ -f "$FILE" ];
  rm $FILE

I want to backup the sql file on my local machine at ~/pg_bak, NOT on the remote server. In other words, I do not want to backup the file on the remote server. Will the pg_dump command be running in the context of the computer I am running it from or from the remote server?

  • if postgresql-client-common is installed -yes. – malyy Jun 10 '16 at 9:44

Simply use

pg_dump -h $SERVER -U $PG_USER $DATABASE > $FILE.sql 

to output the result of the command to the local machine. You might want to pipe it through gzip or similar, first, to obtain a smaller dump.

Since I'm already here, a couple of other suggestions:

30 5 * * * /home/donato/Documents/pg_bak.sh

You don't need to invoke sh to run your script, just give it the owner execution bit.

And the whole chunk of code in the script immediately after the echo can be changed to:

cd $DIR
rm $FILE 2>/dev/null

In fact, since now you're invoking pg_dump only once, there's no need to wrap it in a function -- just call it directly.

  • Quick question. I want to name my file with the current date in it. That's why I used $FILE.sql. In your example, you have the static string mylocaldump.sql. – Donato Jun 10 '16 at 19:18
  • You're absolutely right, I wanted to make clear that the resulting dump file was local but in doing this I removed the dynamic filename. Answer corrected -- in fact you only have to use the stdout redirection > in lieu of the -f option. – dr01 Jun 13 '16 at 9:40

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.