PostgresSQL Server Database Restore - Single processor

pg_restore_single_cpu.sh

#!/bin/bash
CLIENT=" <description for the client who we make backup for> "

SRC_IP_ADD= <ip_address> 
SRC_NAS_BACKUP="/backup-nas"
DST_PG_BACKUP="/tmp/postgres/backuprestore"
NAS_BACKUP_DB="backup-db"

### Operational directory, eg. if we have ssd drive, opinting to it, will
### significally make process faster !
OPER_DIR="/ssd/postgres/backuprestore"

LOG_DIR="/root/scripts/logs"

### here, we specify postgres database password in a file called "DBprod"
cat /root/scripts/sifre/DBprod | sshfs $SRC_IP_ADD:$SRC_NAS_BACKUP $DST_PG_BACKUP -o password_stdin

### sshfs is mounting in the following mount point : $DST_PG_BACKUP
### and after, we're going in it :
cd $DST_PG_BACKUP
cd $NAS_BACKUP_DB

###
echo "PostgresSQL database backup from a production, started : `date '+[%F] - [%X]'` " >> "$LOG_DIR/pg_restore.log"
cat "$LOG_DIR/pg_restore.log" | tail -n 1 | sh /root/scripts/mail/posaljimail44.sh "dobri7@gmail.com" "$CLIENT, Databse backup started"

### Copy the latest backup file in operational directoy ...
cp -pv "`ls -tr | tail -1`" $OPER_DIR

### The full path for the backup file with "gz" extension :
ZIPPED_BACKUP_FILE="$OPER_DIR/`ls -tr | tail -1`"

echo "Transfer for the production backup file completed : `date '+[%F] - [%X]'` " >> "$LOG_DIR/pg_restore.log"

### Current folder MUST be changed, otherwise, unmount of the $DST_PG_BACKUP will fail
cd $OPER_DIR
fusermount -u $DST_PG_BACKUP


##################
#	               #
#   DB RESTORE   #
#		             #
##################

PGHOST="localhost"
PGUSER="postgres"
PGPASSWORD=" <postgres password> "
export PGPASSWORD

echo "Database restore, start time : `date '+[%F] - [%X]'` " >> "$LOG_DIR/pg_restore.log"

yesterdayDate=`date -d "1 days ago" +"%d-%m"`
UNZIPPED_BACKUP_FILE="$OPER_DIR/RC-$yesterdayDate".back

nice -n 19 gunzip -c $ZIPPED_BACKUP_FILE  > $UNZIPPED_BACKUP_FILE
createdb "RC-$yesterdayDate" -h localhost -U postgres
psql "RC-$yesterdayDate" -h $PGHOST -U $PGUSER  -f $UNZIPPED_BACKUP_FILE

rm -f $ZIPPED_BACKUP_FILE
rm -f $UNZIPPED_BACKUP_FILE
PGPASSWORD=""

echo "Database restore, completition time : `date '+[%F] - [%X]'` " >> "$LOG_DIR/pg_restore.log"

### E-mail notification, last 4 lines of the log file :
cat $LOG_DIR/pg_restore.log | tail -n 4 | sh /root/scripts/mail/posaljimail44.sh "email-address" "$CLIENT, database restore completed."

The simplest way for restoring a database backup

In this example, we use single processor with one core to perform database restore

PGPASSWORD=" "
export PGPASSWORD
createdb "new database name" -h localhost -U postgres
psql "new database name" -h localhost -U postgres -f /path/to/backup/file.back

Concrete example :

PGPASSWORD="8eG6uzshOdh"
export PGPASSWORD
createdb "db-prod-2017-03-21" -h localhost -U postgres
psql "db-prod-2017-03-21" -h localhost -U postgres -f /run/shm/db-prod.20-03.back

Concrete example 2 : Linux backup, Windows restore 👍

C:\Program Files\PostgreSQL\9.6\bin>psql.exe -U postgres -d "database_restored" -f "D:\Users\dobri\Desktop\database_restored_25032019.back"