#!/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
PG_BACKUP
Script para hacer copias de seguridad (y recuperarlas) de basesa de datos PostgreSQL. Bajo licencia GPL.
Descargar adjuntos
COMPARTE ESTE TUTORIAL
COMPARTIR EN FACEBOOK
COMPARTIR EN TWITTER
COMPARTIR EN LINKEDIN
COMPARTIR EN WHATSAPP