Home / server / Questions / 698304
Accepted
nulltek
Asked: 2015-06-12 05:58:36 +0800 CST

Automate pg_dump in a bash script

  • 772

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!

backup

2 Answers

  1. Best Answer

    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" [email protected]; exit 1; fi
    • 4

  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!

    • 3