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