Failover & Failback for PostgreSQL on Microsoft Azure

It’s pretty common to use the cloud to store your data or as a failover option in the case of master failure. There are several cloud providers which allow you to store, manage, retrieve, and manipulate data via a cloud platform; accessible over the internet. Each cloud provider has its own product offerings and unique features, each with different cost models. 

Microsoft Azure is one of these could providers. In this blog, we’ll take a look at what features Microsoft Azure offers for primary storage, as a disaster recovery site, and specifically look at how it handles a mixed PostgreSQL database environment.

Deploying a PostgreSQL Database Instance on Microsoft Azure

Before performing this task, you need to decide how you will use this instance and which Azure product is best for you. There are two basic ways to deploy a PostgreSQL instance on Microsoft Azure.

1. Azure Database for PostgreSQL: Is a managed service that you can use to run, manage, and scale highly-available PostgreSQL databases in the cloud. It’s available in two deployment options: Single Server and Hyperscale.
2. Virtual Machine: Provides an on-demand, high-scale, secure, virtualized infrastructure. It has support for Ubuntu Server, RedHat Enterprise Linux, SUSE Linux Enterprise Server, CentOS, Debian, and Windows Server and it allows you to develop, test, run applications, and extend your datacenter in just a few seconds.

For this blog we will take a look at both how we can create an Azure Database for PostgreSQL and use a Virtual Machine Azure from the Microsoft Azure Portal.

Deploying Azure Database for PostgreSQL

If you go to your Azure Portal -> Create a Resource -> Databases -> Azure Database for PostgreSQL, you’ll be able to choose between Single Server or Hyperscale. For this blog, we’ll use a Single Server, as the Hyperscale option is on preview and it doesn’t offer an SLA yet.

Here you need to add some information about your new PostgreSQL instance; such as subscription, server name, user credentials, and location. You can also choose which PostgreSQL version to use (9.5, 9.6, 10 or 11 versions are currently available) and the virtual hardware to run it (Compute + Storage).

When you specify the hardware, you’ll see the estimated price in real-time. This is really useful to avoid a big surprise next month. After this step, you just have to confirm the resource configuration and wait a couple minutes until Azure finishes the creation job.

When you have the new resource created, you can go to All Resources to see the resource options available.

In the created resource options, you can go to Replication to enable it and replicate from the master server to up to five replicas. You should also check the Connection Security section to enable or disable external access. To know the access information, you must visit the overview resource section.

$ psql -h pg1blog.postgres.database.azure.com -U severalnines@pg1blog postgres

Password for user severalnines@pg1blog:

psql (11.5, server 11.4)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

postgres=>

Failover on Azure Database for PostgreSQL

Unfortunately, automated failover between master and replica servers is not available. If you delete the master instance, however, Azure will perform a failover process to promote the replica in an automatic way.

There is an option to perform this failover task manually, which requires you to stop the replica and configure the new endpoint into your application to point to the new master. The replica will be promoted and delinked from the master. There is not a way to relink this replica to your master again.

Deploying PostgreSQL on Azure Virtual Machine

If you go to your Azure Portal -> Create a Resource -> Compute -> Virtual Machine, you’ll open the Create a virtual machine section where you can specify different configurations for your new Azure Virtual Machine.

In the basic tab, you must specify the Azure subscription, Region, Availability options, Operating System, Server Size, access credentials (username/password or SSH Key), and inbound firewall rules.

In the disk tab, you must specify the storage (type and size) for your new virtual machine. The disk type can be Standard HDD, Standard SSD, or Premium SSD. The last one is recommended for high IOPS workloads.

In the networking tab, you can specify the virtual network, public IP address, and the allowed inbound ports. You can also add this new virtual machine behind an exiting Azure load balancing solution.

In the next tab, we have some management options, like monitoring and backups. 

And finally, in the advanced tab, we can add extensions, cloud-init, or host groups.

After reviewing the previous option and confirming it, you’ll have your new virtual machine created and accessible from the Azure Portal. In the Resource -> Overview section, you can see the virtual machine access information (Public/Private IP Address).

Now, you can access it via SSH and install the PostgreSQL database using ClusterControl.

$ ssh 23.102.177.27

Last login: Mon Sep 23 21:33:27 2019

[sinsausti@vm1test ~]$

You can check this link to see the steps to perform the PostgreSQL deployment with ClusterControl.

PostgreSQL Failover on Azure Virtual Machine

Disaster recovery is a Virtual Machine feature under the Operations section that allows you to replicate your environment in another Azure region. To enable it, you need to choose the target region. In the advanced tab, you can modify the specific target details; such as virtual network, storage settings, and replication settings.

When the disaster recovery is enabled, you’ll be able to check the replication status, test the failover process, or manually failover to it.

Enabling this allows you to have a failover option in the case of failure. This, however, will be a failover for entire environment and not just the database service.

An Improved PostgreSQL Failover Process for Microsoft Azure

As you have SSH access, you can improve this failover process by importing the virtual machine (or even deploying the PostgreSQL database) with ClusterControl.

If you’re managing the database nodes with ClusterControl (and if the “Auto Recovery” option is ON) in the case of master failure, ClusterControl will promote the most advanced slave (if it is not blacklisted) to master as well as notify you of the problem. It also automatically fails over the rest of the slaves to replicate from the new master.

With ClusterControl, you even also deploy a mixed environment with some nodes on the cloud and other nodes on-prem. You can also add load balancers to your topology to improve our high availability environment. You can find more information about this topic here.

Conclusion

Azure has a lot of features and products to offer an enterprise-level solution. During these tests, however, the main issue I found was that the time to creation and failover was too lengthy for most application needs.

If you need a fast failover and recovery, you should improve the availability of the environment by using a load balancer, or an external system like ClusterControl, to decrease downtime. For more detailed information about running PostgreSQL on Microsoft Azure you can take a look at our deep dive blog.