Skip to content

Restore database

Mark Scrimshire edited this page Jul 27, 2017 · 1 revision

Postgres Database Restoration Process

  1. Connect to dbserver
  2. Copy backup file from S3
 /usr/local/bin/aws s3 cp s3://bb-dbback-{ env }/{ hourly | daily | weekly | monthly | yearly }/bb_fe_{ env }_pg_01.{ hourly | daily | weekly | monthly | yearly }_{ yyyymmdd }_{ hummus }.sql.bz2.ssl .

s3 buckets:

s3://bb-dbback-{ env}/{frequency = hourly | daily | weekly}/

backup names

{table_name}.{frequency}_yyyyMMdd_hhmmss.sql.bz2.ssl

  1. Install Private key that matches Public Key for DB Backup Private key is in Ansible Environment Vault on mgmt server /hhs_ansible/hhs_ansible/vault/env/{env}/vault.yml current_key is in variable: {{ vault_env_db_backup_private_key_current }}

When a new public/private key pair is created move the ...private_key_current to private_key_nnnn, where nnnn is the next available zero-filled number starting from 0001

Run Ansible script to install private key to /data/pgbackup/restore

  1. run unpack.sh
#!/bin/bash
##################################################
# Unpack file using openssl
# v1.0.0 6/21/2017 @ekivemark
#################################################
# /data/pgsql/9.6/data/db_unpack.sh
# Via template hhs_ansible/roles/postgres/templates/db_unpack.sh.j2
# Parameter: filename
#################################################

rm -f workfile.bz2
rm -f workfile

if [ -f "$1" ]; then
   UNPACK_FILE=$1
else
   if [ -f "$1.ssl" ]; then
      UNPACK_FILE=$1.ssl
   else
      exit 1
   fi
fi

if [ -f "$UNPACK_FILE" ]; then
   echo "decrypting..."
   openssl smime -decrypt -in $UNPACK_FILE \
           -binary -inform DEM \
           -inkey ./backup_key.pem.private   -out workfile.bz2
   echo "decompressing..."
   bunzip2 workfile.bz2
   
else
  echo "nothing to do"
fi

update re_init.sql

Use original db_init.sql or adjust: - DB_TABLE_NAME - USERNAME - PASSWORD

CREATE DATABASE bb_fe_{ env }_pg_02 ;

CREATE USER { user } WITH PASSWORD '{ password }' ;

ALTER ROLE { user } SET client_encoding TO 'utf8@' ;
ALTER ROLE { user } SET default_transaction_isolation TO 'read committed' ;
ALTER ROLE { user } SET timezone to UTC ;

GRANT ALL PRIVILEGES ON DATABASE bb_fe_{ env }_pg_01 to { user };

Initialize Database

Run:

su - postgres
/usr/bin/psql -p 15432  -f /data/pgbackup/restore/re_init.sql 

Update pg_hba.conf

The pg_hba.conf file needs to be updated to allow sql to operate.

vi /data/pgsql/9.6/data/pg_hba.conf

add a line

host    all     all     { db_server_ip_address }/32 md5

Set postgres user password

passwd postgres

Restore Database from backup

Use work file created by unpack.sh

su - postgres
psql -p { port number } -h { ip_address } psql -d { database_name | bb_fe_{ env }_pg_01 |  -U postgres </data/pgbackup/restore/workfile 

Update Appserver settings

Apply new database settings to App server - Rebuild django_settings.sh - Rebuild custom-envvars.py Restart AppServer