Respaldar Base de Datos en otra BD

README

Este snnipet debe permitir crear una copia de una base de datos de producción
para utilizarla en un ambiente de pruebas. Se supone el mismo servidor de Base
de datos y el mismo usuario.

# scritp crontab 
# Todos los dias a las 03 de la madrugada
# 00 03   * * *   root    backup_db.sh

backup_db.cfg.tmpl

dbprod="" 		# database production name
dbtest=""		# database backup name
username=""		# username postgres account
pgpass=""		# password postgres account
file=""			# temporal backup file
logfile=""		# logs file

backup_db.sh

#!/bin/bash
source backup_db.cfg
PGUSER="$username"
PGPASSWORD="$pgpass"
export PGUSER PGPASSWORD

dropdbcmd="DROP DATABASE IF EXISTS $dbtest"
createdbcmd="CREATE DATABASE $dbtest WITH OWNER = $username"
echo "----------------" >> "$logfile"
echo "`date`: Dumping DB $dbprod to $file ... " >> "$logfile"
pg_dump -h localhost -F c -f "$file" "$dbprod"
echo "`date`: Recreating DB '$dbtest' ... " >> "$logfile"
psql -d postgres -h localhost -c "$dropdbcmd"
psql -d postgres -h localhost -c "$createdbcmd"
echo "`date`: Restoring DB $dbtest ..." >> "$logfile"
pg_restore -h localhost -d "$dbtest" "$file"
echo "`date`: BACKUPDB DONE !!!" >> "$logfile"
echo "BACKUPDB DONE !!!" 
PGUSER=""
PGPASSWORD=""
export PGUSER PGPASSWORD