Skip to content

Instantly share code, notes, and snippets.

@jinweijie

jinweijie/pg_backup.sh

Forked from spiderr/pg_backup.sh
Last active May 19, 2020 05:52
Show Gist options
  • Save jinweijie/cc98b746c9d1c7a8d9ab63d430a57b4a to your computer and use it in GitHub Desktop.
Save jinweijie/cc98b746c9d1c7a8d9ab63d430a57b4a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jinweijie revised this gist May 19, 2020. 1 changed file with 2 additions and 18 deletions.
    20 changes: 2 additions & 18 deletions pg_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -308,7 +308,6 @@ run_b() {
    duration=`expr $finish_time - $start_time`
    echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup and vacuum
    @@ -329,7 +328,6 @@ run_bv() {
    duration=`expr $finish_time - $start_time`
    echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup, vacuum and analyze
    @@ -350,7 +348,6 @@ run_bva() {
    duration=`expr $finish_time - $start_time`
    echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum
    @@ -364,7 +361,6 @@ run_v() {
    duration=`expr $finish_time - $start_time`
    echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum and analyze
    @@ -378,7 +374,6 @@ run_va() {
    duration=`expr $finish_time - $start_time`
    echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Print information regarding available backups
    @@ -390,17 +385,16 @@ print_info() {
    if [ ! -d $PGBACKUPDIR ] ; then
    echo "There are currently no available backups"
    echo ""
    exit 0
    exit 1
    else
    for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do
    for i in `find "$PGBACKUPDIR" -maxdepth 2 -type d`; do
    echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
    #for j in `ls "$PGBACKUPDIR/$i"`; do
    # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`"
    #done
    done
    echo ""
    fi
    exit 1
    }

    # Print configuration test
    @@ -509,7 +503,6 @@ print_configtest() {
    test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"

    echo ""
    exit 1
    }

    # Print help
    @@ -529,7 +522,6 @@ print_help() {
    echo " help, This message"
    echo ""
    echo "Report bugs to <speedboy_420 at hotmail dot com >."
    exit 1
    }

    case "$1" in
    @@ -538,58 +530,50 @@ case "$1" in
    db_connectivity
    set_permissions
    run_b
    exit 1
    ;;
    # Run backup and vacuum
    bv)
    db_connectivity
    set_permissions
    run_bv
    exit 1
    ;;

    # Run backup, vacuum and analyze
    bva)
    db_connectivity
    set_permissions
    run_bva
    exit 1
    ;;

    # Run vacuum
    v)
    db_connectivity
    set_permissions
    run_v
    exit 1
    ;;

    # Run vacuum and analyze
    va)
    db_connectivity
    set_permissions
    run_va
    exit 1
    ;;

    # Print info
    info)
    set_permissions
    print_info
    exit 1
    ;;

    # Print configtest
    configtest)
    db_connectivity
    set_permissions
    print_configtest
    exit 1
    ;;

    # Default
    *)
    print_help
    exit 1
    ;;
    esac
  2. jinweijie revised this gist May 19, 2020. 1 changed file with 8 additions and 5 deletions.
    13 changes: 8 additions & 5 deletions pg_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -8,16 +8,18 @@
    # +-- speedboy (speedboy_420 at hotmail dot com)
    # +-- spiderr (spiderr at bitweaver dot org)
    # +-- flexiondotorg (code at flexion dot org)
    # +-- jinweijiee (jinweijie at gmail dot com)
    #
    # Last modified
    # |
    # +-- 16-10-2001
    # +-- 16-12-2007
    # +-- 31-03-2010
    # +-- 19-05-2020
    #
    # Version
    # |
    # +-- 1.1.3
    # +-- 1.1.4
    #
    # Description
    # |
    @@ -28,6 +30,7 @@
    # |
    # +-- Postgresql
    # | |
    # | +-- 12.2.0
    # | +-- 8.4.2
    # | +-- 8.3.9
    # | +-- 8.1.9
    @@ -294,7 +297,7 @@ run_b() {
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    "$PGBINDIR/pg_dump" $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    @@ -315,7 +318,7 @@ run_bv() {
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    "$PGBINDIR/pg_dump" $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    @@ -336,7 +339,7 @@ run_bva() {
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    "$PGBINDIR/pg_dump" $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    @@ -589,4 +592,4 @@ case "$1" in
    print_help
    exit 1
    ;;
    esac
    esac
  3. @spiderr spiderr revised this gist Sep 3, 2013. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions pg_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -267,9 +267,9 @@ permissions_backup_log="0644"
    db_connectivity() {
    tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
    if [ "$exclusions" = "" ]; then
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    else
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "SELECT datname FROM pg_database WHERE datistemplate = false" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    fi
    }

  4. @spiderr spiderr revised this gist Aug 24, 2010. 1 changed file with 413 additions and 407 deletions.
    820 changes: 413 additions & 407 deletions pg_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,54 +1,60 @@
    #!/bin/sh
    #
    # Postgresql backup script
    #
    # Postgresql backup script
    # http://www.bitweaver.org/wiki/pg_backup+PostgreSQL+backup+script
    #
    # Author
    # |
    # +-- speedboy (speedboy_420 at hotmail dot com)
    # +-- spiderr (spiderr at bitweaver dot org)
    # +-- flexiondotorg (code at flexion dot org)
    #
    # Last modified
    # |
    # +-- 16-10-2001
    # +-- 16-12-2007
    # +-- 31-03-2010
    #
    # Version
    # |
    # +-- 1.1.2
    # +-- 1.1.3
    #
    # Description
    # |
    # +-- A bourne shell script that automates the backup, vacuum and
    # analyze of databases running on a postgresql server.
    # analyze of databases running on a postgresql server.
    #
    # Tested on
    # |
    # +-- Postgresql
    # | |
    # | +-- 8.1.9
    # | +-- 7.1.3
    # | +-- 7.1.2
    # | +-- 7.1
    # | +-- 7.0
    # | |
    # | +-- 8.4.2
    # | +-- 8.3.9
    # | +-- 8.1.9
    # | +-- 7.1.3
    # | +-- 7.1.2
    # | +-- 7.1
    # | +-- 7.0
    # |
    # +-- Operating systems
    # | |
    # | +-- CentOS 5 (RHEL 5)
    # | +-- Linux Redhat 6.2
    # | +-- Linux Mandrake 7.2
    # | +-- FreeBSD 4.3
    # | +-- Cygwin (omit the column ":" because NT doesn't quite digest it)
    # | |
    # | +-- CentOS 5 (RHEL 5)
    # | +-- Linux Redhat 6.2
    # | +-- Linux Mandrake 7.2
    # | +-- FreeBSD 4.3
    # | +-- Ubuntu 9.04, 9.10 and 10.04 LTS
    # | +-- Cygwin (omit the column ":" because NT doesn't quite digest it)
    # |
    # +-- Shells:
    # |
    # +-- sh
    # +-- bash
    # +-- ksh
    # |
    # +-- sh
    # +-- bash
    # +-- ksh
    #
    # Requirements
    # |
    # +-- grep, awk, sed, echo, bzip2, chmod, touch, mkdir, date, psql,
    # test, expr, dirname, find, tail, du, pg_dump, vacuum_db
    # test, expr, dirname, find, tail, du, pg_dump, vacuum_db
    #
    # Installation
    # |
    @@ -73,13 +79,13 @@
    # | This will test the configuration details.
    # |
    # +-- Once you have done that add similiar entries given as examples
    # below to the crontab (`crontab -e`) without the the _first_ #
    # characters.
    # below to the crontab (`crontab -e`) without the the _first_ #
    # characters.
    #
    # # Run a backup of the remote database host 'foodb', likely on a private network
    # 00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    # # Run a backup of the local database host 'foodb' to a custom backup directory
    # 00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    # # Run a backup of the remote database host 'foodb', likely on a private network
    # 00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    # # Run a backup of the local database host 'foodb' to a custom backup directory
    # 00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    #
    # Restoration
    # |
    @@ -106,16 +112,16 @@
    # |
    # +-- Refer to the following url for more pg_restore help:
    #
    # http://www.postgresql.org/idocs/index.php?app-pgrestore.html
    # http://www.postgresql.org/idocs/index.php?app-pgrestore.html
    #
    # To Do List
    # 1. db_connectivity() is BROKEN, if the script can not create a
    # connection to postmaster it should die and write to the logfile
    # that it could not connect.
    # 2. make configtest check for every required binary
    # 1. db_connectivity() is BROKEN, if the script can not create a
    # connection to postmaster it should die and write to the logfile
    # that it could not connect.
    # 2. make configtest check for every required binary
    #
    ########################################################################
    # Start configuration #
    # Start configuration #
    ########################################################################
    #
    ##################
    @@ -124,41 +130,41 @@
    #
    # Postgresql hostname to connect to.
    if [ $PGHOST ]; then
    PARAM_PGHOST="-h $PGHOST"
    PARAM_PGHOST="-h $PGHOST"
    else
    PGHOST="localhost"
    PGHOST="localhost"
    fi

    # Postgresql username to perform backups under.
    if [ -z $PGUSER ]; then
    PGUSER="postgres"
    PGUSER="postgres"
    fi

    # Postgresql password for the Postgresql username (if required).
    # postgresql_password="password"

    ##################
    # Locations #
    # Locations #
    ##################
    #
    # Location to place backups.
    if [ -z $PGBACKUPDIR ]; then
    PGBACKUPDIR="/var/lib/pgsql/backups"
    PGBACKUPDIR="/var/lib/postgresql/backups"
    fi
    subdir=`date +%Y-%m`

    # Location to place the pg_backup.sh logfile.
    if [ -z $PGLOGDIR ]; then
    PGLOGDIR="/var/log/pgsql/pg_backup.log"
    PGLOGDIR="/var/log/postgresql/pg_backup.log"
    fi

    # Location of the psql binaries.
    if [ -z $PGBINDIR ]; then
    PGBINDIR="/usr/bin"
    PGBINDIR="/usr/bin"
    fi

    ##################
    # Permissions #
    # Permissions #
    ##################
    #
    # Permissions for the backup location.
    @@ -175,412 +181,412 @@ permissions_backup_log="0644"
    ##################
    #
    # Databases to exclude from the backup process (separated by a space)
    exclusions="template"

    # Backup format
    # |
    # +-- p = plain text : psql database < backup_file
    # +-- t = tar : pg_restore -F t -d database backup_file
    # +-- c = custom : pg_restore -F c -d database backup_file
    #
    backup_format="t"

    # Backup large objects
    backup_large_objects="yes"

    # bzip2 the backups
    backup_bzip2="yes"

    # Date format for the backup
    # |
    # +-- %d-%m-%Y = DD-MM-YYYY
    # +-- %Y-%m-%d = YYYY-MM-DD
    # +-- %A-%b-%Y = Tuesday-Sep-2001
    # +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001
    # |
    # +-- For more date formats type:
    #
    # date --help
    #
    backup_date_format="%Y-%m-%d"

    # You must comment out the line below before using this script
    #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1
    ########################################################################
    # End configuration #
    ########################################################################
    #
    #################
    # Variables #
    #################
    #
    version="1.1.2"
    current_time=`date +%H%M`
    date_info=`date +$backup_date_format`
    PGPASSWORD="$postgresql_password"
    PATH="$PATH:/bin:/usr/bin"

    # Export the variables
    export PGUSER PGPASSWORD PATH

    #################
    # Checking #
    #################
    #
    # Check the backup format
    if [ "$backup_format" = "p" ]; then
    backup_type="Plain text SQL"
    backup_args="-F $backup_format"

    elif [ "$backup_format" = "t" ]; then
    backup_type="Tar"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    elif [ "$backup_format" = "c" ]; then
    backup_type="Custom"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    else
    backup_format="c"
    backup_args="-F $backup_format"
    backup_type="Custom"
    fi

    #################
    # Functions #
    #################
    #
    # Obtain a list of available databases with reference to the user
    # defined exclusions
    exclusions="template"

    # Backup format
    # |
    # +-- p = plain text : psql database < backup_file
    # +-- t = tar : pg_restore -F t -d database backup_file
    # +-- c = custom : pg_restore -F c -d database backup_file
    #
    backup_format="t"

    # Backup large objects
    backup_large_objects="yes"

    # bzip2 the backups
    backup_bzip2="yes"

    # Date format for the backup
    # |
    # +-- %d-%m-%Y = DD-MM-YYYY
    # +-- %Y-%m-%d = YYYY-MM-DD
    # +-- %A-%b-%Y = Tuesday-Sep-2001
    # +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001
    # |
    # +-- For more date formats type:
    #
    # date --help
    #
    backup_date_format="%Y-%m-%d"

    # You must comment out the line below before using this script
    #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1
    ########################################################################
    # End configuration #
    ########################################################################
    #
    #################
    # Variables #
    #################
    #
    version="1.1.3"
    current_time=`date +%H%M`
    date_info=`date +$backup_date_format`
    PGPASSWORD="$postgresql_password"
    PATH="$PATH:/bin:/usr/bin"

    # Export the variables
    export PGUSER PGPASSWORD PATH

    #################
    # Checking #
    #################
    #
    # Check the backup format
    if [ "$backup_format" = "p" ]; then
    backup_type="Plain text SQL"
    backup_args="-F $backup_format"

    elif [ "$backup_format" = "t" ]; then
    backup_type="Tar"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    elif [ "$backup_format" = "c" ]; then
    backup_type="Custom"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    else
    backup_format="c"
    backup_args="-F $backup_format"
    backup_type="Custom"
    fi

    #################
    # Functions #
    #################
    #
    # Obtain a list of available databases with reference to the user
    # defined exclusions
    db_connectivity() {
    tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
    if [ "$exclusions" = "" ]; then
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    else
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    fi
    tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
    if [ "$exclusions" = "" ]; then
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    else
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -v : | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    fi
    }

    # Setup the permissions according to the Permissions section
    set_permissions() {
    # Make the backup directories and secure them.
    mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"
    # Make the backup directories and secure them.
    mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"

    # Touch the log file
    touch "$PGLOGDIR"
    # Touch the log file
    touch "$PGLOGDIR"

    # Make the backup tree
    chmod -f $permissions_backup_log "$PGLOGDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir"
    # chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info"
    # Make the backup tree
    chmod -f $permissions_backup_log "$PGLOGDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir"
    # chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info"
    }

    # Run backup
    run_b() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup and vacuum
    run_bv() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup, vacuum and analyze
    run_bva() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum
    run_v() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum and analyze
    run_va() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Print information regarding available backups
    print_info() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Available backups:"
    echo ""
    if [ ! -d $PGBACKUPDIR ] ; then
    echo "There are currently no available backups"
    echo ""
    exit 0
    else
    for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do
    echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
    #for j in `ls "$PGBACKUPDIR/$i"`; do
    # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`"
    #done
    done
    echo ""
    fi
    exit 1
    echo "Postgresql backup script version $version"
    echo ""
    echo "Available backups:"
    echo ""
    if [ ! -d $PGBACKUPDIR ] ; then
    echo "There are currently no available backups"
    echo ""
    exit 0
    else
    for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do
    echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
    #for j in `ls "$PGBACKUPDIR/$i"`; do
    # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`"
    #done
    done
    echo ""
    fi
    exit 1
    }

    # Print configuration test
    print_configtest() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Configuration test..."
    echo ""

    # Check database connectivity information
    echo -n "Database hostname : "
    echo "$PGHOST"
    echo -n "Database username : "
    echo "$PGUSER"
    echo -n "Database connectivity : "
    $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."

    # Backup information
    echo ""
    echo "Backup information:"
    echo ""
    echo -n "Backup format : "
    if [ "$backup_format" = "p" ]; then
    echo "Plain text SQL"
    elif [ "$backup_format" = "t" ]; then
    echo "Tar"
    else
    echo "Custom"
    fi

    echo -n "Backup large objects : "
    if [ "$backup_large_objects" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "bzip2 backups : "
    if [ "$backup_bzip2" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "Backup date format : $date_info"
    echo ""

    # File locations
    echo -n "Backup directory : "
    echo "$PGBACKUPDIR"
    echo -n "Backup logfile : "
    echo "$PGLOGDIR"
    echo -n "Postgresql binaries : "
    echo "$PGBINDIR"

    # Backup file permissions

    echo -n "Backup directory permissions : "
    echo "$permissions_backup_dir"
    echo -n "Backup file permissions : "
    echo "$permissions_backup_file"
    echo -n "Backup log permissions : "
    echo "$permissions_backup_log"

    # Databases that will be backed up
    echo -n "Databases that will be backed up : "
    echo ""
    for i in $databases; do
    echo " $i"
    done

    # Databases that will not be backed up
    echo -n "Databases that will not be backed up :"
    echo ""
    if [ "$exclusions" = "" ]; then
    echo " none"
    else
    for i in $exclusions; do
    echo " $i"
    done
    echo ""
    fi

    # Check if the backups location is writable
    echo "Checking permissions:"
    echo ""
    echo -n "Write access : $PGBACKUPDIR: "
    # Needed to create/write to the dump location"
    test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"

    # Check if the logfile location is writable
    echo -n "Write access : $PGLOGDIR: "
    # Needed to create/write to this scripts logfile"
    if [ ! -x $PGLOGDIR ] ; then
    test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No"
    else
    test -w "$PGLOGDIR" && echo "Yes" || echo "No"
    fi

    # Check if the binaries are executable
    echo -n "Execute access : $PGBINDIR/psql: "
    test -x $PGBINDIR/psql && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/pg_dump: "
    test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/vacuumdb: "
    test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"

    echo ""
    exit 1
    echo "Postgresql backup script version $version"
    echo ""
    echo "Configuration test..."
    echo ""

    # Check database connectivity information
    echo -n "Database hostname : "
    echo "$PGHOST"
    echo -n "Database username : "
    echo "$PGUSER"
    echo -n "Database connectivity : "
    $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."

    # Backup information
    echo ""
    echo "Backup information:"
    echo ""
    echo -n "Backup format : "
    if [ "$backup_format" = "p" ]; then
    echo "Plain text SQL"
    elif [ "$backup_format" = "t" ]; then
    echo "Tar"
    else
    echo "Custom"
    fi

    echo -n "Backup large objects : "
    if [ "$backup_large_objects" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "bzip2 backups : "
    if [ "$backup_bzip2" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "Backup date format : $date_info"
    echo ""

    # File locations
    echo -n "Backup directory : "
    echo "$PGBACKUPDIR"
    echo -n "Backup logfile : "
    echo "$PGLOGDIR"
    echo -n "Postgresql binaries : "
    echo "$PGBINDIR"

    # Backup file permissions

    echo -n "Backup directory permissions : "
    echo "$permissions_backup_dir"
    echo -n "Backup file permissions : "
    echo "$permissions_backup_file"
    echo -n "Backup log permissions : "
    echo "$permissions_backup_log"

    # Databases that will be backed up
    echo -n "Databases that will be backed up : "
    echo ""
    for i in $databases; do
    echo " $i"
    done

    # Databases that will not be backed up
    echo -n "Databases that will not be backed up :"
    echo ""
    if [ "$exclusions" = "" ]; then
    echo " none"
    else
    for i in $exclusions; do
    echo " $i"
    done
    echo ""
    fi

    # Check if the backups location is writable
    echo "Checking permissions:"
    echo ""
    echo -n "Write access : $PGBACKUPDIR: "
    # Needed to create/write to the dump location"
    test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"

    # Check if the logfile location is writable
    echo -n "Write access : $PGLOGDIR: "
    # Needed to create/write to this scripts logfile"
    if [ ! -x $PGLOGDIR ] ; then
    test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No"
    else
    test -w "$PGLOGDIR" && echo "Yes" || echo "No"
    fi

    # Check if the binaries are executable
    echo -n "Execute access : $PGBINDIR/psql: "
    test -x $PGBINDIR/psql && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/pg_dump: "
    test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/vacuumdb: "
    test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"

    echo ""
    exit 1
    }

    # Print help
    print_help() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Usage: $0 [options]"
    echo ""
    echo "Options:"
    echo " b, Backup ALL databases"
    echo " bv, Backup and Vacuum ALL databases"
    echo " bva, Backup, Vacuum and Analyze ALL databases"
    echo " v, Vacuum ALL databases"
    echo " va, Vacuum and Analyze ALL databases"
    echo " info, Information regarding all available backups"
    echo " configtest, Configuration test"
    echo " help, This message"
    echo ""
    echo "Report bugs to <speedboy_420 at hotmail dot com >."
    exit 1
    echo "Postgresql backup script version $version"
    echo ""
    echo "Usage: $0 [options]"
    echo ""
    echo "Options:"
    echo " b, Backup ALL databases"
    echo " bv, Backup and Vacuum ALL databases"
    echo " bva, Backup, Vacuum and Analyze ALL databases"
    echo " v, Vacuum ALL databases"
    echo " va, Vacuum and Analyze ALL databases"
    echo " info, Information regarding all available backups"
    echo " configtest, Configuration test"
    echo " help, This message"
    echo ""
    echo "Report bugs to <speedboy_420 at hotmail dot com >."
    exit 1
    }

    case "$1" in
    # Run backup
    b)
    db_connectivity
    set_permissions
    run_b
    exit 1
    ;;
    # Run backup and vacuum
    bv)
    db_connectivity
    set_permissions
    run_bv
    exit 1
    ;;

    # Run backup, vacuum and analyze
    bva)
    db_connectivity
    set_permissions
    run_bva
    exit 1
    ;;

    # Run vacuum
    v)
    db_connectivity
    set_permissions
    run_v
    exit 1
    ;;

    # Run vacuum and analyze
    va)
    db_connectivity
    set_permissions
    run_va
    exit 1
    ;;

    # Print info
    info)
    set_permissions
    print_info
    exit 1
    ;;

    # Print configtest
    configtest)
    db_connectivity
    set_permissions
    print_configtest
    exit 1
    ;;

    # Default
    *)
    print_help
    exit 1
    ;;
    # Run backup
    b)
    db_connectivity
    set_permissions
    run_b
    exit 1
    ;;
    # Run backup and vacuum
    bv)
    db_connectivity
    set_permissions
    run_bv
    exit 1
    ;;

    # Run backup, vacuum and analyze
    bva)
    db_connectivity
    set_permissions
    run_bva
    exit 1
    ;;

    # Run vacuum
    v)
    db_connectivity
    set_permissions
    run_v
    exit 1
    ;;

    # Run vacuum and analyze
    va)
    db_connectivity
    set_permissions
    run_va
    exit 1
    ;;

    # Print info
    info)
    set_permissions
    print_info
    exit 1
    ;;

    # Print configtest
    configtest)
    db_connectivity
    set_permissions
    print_configtest
    exit 1
    ;;

    # Default
    *)
    print_help
    exit 1
    ;;
    esac
  5. @spiderr spiderr created this gist Aug 24, 2010.
    586 changes: 586 additions & 0 deletions pg_backup.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,586 @@
    #!/bin/sh
    #
    # Postgresql backup script
    #
    # Author
    # |
    # +-- speedboy (speedboy_420 at hotmail dot com)
    # +-- spiderr (spiderr at bitweaver dot org)
    #
    # Last modified
    # |
    # +-- 16-10-2001
    # +-- 16-12-2007
    #
    # Version
    # |
    # +-- 1.1.2
    #
    # Description
    # |
    # +-- A bourne shell script that automates the backup, vacuum and
    # analyze of databases running on a postgresql server.
    #
    # Tested on
    # |
    # +-- Postgresql
    # | |
    # | +-- 8.1.9
    # | +-- 7.1.3
    # | +-- 7.1.2
    # | +-- 7.1
    # | +-- 7.0
    # |
    # +-- Operating systems
    # | |
    # | +-- CentOS 5 (RHEL 5)
    # | +-- Linux Redhat 6.2
    # | +-- Linux Mandrake 7.2
    # | +-- FreeBSD 4.3
    # | +-- Cygwin (omit the column ":" because NT doesn't quite digest it)
    # |
    # +-- Shells:
    # |
    # +-- sh
    # +-- bash
    # +-- ksh
    #
    # Requirements
    # |
    # +-- grep, awk, sed, echo, bzip2, chmod, touch, mkdir, date, psql,
    # test, expr, dirname, find, tail, du, pg_dump, vacuum_db
    #
    # Installation
    # |
    # +-- Set the path and shell you wish to use for this script on the
    # | first line of this file. Keep in mind this script has only been
    # | tested on the shells above in the "Tested on" section.
    # |
    # +-- Set the configuration variables below in the configuration
    # | section to appropriate values.
    # |
    # +-- Remove the line at the end of the configuration section so that
    # | the script will run.
    # |
    # +-- Now save the script and perform the following command:
    # |
    # | chmod +x ./pg_backup.sh
    # |
    # +-- Now run the configuration test:
    # |
    # | ./pg_backup.sh configtest
    # |
    # | This will test the configuration details.
    # |
    # +-- Once you have done that add similiar entries given as examples
    # below to the crontab (`crontab -e`) without the the _first_ #
    # characters.
    #
    # # Run a backup of the remote database host 'foodb', likely on a private network
    # 00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    # # Run a backup of the local database host 'foodb' to a custom backup directory
    # 00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2 >> /var/log/pgsql/pg_backup.log 2>&1
    #
    # Restoration
    # |
    # +-- Restoration can be performed by using psql or pg_restore.
    # | Here are two examples:
    # |
    # | a) If the backup is plain text:
    # |
    # | Firstly bunzip2 your backup file (if it was bzip2ped).
    # |
    # | nunzip2 backup_file.bz2
    # | psql -U postgres database < backup_file
    # |
    # | b) If the backup is not plain text:
    # |
    # | Firstly bunzip2 your backup file (if it was bzip2ed).
    # |
    # | bunzip2 backup_file
    # | pg_restore -d database -F {c|t} backup_file
    # |
    # | Note: {c|t} is the format the database was backed up as.
    # |
    # | pg_restore -d database -F t backup_file_tar
    # |
    # +-- Refer to the following url for more pg_restore help:
    #
    # http://www.postgresql.org/idocs/index.php?app-pgrestore.html
    #
    # To Do List
    # 1. db_connectivity() is BROKEN, if the script can not create a
    # connection to postmaster it should die and write to the logfile
    # that it could not connect.
    # 2. make configtest check for every required binary
    #
    ########################################################################
    # Start configuration #
    ########################################################################
    #
    ##################
    # Authentication #
    ##################
    #
    # Postgresql hostname to connect to.
    if [ $PGHOST ]; then
    PARAM_PGHOST="-h $PGHOST"
    else
    PGHOST="localhost"
    fi

    # Postgresql username to perform backups under.
    if [ -z $PGUSER ]; then
    PGUSER="postgres"
    fi

    # Postgresql password for the Postgresql username (if required).
    # postgresql_password="password"

    ##################
    # Locations #
    ##################
    #
    # Location to place backups.
    if [ -z $PGBACKUPDIR ]; then
    PGBACKUPDIR="/var/lib/pgsql/backups"
    fi
    subdir=`date +%Y-%m`

    # Location to place the pg_backup.sh logfile.
    if [ -z $PGLOGDIR ]; then
    PGLOGDIR="/var/log/pgsql/pg_backup.log"
    fi

    # Location of the psql binaries.
    if [ -z $PGBINDIR ]; then
    PGBINDIR="/usr/bin"
    fi

    ##################
    # Permissions #
    ##################
    #
    # Permissions for the backup location.
    permissions_backup_dir="0755"

    # Permissions for the backup files.
    permissions_backup_file="0644"

    # Permissions for the backup logfile.
    permissions_backup_log="0644"

    ##################
    # Other options #
    ##################
    #
    # Databases to exclude from the backup process (separated by a space)
    exclusions="template"

    # Backup format
    # |
    # +-- p = plain text : psql database < backup_file
    # +-- t = tar : pg_restore -F t -d database backup_file
    # +-- c = custom : pg_restore -F c -d database backup_file
    #
    backup_format="t"

    # Backup large objects
    backup_large_objects="yes"

    # bzip2 the backups
    backup_bzip2="yes"

    # Date format for the backup
    # |
    # +-- %d-%m-%Y = DD-MM-YYYY
    # +-- %Y-%m-%d = YYYY-MM-DD
    # +-- %A-%b-%Y = Tuesday-Sep-2001
    # +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001
    # |
    # +-- For more date formats type:
    #
    # date --help
    #
    backup_date_format="%Y-%m-%d"

    # You must comment out the line below before using this script
    #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1
    ########################################################################
    # End configuration #
    ########################################################################
    #
    #################
    # Variables #
    #################
    #
    version="1.1.2"
    current_time=`date +%H%M`
    date_info=`date +$backup_date_format`
    PGPASSWORD="$postgresql_password"
    PATH="$PATH:/bin:/usr/bin"

    # Export the variables
    export PGUSER PGPASSWORD PATH

    #################
    # Checking #
    #################
    #
    # Check the backup format
    if [ "$backup_format" = "p" ]; then
    backup_type="Plain text SQL"
    backup_args="-F $backup_format"

    elif [ "$backup_format" = "t" ]; then
    backup_type="Tar"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    elif [ "$backup_format" = "c" ]; then
    backup_type="Custom"
    if [ "$backup_large_objects" = "yes" ]; then
    backup_args="-b -F $backup_format"
    else
    backup_args="-F $backup_format"
    fi
    else
    backup_format="c"
    backup_args="-F $backup_format"
    backup_type="Custom"
    fi

    #################
    # Functions #
    #################
    #
    # Obtain a list of available databases with reference to the user
    # defined exclusions
    db_connectivity() {
    tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
    if [ "$exclusions" = "" ]; then
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    else
    databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
    fi
    }

    # Setup the permissions according to the Permissions section
    set_permissions() {
    # Make the backup directories and secure them.
    mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"

    # Touch the log file
    touch "$PGLOGDIR"

    # Make the backup tree
    chmod -f $permissions_backup_log "$PGLOGDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR"
    chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir"
    # chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info"
    }

    # Run backup
    run_b() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup and vacuum
    run_bv() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run backup, vacuum and analyze
    run_bva() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    if [ "$backup_bzip2" = "yes" ]; then
    bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
    else
    chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
    fi
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum
    run_v() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Run vacuum and analyze
    run_va() {
    for i in $databases; do
    start_time=`date '+%s'`
    timeinfo=`date '+%T %x'`

    "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
    finish_time=`date '+%s'`
    duration=`expr $finish_time - $start_time`
    echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
    done
    exit 1
    }

    # Print information regarding available backups
    print_info() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Available backups:"
    echo ""
    if [ ! -d $PGBACKUPDIR ] ; then
    echo "There are currently no available backups"
    echo ""
    exit 0
    else
    for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do
    echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
    #for j in `ls "$PGBACKUPDIR/$i"`; do
    # echo " $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`"
    #done
    done
    echo ""
    fi
    exit 1
    }

    # Print configuration test
    print_configtest() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Configuration test..."
    echo ""

    # Check database connectivity information
    echo -n "Database hostname : "
    echo "$PGHOST"
    echo -n "Database username : "
    echo "$PGUSER"
    echo -n "Database connectivity : "
    $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."

    # Backup information
    echo ""
    echo "Backup information:"
    echo ""
    echo -n "Backup format : "
    if [ "$backup_format" = "p" ]; then
    echo "Plain text SQL"
    elif [ "$backup_format" = "t" ]; then
    echo "Tar"
    else
    echo "Custom"
    fi

    echo -n "Backup large objects : "
    if [ "$backup_large_objects" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "bzip2 backups : "
    if [ "$backup_bzip2" = "yes" ]; then
    echo "Yes"
    else
    echo "No"
    fi
    echo -n "Backup date format : $date_info"
    echo ""

    # File locations
    echo -n "Backup directory : "
    echo "$PGBACKUPDIR"
    echo -n "Backup logfile : "
    echo "$PGLOGDIR"
    echo -n "Postgresql binaries : "
    echo "$PGBINDIR"

    # Backup file permissions

    echo -n "Backup directory permissions : "
    echo "$permissions_backup_dir"
    echo -n "Backup file permissions : "
    echo "$permissions_backup_file"
    echo -n "Backup log permissions : "
    echo "$permissions_backup_log"

    # Databases that will be backed up
    echo -n "Databases that will be backed up : "
    echo ""
    for i in $databases; do
    echo " $i"
    done

    # Databases that will not be backed up
    echo -n "Databases that will not be backed up :"
    echo ""
    if [ "$exclusions" = "" ]; then
    echo " none"
    else
    for i in $exclusions; do
    echo " $i"
    done
    echo ""
    fi

    # Check if the backups location is writable
    echo "Checking permissions:"
    echo ""
    echo -n "Write access : $PGBACKUPDIR: "
    # Needed to create/write to the dump location"
    test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"

    # Check if the logfile location is writable
    echo -n "Write access : $PGLOGDIR: "
    # Needed to create/write to this scripts logfile"
    if [ ! -x $PGLOGDIR ] ; then
    test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No"
    else
    test -w "$PGLOGDIR" && echo "Yes" || echo "No"
    fi

    # Check if the binaries are executable
    echo -n "Execute access : $PGBINDIR/psql: "
    test -x $PGBINDIR/psql && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/pg_dump: "
    test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"

    echo -n "Execute access : $PGBINDIR/vacuumdb: "
    test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"

    echo ""
    exit 1
    }

    # Print help
    print_help() {
    echo "Postgresql backup script version $version"
    echo ""
    echo "Usage: $0 [options]"
    echo ""
    echo "Options:"
    echo " b, Backup ALL databases"
    echo " bv, Backup and Vacuum ALL databases"
    echo " bva, Backup, Vacuum and Analyze ALL databases"
    echo " v, Vacuum ALL databases"
    echo " va, Vacuum and Analyze ALL databases"
    echo " info, Information regarding all available backups"
    echo " configtest, Configuration test"
    echo " help, This message"
    echo ""
    echo "Report bugs to <speedboy_420 at hotmail dot com >."
    exit 1
    }

    case "$1" in
    # Run backup
    b)
    db_connectivity
    set_permissions
    run_b
    exit 1
    ;;
    # Run backup and vacuum
    bv)
    db_connectivity
    set_permissions
    run_bv
    exit 1
    ;;

    # Run backup, vacuum and analyze
    bva)
    db_connectivity
    set_permissions
    run_bva
    exit 1
    ;;

    # Run vacuum
    v)
    db_connectivity
    set_permissions
    run_v
    exit 1
    ;;

    # Run vacuum and analyze
    va)
    db_connectivity
    set_permissions
    run_va
    exit 1
    ;;

    # Print info
    info)
    set_permissions
    print_info
    exit 1
    ;;

    # Print configtest
    configtest)
    db_connectivity
    set_permissions
    print_configtest
    exit 1
    ;;

    # Default
    *)
    print_help
    exit 1
    ;;
    esac