Postgres Database Backup & Restore Script

Backup

======

[postgres@prod log]$ cat /u01/database_backup/db_backup.sh

PATH=/u01/opt/PostgreSQL/11/bin/pg_dump:.:$PATH; export PATH

PATH=/u01/opt/PostgreSQL/11/bin:$PATH:$HOME/.local/bin:$HOME/bin

export PATH

~

export PGDATA=/u01/opt/PostgreSQL/11/data

export PGUSER=postgres

export PGDATABASE=postgres

MYDB=postgresql://postgres:postgres@127.0.0.1:5432/appdb; export MYDB

pg_dump -v -Fc --dbname=$MYDB --file /u01/database_backup/appdb_$(date +%Y-%m-%d).dump 2> /u01/database_backup/log/appdb_export_log_$(date +%Y-%m-%d).log

Restore

========

[postgres@uat log]$ cat /u01/database_backup/database_restore.sh

PATH=/u01/opt/PostgreSQL/11/bin/pg_dump:.:$PATH; export PATH

PATH=/u01/opt/PostgreSQL/11/bin:$PATH:$HOME/.local/bin:$HOME/bin

export PATH

~

export PGDATA=/u01/opt/PostgreSQL/11/data

export PGUSER=postgres

export PGDATABASE=postgres

PGPASSWORD=postgres; export PGPASSWORD

psql -h db.uat -U postgres -d postgres  -c "SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'appdb';"

psql -h db.uat -U postgres -d postgres  -c "drop  DATABASE appdb;"

psql -h db.uat -U postgres -d postgres  -c "CREATE DATABASE appdb WITH OWNER = appusr ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TABLESPACE = appdb_data CONNECTION LIMIT = -1;"

pg_restore -c -C -v -j 4 -Fc -d postgres /u01/database_backup/appdb_$(date +%Y-%m-%d).dump > /u01/database_backup/log/appdbRestore_$(date +%Y-%m-%d).txt 2>&1