Connecting Sequelize To a PostgreSQL Cluster

Prologue

In a previous post, I showed how to automate a PostgreSQL fault-tolerant cluster with Vagrant and Ansible.

This kind of setup makes our database cluster resilient to server failure and keeps the data available with no need for human interaction. But what about the apps using this database? Are they fault-tolerant too?

ORMs like Sequelize have read replication features, which allows you to define your primary and standby nodes in the database connection. But what happens if your primary node, which is responsible for write operations, is offline and your app needs to continue saving data on your database?

One way to solve this is by adding an extra layer to the system - a load balancing layer - using PostgreSQL third-party tools like pgbouncer or Pgpool-II or even a properly configured HAproxy instance. Besides the complexity brought by this method, you could also be introducing an undesired single point of failure.

Another way is using a floating IP address/virtual IP address to assign to the current primary database node, so the application knows which node it must connect to when performing write operations even if another node takes up the primary role.

We will be using Digital Ocean for server creation and floating IP assignment, but the strategy also works with other cloud providers who support floating IP.

Objectives

connecting a NodeJS application with Sequelize to a PostgreSQL cluster in order to write to the primary node and read from standby nodes;
create and assign a Digital Ocean Floating IP (aka FLIP) to our current primary database node;
make repmgr interact with Digital Ocean CLI to reassign FLIP to a new primary node on promotions;
keep this switchover transparent to the NodeJS application, so the whole system works without human help.

Pre-Requisites

A Digital Ocean account and API token (create an account using my referral to get free credits)
A PostgreSQL cluster with repmgr on Digital Ocean (you can grab the Ansible playbook in this tutorial to configure it or just use a cluster with streaming replication and simulate failure + manual promotion);
NodeJS and npm installed (I’m using NodeJS v12 with npm v6);
A PostgreSQL user with password authentication which accepts remote connections from your application host (I’ll be using postgres:123456).

Set up Your Cluster

Create Your Droplets

digitalocean

Create 3 droplets, preferably with the Ubuntu 20.04 operating system:

pg1 (primary)
pg2 (standby)
pg3 (witness)

To make configurations run smoother, add your public SSH key when creating the droplets. You can also use the key pair I provided on GitHub for testing purposes.

If you’d like to only use 2 droplets, you can ignore the third node as it will be a PostgreSQL witness.

Note: If you use an SSH private key which is shared publicly on the internet, your cluster can get hacked.

authenication

#tutorial #devops #node.js #postgresql #node #postgres #sequelize