Skip to content

Instantly share code, notes, and snippets.

@afair
Created May 27, 2021 14:17
Show Gist options
  • Save afair/caed27db3930b96062aa57670a1d156e to your computer and use it in GitHub Desktop.
Save afair/caed27db3930b96062aa57670a1d156e to your computer and use it in GitHub Desktop.
PostgreSQL Backup and Restore Script
#!/usr/bin/env bash
################################################################################
# pgbackup - Run without arguments for help.
# - Wrapper for PG backup/restore utilities
# (C) 2018 Allen Fair. All rights reserved.
################################################################################
# Use ENV Variables to override these defaults
user=${PGUSER-$USER}
dir=${PGBACKUPDIR-.}
host=${PGHOST-localhost}
pgjobs=${PGJOBS-4}
userhost=${PGUSERHOST--U $user -h $host}
pgdata=${PGDATA-`psql -A -t $userhost -c "show data_directory;"`}
pgopts="$PGOPTS $userhost"
ts=`date "+%Y%m%d-%H%M%S"`
dt=`date "+%Y%m%d"`
cmd=${1-help}
# BACKUP ALL ###################################################################
if [[ "$cmd" = "all" ]]; then
base=$dir/pgbackup.$dt
echo "BACKUP PostgreSQL ALL Directory Structure -> $base"
mkdir $base
echo UH=$userhost
pg_dumpall --globals-only $userhost | gzip > $base/globals.sql.gz
echo UH=$userhost
pg_dumpall --schema-only $userhost | gzip > $base/schema.sql.gz
echo UH=$userhost
psql -A -t $userhost \
-c "SELECT datname FROM pg_database WHERE NOT datistemplate order by 1" \
| while read db; do
mkdir $base/$db
opts="$pgopts --format=directory --jobs=$pgjobs --dbname=$db"
echo $db
pg_dump $opts --file=$base/$db # --no-synchronized-snapshots
du -hs $base/$db | sort -h
done;
du -hs $base | sort -h
exit;
# RESTORE-ALL ##################################################################
elif [[ "$cmd" = "restore-all" ]]; then
base=${2-.}
echo "RESTORE PostgreSQL ALL Directory Structure <- $base"
cd $base
gzcat globals.sql.gz | psql $userhost -d template1
find . -type d \
| while read db; do
db=${db#./}
if [[ "$db" != "." ]]; then
createdb $db
echo pg_restore -h $host --format=directory -d $db $db
pg_restore -h $host --format=directory -d $db $db
fi
done
exit;
# PLAIN|CUSTOM|all [DBNAME [SCHEMA [TABLE]]] ##################################
elif [ "$cmd" = "plain" -o "$cmd" = "custom" -o "$cmd" = "directory" ]; then
format=$cmd
db=${2-$user}
schema=$3
table=$4
dest=$dir/pgbackup
if [[ "$db" > " " ]]; then
pgopts=" --dbname=$db"
dest=$dest.$db
fi
if [[ "$schema" > " " ]]; then
pgopts=" --schema=$schema"
dest=$dest.$schema
fi
if [[ "$table" > " " ]]; then
pgopts=" --table=$table"
dest=$dest.$table
fi
dest=$dest.$format.$dt
#echo dest=$dest db=$db opt=$pgopts s=$schema t=$table format=$format
if [[ "$db" = "" ]]; then
echo "BACKUP PostgreSQL CLUSTER -> $dest.gz"
pg_dumpall $pgopts > $dest
gzip $dest
ls -lh $dest.gz
else
echo "BACKUP PostgreSQL $db $schema $table -> $dest"
if [[ "$format" = "plain" ]]; then
dest=$dest.gz
pg_dump $pgopts --format=$format | gzip > $dest
else
echo pg_dump $pgopts --format=$format --file=$dest
pg_dump $pgopts --format=$format --file=$dest
fi
ls -lh $dest
du -hs $dest
fi
exit;
# RESTORE[-PLAIN|-CUSTOM] BACKUP DBNAME [SCHEMA [TABLE]] ################################
elif [ "$cmd" = "restore" -o "$cmd" = "restore-custom" -o "$cmd" = "restore-plain" ]; then
if [[ "$cmd" = "restore" ]]; then
format=directory
elif [[ "$cmd" = "restore-plain" ]]; then
format=plain
else
format=custom
fi
backup=$2
db=$3
schema=$4
table=$5
if [[ "$db" > " " ]]; then
pgopts=" --dbname=$db"
fi
echo "RESTORE $backup -> PostgreSQL $db $schema $table"
if [[ "$format" = "plain" ]]; then
db=${schema-$USER}
schema=${schema-public}
# Optional: psql --set ON_ERROR_STOP=on dbname < dumpfile
zcat $backup | psql "host=$host user=$user dbname=$db options=--search_path=$schema,public"
else
pgopts="$pgopts --format=$format"
if [[ "$format" = "directory" ]]; then
pgopts="$pgopts --jobs $pgjobs"
fi
if [[ "$schema" > " " ]]; then
pgopts=" --schema=$schema"
fi
if [[ "$table" > " " ]]; then
pgopts=" --table=$table"
fi
echo pg_restore $pgopts --format=$format $backup
pg_restore $pgopts --format=$format $backup
fi
exit;
# MIGRATE FROMHOST FROMDB TOHOST TODB TOSCHEMA TOUSER ##########################
elif [[ "$cmd" = "migrate" ]]; then
fromhost=${2-localhost}
fromdb=${3-$USER}
tohost=${4-localhost}
todb=${6-$fromdb}
as=${7-$user}
dumpdir=$dir/dump.$fromdb
echo mkdir $dumpdir
echo time pg_dump --format=directory -j $pgjobs -U $as -h $fromhost -d $fromdb -f $dumpdir
echo time pg_restore --format=directory -j $pgjobs -U $as -h $tohost -d $todb -e $dumpdir
echo rm -r $dumpdir
exit;
# MIGRATE-SIMPLE FROMHOST FROMDB TOHOST TODB TOUSER ############################
elif [[ "$cmd" = "migrate-simple" ]]; then
fromhost=${2-localhost}
fromdb=${3-$USER}
tohost=${4-localhost}
todb=${6-$fromdb}
as=${7-$user}
pg_dump -h $fromhost -U $as $fromdb | psql --set ON_ERROR_STOP=on -h $tohost -U $as $todb
exit;
# BACKUP-RSYNC [FROMHOST:]DIR [TOHOST:]DIR #########################################
elif [[ "$cmd" = "backup-rsync" ]]; then
fromdir=$2
todir=${3-$pgdata}
rsync -az --exclude pg_xlog --exclude postgresql.conf $fromdir/* $todir/
echo "Stop postgresql on old server, enter when ready to complete, Ctrl+C to abort"
read go
rsync -c -az --exclude pg_xlog --exclude postgresql.conf $fromdir/* $todir/
echo "Start postgresql on the new server"
exit;
# BASE FROMHOST [DIR] ##########################################################
elif [[ "$cmd" = "base" ]]; then
fromhost={$2-$host}
dir={$3-$pgdata}
pg_basebackup $PGOPTS -h $fromhost -U $user -D $dir
exit;
# USAGE ########################################################################
else
echo " Usage: pgbackup [command] # Wrapper for PostgreSQL Backup/Restore Utilities"
echo " all - Backs up the cluster to directory format: ./pgbackup.yyyymmdd/dbname/backupfiles"
echo " restore-all [backupdir] - Restores all DB by directory backup (via all)"
echo " directory|plain|custom [dbname [schema [table]]] - Backup to format (all -> directory)"
echo " restore[-custom] backupname [dbname [schema [table]]] - Restores dir or custom Backup"
echo " restore-plain backupname [dbname [schema]] - Restores backup (using psql)"
echo " migrate fromhost fromdb todb - Migrates DB from one host to another"
echo "Configure with enviroment variables:"
echo " PGUSER=user (default current user)"
echo " PGBACKUPDIR=path (default is current directory)"
echo " PGHOST=host|ip (default localhost)"
echo " PGJOBS=4 (number of backup jobs, default 4)"
echo " PGDATA=path (PostgreSQL Data Dir, defaults to 'show data_directory')"
echo " PGOPTS=.... (additional options to pass on command line"
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment