PostgreSQL 9.6 :: database management - binary

pg-dump.sh

#!/bin/bash
# PostgreSQL database dump

# troubleshooting for
# pg_dump: server version: 9.6.2; pg_dump version: 9.5.6
# pg_dump: aborting because of server version mismatch
#
# commands to verify version mismatch on linux environment
# psql --version
# pg_dump --version
# which pg_dump
# ls -la /usr/bin/pg_dump
# dpkg -l | grep postgres

# Ubuntu Xenial 16.04 :: install the correct postgresql-client-version
# 
# sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# sudo apt-get update
# sudo apt-get install postgresql-9.6
# sudo apt autoremove

start_date="`date +%Y-%m-%d_%H:%M:%S`"

DIR="/opt/data/backup/pgsql"
ERRORLOG="$DIR/pg-error.log"
ERROR=0;

PG_HOST="localhost"
PG_USER="postgres"
PG_PASS=$1
PG_PORT="5432"
PG_DATABASES="wplaces"

PG_CONNECTION="postgresql://$PG_USER:$PG_PASS@$PG_HOST:$PG_PORT/$PG_DATABASES"

if [ -z "$1" ]; then
	echo ">>> dump database password argument not found, exiting...";
	exit
fi

if [ ! -d $DIR ]; then
    mkdir -p $DIR
    chown -R postgres -R $DIR
fi

echo ">>> dump database $PG_DATABASES"
pg_dump $PG_CONNECTION -Fc -v -f $DIR/db-$PG_DATABASES.bkp 2> $ERRORLOG
# pg_dump -d $PG_DATABASES -h $PG_HOST -p $PG_PORT -U $PG_USER -Fc -v -f $DIR/db-$PG_DATABASES.bkp 2> $ERRORLOG
    
if [ "$?" -ne 0 ]; then
    echo "ERROR to create database dump :: '$PG_DATABASES'";
    ERROR=1;
fi
    
echo ">>> compact database dump :: $PG_DATABASES"
tar -cvzf $DIR/db-$PG_DATABASES-`date +"%Y%m%d-%H%M%S"`.tgz -C $DIR db-$PG_DATABASES.bkp 2>> $ERRORLOG

if [ "$?" -ne 0 ]; then
    echo "ERROR to compact database dump :: '$PG_DATABASES'";
    ERROR=1;
fi

# clean .bkp files and old compact files, only last 7 days
rm -rf $DIR/*.bkp
find $DIR/ -name "*.tgz" -mtime +7 -type f -exec rm -f {} \;

echo "PG dump process start at $start_date"
echo "PG dump process end   at `date +%Y-%m-%d_%H:%M:%S`"

pg-restore.sh

#!/bin/bash
# PostgreSQL database restore

start_date="`date +%Y-%m-%d_%H:%M:%S`"

DIR="/opt/backup/postgresql"
LOGFILE="$DIR/pg-restore.log"
ERROR=0;

PG_HOST="localhost"
PG_USER="postgres"
PG_PORT="5432"
PG_DATABASES_TO=$1
PG_FILENAME=$2
PG_PASSWD=$3

if [ -z "$1" ] || [ -z "$2" ] || [ -z "$3" ]; then
    echo ">>> restore database arguments[db-to::filename::password] not found, exiting...";
    exit
fi

echo ">>> restore database $PG_DATABASES_TO"
PGPASSWORD=$PG_PASSWD pg_restore -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DATABASES_TO -v -1 $PG_FILENAME 2> $LOGFILE

if [ "$?" -ne 0 ]; then
    echo "ERROR to restore database dump :: '$PG_DATABASES_TO'";
    ERROR=1;
fi

echo "PG restore process start at $start_date"
echo "PG restore process end   at `date +%Y-%m-%d_%H:%M:%S`"