Failover

On this page I will be looking at a technique to make our system more resilient to failures. I have made a copy of my Postgres Data Warehouse and added this to my Databricks environment. Note that we could have made a similar setup without using Databricks.

As you can see in the green box below, in Databricks, I now have two catalog entries, one corresponding to each instance of my Postgres Data Warehouse. To make things as robust as possible, ideally they should be on different clouds and maybe in different regions.

How the two databases are synchronized is beyond the scope of this topic, and it will also depend a lot on your particular use case.

Below, let's now look at the Workflow.

In the workflow below, we have added failover.

First, we try to connect to our 'main' database instance. This is done by the DB Table Selector node (postgres Proton instance) node within the Try/Catch pair of nodes.

If this works, the Catch node will 'pass control' to the DB Reader node 

However, if for some reason this fails (database down, connection error,....) , The Catch node will 'pass control' to the other DB Table Selector node (postgres Bocebad instance).

As in any case, only one of the two DB Reader nodes will return data, we can concatenate their outputs to get access to our data.

This workflow can easily be extended to, for example, add a second fallover database and/or add a Send Email node to alert the Administrator in case there was a problem with one of our databases