Skip to content

Backup Database for Migration #22

Backup Database for Migration

Backup Database for Migration #22

name: Backup Database for Migration
on:
workflow_dispatch:
inputs:
environment:
description: Environment to backup
required: true
default: test
type: choice
options:
- test
- preprod
- production
jobs:
backup:
name: Backup Azure Database
runs-on: ubuntu-latest
environment:
name: ${{ inputs.environment }}
steps:
- name: Checkout code
uses: actions/checkout@v4
- uses: Azure/login@v2
with:
creds: ${{ secrets.azure_credentials }}
- name: Set environment variables
shell: bash
run: |
tf_vars_file="terraform/workspace_variables/${{ inputs.environment }}.tfvars.json"
echo "KEY_VAULT_NAME=$(jq -r '.key_vault_name' ${tf_vars_file})" >> $GITHUB_ENV
echo "RESOURCE_PREFIX=$(jq -r '.resource_prefix' ${tf_vars_file})" >> $GITHUB_ENV
echo "ENV=$(jq -r '.environment_name' ${tf_vars_file})" >> $GITHUB_ENV
echo "RESOURCE_GROUP_NAME=$(jq -r '.resource_group_name' ${tf_vars_file})" >> $GITHUB_ENV
echo "BACKUP_FILE_NAME=rsm-${{ inputs.environment }}-psql-$(date +'%F-%H')" >> $GITHUB_ENV
echo "RUNNER_IP=$(curl ifconfig.me)" >> $GITHUB_ENV
source global_config/${{ inputs.environment }}.sh
resource_prefix=$(jq -r '.resource_prefix' ${tf_vars_file})
resource_prefix="${resource_prefix//-/}"
echo "BACKUP_STORAGE_GROUP=${resource_prefix}dbbackup${CONFIG_SHORT}" >> $GITHUB_ENV
- name: Set postgres environment variables
shell: bash
run: |
echo "POSTGRES_SERVER_NAME=${{ env.RESOURCE_PREFIX }}-${{ env.ENV }}-psql" >> $GITHUB_ENV
echo "POSTGRES_SERVER_HOST_NAME=${{ env.RESOURCE_PREFIX }}-${{ env.ENV }}-psql.postgres.database.azure.com" >> $GITHUB_ENV
echo "POSTGRES_DATABASE_NAME=refer_serious_misconduct_production" >> $GITHUB_ENV
- name: Get BACKUP_STORAGE_CONNECTION_STRING
run: |
BACKUP_STORAGE_ACCESS_KEY="$(az storage account keys list -g ${{ env.RESOURCE_GROUP_NAME }} -n ${{ env.BACKUP_STORAGE_GROUP }} | jq -r '.[0].value')"
echo "::add-mask::$BACKUP_STORAGE_ACCESS_KEY"
echo "BACKUP_STORAGE_CONNECTION_STRING=DefaultEndpointsProtocol=https;AccountName=${{ env.BACKUP_STORAGE_GROUP }};AccountKey=${BACKUP_STORAGE_ACCESS_KEY};EndpointSuffix=core.windows.net" >> $GITHUB_ENV
shell: bash
- uses: DfE-Digital/keyvault-yaml-secret@v1
id: get_monitoring_secret
with:
keyvault: ${{ env.KEY_VAULT_NAME }}
secret: MONITORING
key: SLACK_WEBHOOK
env:
GITHUB_TOKEN: ${{secrets.GITHUB_TOKEN}}
- uses: DfE-Digital/keyvault-yaml-secret@v1
id: get_infrastructure_secrets
with:
keyvault: ${{ env.KEY_VAULT_NAME }}
secret: INFRASTRUCTURE
key: POSTGRES_ADMIN_USERNAME,POSTGRES_ADMIN_PASSWORD
env:
GITHUB_TOKEN: ${{secrets.GITHUB_TOKEN}}
- name: Add firewall rule
uses: azure/CLI@v2
with:
azcliversion: 2.30.0
inlineScript: |
az postgres flexible-server firewall-rule create --name ${{ env.POSTGRES_SERVER_NAME }} --resource-group ${{ env.RESOURCE_GROUP_NAME }} --rule-name Allow-GithubRunner-Postgres --start-ip-address ${{ env.RUNNER_IP }} --end-ip-address ${{ env.RUNNER_IP }}
- name: Add PG PASS
shell: bash
run: |
echo '${{ env.POSTGRES_SERVER_HOST_NAME }}:5432:${{ env.POSTGRES_DATABASE_NAME }}:${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_USERNAME }}:${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_PASSWORD }}' >> ~/.pgpass
chmod 600 ~/.pgpass
- name: Run PG Dump
shell: bash
run: |
export PGSSLMODE=require
pg_dump -E utf8 --clean --compress=1 --if-exists --no-owner --verbose --host=${{ env.POSTGRES_SERVER_HOST_NAME }} --port=5432 --username=${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_USERNAME }} --dbname=${{ env.POSTGRES_DATABASE_NAME }} -f ${{ env.BACKUP_FILE_NAME }}.sql.gz
# - name: Run PG Dump
# shell: bash
# run: |
# export PGSSLMODE=require
# pg_dump -Fc -v --host=${{ env.POSTGRES_SERVER_HOST_NAME }} --port=5432 --username=${{ steps.get_infrastructure_secrets.outputs.POSTGRES_ADMIN_USERNAME }} --dbname=${{ env.POSTGRES_DATABASE_NAME }} > ${{ env.BACKUP_FILE_NAME }}.sql
# zip -r ${{ env.BACKUP_FILE_NAME }}.sql.zip ${{ env.BACKUP_FILE_NAME }}.sql
- name: Delete firewall rule
if: always()
uses: azure/CLI@v2
with:
azcliversion: 2.30.0
inlineScript: |
az postgres flexible-server firewall-rule delete --name ${{ env.POSTGRES_SERVER_NAME }} --resource-group ${{ env.RESOURCE_GROUP_NAME }} --rule-name Allow-GithubRunner-Postgres --yes
- name: Upload Backup to Azure Storage
run: |
az storage blob upload --container-name rsm \
--file ${BACKUP_FILE_NAME}.sql.gz --name ${BACKUP_FILE_NAME}.sql.gz \
--connection-string '${{ env.BACKUP_STORAGE_CONNECTION_STRING }}' \
--overwrite true