2

I am re-learning bash after years of not using it very much and need to figure out a way to automate pg_dump of a single database to a directory.

Currently I manually ssh into the box, su to postgres user, then run pg_dump database > outfile.

This works fine, but I'm getting tired of having to do this manually.

I'm really rusty with bash and would like to figure out a way to do the following.

1.) Write a script that will pg_dump my database to a specific directory 2.) The script should output the outfile name as hostname-date (to allow for multiple backups) 3.) Hopefully provide some form of error handling.

I've looked at the Postgres wiki and found a pretty elaborate script that does this, but was wondering if there's something quick and dirty that will get the job done.

Any hints or a point in the right direction would be greatly appreciated.

Thanks guys and gals!

3

Not taking into account any specific syntax for pg_dump:

#!/bin/bash
$TODAY=`date --iso-8601` 
$BACKDIR=/backup 

pg_dump [options] > $BACKDIR/$HOSTNAME-$TODAY

if [ "$?"-ne 0]; then echo "Help" | mail -s "Backup failed" you@example.com; exit 1; fi
  • Thank you very much for the help. This works well. I'm going to post an answer I came up with on my own, but it doesn't have any error handling. Maybe I can combine your effort with my own. Cheers! – nulltek Jun 11 '15 at 15:23
  • as best practice, the mail sending in case of failure is not the recomended one, as you put your script into a cron job any result of that would be sent to root that you can forward to admin@serverAdminDomain.com – Philippe Gachoud Nov 27 '18 at 16:45
2

Here's what I came up with it's very simple but it works. Although I think Sven's answer does a better job with basic error handling.

#!/bin/sh    
hostname=`hostname`
# Dump DBs
  date=`date +"%Y%m%d_%H%M%N"`
  filename="/var/backups/app/${hostname}_${db}_${date}.sql"
  pg_dump databasename >  $filename 
  gzip $filename

exit 0

Let me know what you think!

  • 1
    You can use pg_dump -Z9 -Fc for compression to :) – max kaplan Nov 13 '17 at 8:30

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.