#!/bin/bash

# PostgreSQL database backup/restore  script - Copyright 2003 DRIM-TECH S.L..

#  This script is distributed under the terms of the GNU General Public

#    License.  A copy is available at http://www.gnu.org/



# #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

# #                       PARAMS

# #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

pg_dump="/usr/bin/pg_dump"

psql="/usr/bin/psql"

createdb="/usr/bin/createdb"

USER=postgres

BACKUP_DIR="/data/web/internal_app/pg_backups/"

DATA_OPTIONS="--data-only --inserts --column-inserts --format p --no-owner --no-reconnect -U $USER"

SCHEMA_OPTIONS="--create --schema-only --format p --no-owner -U $USER"

ZIPIT="tar zcvf"

ZIPIT_OUT="tar zxvf "

TMP_DIR="/tmp/"









# #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

# #                       FUNCTIONS

# #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





function backup_pg_database

{

DATABASE=$1

# The output files

DATA_FILE="${BACKUP_DIR}${DATABASE}-data.sql"

SCHEMA_FILE="${BACKUP_DIR}${DATABASE}-schema.sql"

# Dump Data

#echo Dumping $DATABASE data to $DATA_FILE

$pg_dump $DATA_OPTIONS --file $DATA_FILE $DATABASE

# Dump Schema

#echo Dumping $DATABASE schema to $SCHEMA_FILE

$pg_dump $SCHEMA_OPTIONS --file $SCHEMA_FILE $DATABASE

if [ -n "$ZIPIT" ]; then

$ZIPIT ${DATA_FILE}.tar.gz $DATA_FILE  > $TMP_DIR/pg_backup.log

$ZIPIT ${SCHEMA_FILE}.tar.gz $SCHEMA_FILE > $TMP_DIR/pg_backup.log

rm ./pg_backups/*.sql

fi

}







function restore_pg_database

{

DATABASE=$1

DATA_FILE="${BACKUP_DIR}${DATABASE}-data.sql.tar.gz"

SCHEMA_FILE="${BACKUP_DIR}${DATABASE}-schema.sql.tar.gz"

cp $DATA_FILE $TMP_DIR   > $TMP_DIR/pg_backup.log

cp $SCHEMA_FILE $TMP_DIR > $TMP_DIR/pg_backup.log

su -l postgres -s /bin/sh -c "$createdb $DATABASE" > $TMP_DIR/pg_backup.log



DATA_FILE_GZ="${TMP_DIR}${DATABASE}-data.sql.tar.gz"

SCHEMA_FILE_GZ="${TMP_DIR}${DATABASE}-schema.sql.tar.gz"

DATA_FILE="${TMP_DIR}${DATABASE}-data.sql"

SCHEMA_FILE="${TMP_DIR}${DATABASE}-schema.sql"



if [ -n "$ZIPIT_OUT" ]; then

$ZIPIT_OUT $DATA_FILE_GZ > $TMP_DIR/pg_backup.log

$ZIPIT_OUT $SCHEMA_FILE_GZ > $TMP_DIR/pg_backup.log

fi



$psql < $SCHEMA_FILE -U $USER $DATABASE > $TMP_DIR/pg_backup.log

$psql < $DATA_FILE -U $USER $DATABASE   > $TMP_DIR/pg_backup.log

rm /tmp/*sql*

echo " ";

echo "$DATABASE RESTORE    [OK]";

echo "Do: more  $TMP_DIRpg_backup.log"

}







# #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

# #                       VOID ()

# #+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





if  [ "$1" == "backup" ] ; then

if [ -n "$2" ]; then

backup_pg_database $2

else

# Get Database List

DB_LIST=`$psql -l -t -U $USER |cut -f1 -d| |sed -e 's/ //g'`

for DB in $DB_LIST

do

if [ "$DB" != "template0" ] && [ "$DB" != "template1" ]; then

backup_pg_database $DB

fi

done

fi

else



if [ "$1" == "restore" ] ; then

if [ -n "$2" ]; then

restore_pg_database $2

else

# Get Database List

DB_LIST=`$psql -l -t -U $USER |cut -f1 -d| |sed -e 's/ //g'`

for DB in $DB_LIST

do

if [ "$DB" != "template0" ] && [ "$DB" != "template1" ]; then

backup_pg_database $DB

fi

done

fi

else

echo "USE: pg_backup backup/restore [database]";

fi

fi