Automate Backups from Azure Database for PostgreSQL Server to Azure Storage for Long Term Retention

[PedroAcevedo]

You can perform logical backups of your database from Azure Database for PostgreSQL to azure storage accounts for long term retention. These backups can be restored to your On-Premise environment or to another PostgreSQL instance on a Virtual Machine.

Follow the steps below to extract a backup from Azure Database for PostgreSQL to a Storage Account.

In an nutshell, what we will need to do is the following:

1. Use an existing VM or spin a Linux VM in the same region as the database (we used Ubuntu Server for this example).
2. Mount your storage account as file share in the VM.
3. Create a bash script that uses pg_dump to create a backup from your database.
4. Schedule a task to run this script using crontab and to deallocate the VM when completed.
5. Schedule the VM to start before the selected backup time using Logic Apps.

Prerequisites for the steps below:

1. Blob storage account with a File Share
2. A Virtual Machine
3. A Logic Apps instance.

Let’s break down each step.

1. Spin up or use an existing Virtual Machine and configure it:

1. Create a VM from the Azure portal. Refer to this QuickStart guide if needed.
2. Start a remote session to your VM and install the following required packages:

   sudo apt install postgresql-client-10 # installs PostgreSQL client utilities
   sudo apt install cifs-utils # installs the Common Internet File System utilities
   curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash # installs Azure CLI

3. If your PostgreSQL server is version 11 or above, please install the client tools with the following commands:

   sudo apt install wget ca-certificates
   wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
   sudo  apt update
   sudo apt-get install postgresql-client-11 #Or the required version above 10

1. 1.  
2. Mount your storage account as a File Share.

1. Create a File Share in your storage account
   1.  

1. If needed you can refer to this document on how to create an Azure File Share.

2. Collect the following details from the Azure Portal: Resource Group, Storage Account Name and File Share Name.
3. Log into your VM and declare the following variables:

   resourceGroupName="myResourceGroup"
   storageAccountName="myStorageAccount"
   fileShareName="myFileShare"
   mntPath="/home/azureuser/myfolder/"#File share needs to be mounted in the home directory rather than the mnt directory. Otherwise, folder will be deleted after VM deallocation.​

4. Create a folder where the storage account will be mounted:

   mkdir  /home/azureuser/myfolder​

5. Login to your Azure subscription from the VM:

   az login​

6. Check that connection to the storage account through port 445 is possible:

   httpEndpoint=$(az storage account show \
       --resource-group $resourceGroupName \
       --name $storageAccountName \
       --query "primaryEndpoints.file" | tr -d '"')
   smbPath=$(echo $httpEndpoint | cut -c7-$(expr length $httpEndpoint))
   fileHost=$(echo $smbPath | tr -d "/")
    
   nc -zvw3 $fileHost 445​

7. Storage account credentials are stored in the VM:

   sudo mkdir /etc/smbcredentials
   
   storageAccountKey=$(az storage account keys list \
       --resource-group $resourceGroupName \
       --account-name $storageAccountName \
       --query "[0].value" | tr -d '"')
   
   smbCredentialFile="/etc/smbcredentials/$storageAccountName.cred"
   
   if [ ! -f $smbCredentialFile ]; then
       echo "username=$storageAccountName" | sudo tee $smbCredentialFile > /dev/null
       echo "password=$storageAccountKey" | sudo tee -a $smbCredentialFile > /dev/null
   else 
       echo "The credential file $smbCredentialFile already exists, and was not modified."
   fi​

8. Change permissions so only root can read and modify the password file:

   sudo chmod 600 $smbCredentialFile​

9. Append mount point to /etc/fstab:

   httpEndpoint=$(az storage account show \
       --resource-group $resourceGroupName \
       --name $storageAccountName \
       --query "primaryEndpoints.file" | tr -d '"')
   smbPath=$(echo $httpEndpoint | cut -c7-$(expr length $httpEndpoint))$fileShareName
    
   if [ -z "$(grep $smbPath\ $mntPath /etc/fstab)" ]; then
       echo "$smbPath $mntPath cifs $nofail,vers=3.0,credentials=$smbCredentialFile,serverino,dir_mode=0777,file_mode=0$777" | sudo tee -a /etc/fstab > /dev/null
   else
       echo "/etc/fstab was not modified to avoid conflicting entries as this Azure file share was already present. You may want to double check /etc/fstab to ensure the configuration is as desired."
   fi​

10. Mount the storage account:

    sudo mount -a​

3. Create a bash script that uses pg_dump to create a backup from your database. You can write something like this:

#!bin/bash
cd /home/azureuser/<folder to mount storage account>/
export PGPASSWORD="password"
date=$(date +%s)
echo $date
pg_dump -Fc -v --host=dbservername.postgres.database.azure.com --dbname=dbname --username=user@dbservername -f dbtest$date.dump
az vm deallocate -g MyResourceGroup -n MyVm #This deallocates the VM after the backup has been saved to the storage account

4. Schedule a task to run this script using crontab and to deallocate the VM when completed.

crontab -e

For example, use the following line if you would like to have the backup run every Friday at midnight (VM time zone is UTC):
0 0 * * 5 /home/azureuser/backup_script.sh

5. Schedule the VM to start before the selected backup time using Logic Apps.