PostgreSQL Database Backup and Restoring using Database Template

backup_db.sh

#!/usr/bin/env bash
SECONDS=0
PROJECT_PATH=/var/webapps/myproject
CRON_LOG_FILE=${PROJECT_PATH}/logs/backup_db.log
WEEK_DATE=$(LC_ALL=en_US.UTF-8 date +"%w-%A")
BACKUP_PATH=${PROJECT_PATH}/db_backups/${WEEK_DATE}.backup
LATEST_PATH=${PROJECT_PATH}/db_backups/latest.backup

source ${PROJECT_PATH}/venv/bin/activate
cd ${PROJECT_PATH}/project/myproject || exit 1

DATABASE=$(echo "from django.conf import settings; print(settings.DATABASES['default']['NAME'])" | python manage.py shell)

script_errors=0

cd ${PROJECT_PATH} || exit
mkdir -p logs
mkdir -p db_backups

echo "Creating DB Backup" > ${CRON_LOG_FILE}
date >> ${CRON_LOG_FILE}

echo "Dump database" >> ${CRON_LOG_FILE}
pg_dump --format=p \
        --encoding=UTF8 \
        --no-owner \
        --no-privileges \
        --no-comments \
        --file="${BACKUP_PATH}" \
        ${DATABASE} || exit 1
        
function_exit_code=$?
if [[ $function_exit_code -ne 0 ]]; then
    {
        echo "<<<<<<<<<<<<"
        echo "Function exit code is non-zero ($function_exit_code) for command pg_dump"
        echo ">>>>>>>>>>>>"
        script_errors=$((script_errors + 1))
    } >> "${CRON_LOG_FILE}" 2>&1
else
    echo "No error running command pg_dump" >> "${CRON_LOG_FILE}" 2>&1
fi

gzip --force "${BACKUP_PATH}"
function_exit_code=$?
if [[ $function_exit_code -ne 0 ]]; then
    {
        echo "<<<<<<<<<<<<"
        echo "Function exit code is non-zero ($function_exit_code) for command gzip"
        echo ">>>>>>>>>>>>"
        script_errors=$((script_errors + 1))
    } >> "${CRON_LOG_FILE}" 2>&1
else
    echo "No error running command gzip" >> "${CRON_LOG_FILE}" 2>&1
fi

if [ -e "${LATEST_PATH}.gz" ]; then
    rm "${LATEST_PATH}.gz"
fi
ln -s "${BACKUP_PATH}.gz" "${LATEST_PATH}.gz"

echo "Finished." >> ${CRON_LOG_FILE}
duration=$SECONDS
echo "$((duration / 60)) minutes and $((duration % 60)) seconds elapsed." >> ${CRON_LOG_FILE}

if [[ $script_errors -ne 0 ]]; then
    {
        echo "<<<<<<<<<<<<"
        echo "Script encountered $script_errors errors during execution"
        echo ">>>>>>>>>>>>"
    } >> "${CRON_LOG_FILE}" 2>&1
fi

exit $script_errors

provision.yml

- name: Create template database with extensions
  become_user: postgres
  postgresql_db:
    name: db_template_with_extensions
    encoding: "UTF-8"
    template: template0
  tags: [provision, database]

- name: Install PostGIS extension in template
  become_user: postgres
  become: True
  postgresql_ext:
    db: db_template_with_extensions
    name: postgis
  tags: [provision, database]

- name: Install pg_trgm extension in template
  become_user: postgres
  become: True
  postgresql_ext:
    db: db_template_with_extensions
    name: pg_trgm
  tags: [provision, database]

- name: Grant all privileges on template database to db user
  become_user: postgres
  postgresql_query:
    db: db_template_with_extensions
    query: "GRANT ALL PRIVILEGES ON DATABASE db_template_with_extensions TO {{ db_user }};"
  tags: [provision, database]

- name: Make db_template_with_extensions a template database
  become_user: postgres
  postgresql_query:
    db: postgres
    query: "UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'db_template_with_extensions';"
  tags: [provision, database]

- name: Create postgresql db from the db template
   become_user: postgres
   postgresql_db:
     name: "{{ db_name }}"
     owner: "{{ db_user }}"
     encoding: "UTF-8"
     template: db_template_with_extensions
   notify:
     - Restart postgres
   tags: [provision, database]

restore_db.sh

#!/usr/bin/env bash
SECONDS=0
PROJECT_PATH=/var/webapps/myproject
LATEST_PATH=${PROJECT_PATH}/db_backups/latest.backup
export DJANGO_SETTINGS_MODULE=myproject.settings.production

source ${PROJECT_PATH}/venv/bin/activate
cd ${PROJECT_PATH}/project/myproject || exit 1

DATABASE=$(echo "from django.conf import settings; print(settings.DATABASES['default']['NAME'])" | python manage.py shell)
USER=$(echo "from django.conf import settings; print(settings.DATABASES['default']['USER'])" | python manage.py shell)
PASSWORD=$(echo "from django.conf import settings; print(settings.DATABASES['default']['PASSWORD'])" | python manage.py shell)
HOST=$(echo "from django.conf import settings; print(settings.DATABASES['default']['HOST'])" | python manage.py shell)
PORT=$(echo "from django.conf import settings; print(settings.DATABASES['default']['PORT'])" | python manage.py shell)

export PGPASSWORD=$PASSWORD
export PGCLIENTENCODING=UTF8

psql --username=$USER --host=$HOST --port=$PORT --dbname=$DATABASE --command='SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();'
dropdb --username=$USER --host=$HOST --port=$PORT $DATABASE

echo "Creating database from db_template_with_extensions (includes PostGIS and pg_trgm extensions)..."
createdb --username=$USER --host=$HOST --port=$PORT --encoding=UTF8 --template=db_template_with_extensions $DATABASE

echo "Restoring database..."
zcat "${LATEST_PATH}.gz" | psql --username=$USER --host=$HOST --port=$PORT --dbname=$DATABASE --variable=ON_ERROR_STOP=0 --quiet 2>&1 | grep -v -E "(no privileges were granted for|must be owner of extension)"

unset PGPASSWORD
unset PGCLIENTENCODING

echo "Finished."
duration=$SECONDS
echo "$((duration / 60)) minutes and $((duration % 60)) seconds elapsed."