PG_BACKUP

Script para hacer copias de seguridad (y recuperarlas) de basesa de datos PostgreSQL. Bajo licencia GPL.

				#!/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