• Categories
  • Load Balancer
  • Create a High-Availability PostgreSQL Cluster with Patroni, Vultr Load Balancer, and Object Storage

Article

Table of Contents
Theme:
Was this article helpful?

2  out of  2 found this helpful

Try Vultr Today with

$50 Free on Us!

Get started now
Want to contribute?

You could earn up to $600 by adding new articles.

Learn more!

Create a High-Availability PostgreSQL Cluster with Patroni, Vultr Load Balancer, and Object Storage

Author: Quan Hua Hong

Last Updated: Wed, Jun 28, 2023
Load Balancer PostgreSQL

Introduction

PostgreSQL is a powerful, open-source relational database system. Patroni is a tool to create, manage, and monitor a high-availability PostgreSQL cluster using streaming replication and automated failover. etcd is a distributed key-value store that Patroni uses to store all the configurations.

This article uses Patroni and etcd to automate and simplify the deployment and management of PostgreSQL clusters. In a few minutes, you can have a production-grade Postgres cluster with high availability and disaster recovery.

Prerequisites

Before you begin, make sure you:

Server Configuration

This section describes how you can set up a cluster of two Patroni instances, and three etcd instances to store the configuration. The Patroni setup contains one PostgreSQL primary and one replica. The etcd cluster contains three instances to enable high availability, which can cover at most one instance failure.

In this article, deploy five servers with 1GB of memory each. But production deployments require higher memory servers. These servers should be independent of each other. For example, they can be in different locations, or in a single Vultr Virtual Private Cloud (VPC).

Create the Vultr Server Instances

  1. Log in to your Vultr account.

  2. Deploy 5 Vultr Server Instances in any location.

  3. Label the server instances as etcd-1, etcd-2, etcd-3, patroni-1 and patroni-2.

  4. Use SSH to access each of the servers as a non-root sudo user.

This article uses the following example names and addresses:

  • etcd-1: etcd-server-1-ip

  • etcd-2: etcd-server-2-ip

  • etcd-3: etcd-server-3-ip

  • patroni-1: patroni-server-1-ip

  • patroni-2: patroni-server-2-ip

Configure the etcd Cluster

This section describes how to set up an etcd cluster on the etcd-1, etcd-2, and etcd-3 servers.

Install etcd

  1. Run the following command to download and install the etcd binary. Visit the etcd GitHub Repository for the latest version.

    $ ETCD_VER=v3.5.7

$ DOWNLOAD_URL=https://github.com/etcd-io/etcd/releases/download



$ curl -L ${DOWNLOAD_URL}/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz -o /tmp/etcd-${ETCD_VER}-linux-amd64.tar.gz

$ tar xzvf /tmp/etcd-${ETCD_VER}-linux-amd64.tar.gz -C /tmp



$ sudo mv /tmp/etcd-${ETCD_VER}-linux-amd64/etcd /usr/bin  

$ sudo mv /tmp/etcd-${ETCD_VER}-linux-amd64/etcdctl /usr/bin  

$ sudo mv /tmp/etcd-${ETCD_VER}-linux-amd64/etcdutl /usr/bin  



$ sudo chown root:root /usr/bin/etcd

$ sudo chown root:root /usr/bin/etcdctl

sudo chown root:root /usr/bin/etcdutl



$ rm -rf /tmp/etcd-${ETCD_VER}-linux-amd64*

  2. Verify the installed version.

    $ etcd --version

Set Up the etcd Service

Perform the following steps to set up the etcd service on each etcd server.

  1. Create a new service user and prepare folder permissions as below.

    $ sudo useradd --system --no-create-home etcd

$ sudo mkdir -p /etc/etcd /var/lib/etcd

$ sudo chown etcd:etcd /etc/etcd

$ sudo chown etcd:etcd /var/lib/etcd

  2. Create the configuration file /etc/etcd/etcd.conf.yml with the following configurations.

    name: YOUR_INSTANCE_NAME

data-dir: /var/lib/etcd

initial-cluster-state: 'new'

initial-cluster-token: 'etcd-cluster'

initial-cluster: YOUR_INITIAL_CLUSTER

initial-advertise-peer-urls: http://YOUR_INSTANCE_ADDRESS:2380

advertise-client-urls: http://YOUR_INSTANCE_ADDRESS:2379

listen-peer-urls: 'http://YOUR_INSTANCE_ADDRESS:2380,http://127.0.0.1:2380'

listen-client-urls: 'http://YOUR_INSTANCE_ADDRESS:2379,http://127.0.0.1:2379'

enable-v2: true

  3. Using a text editor such as Vim. Open the file /etc/etcd/etcd.conf.yml.

    $ sudo vim /etc/etcd/etcd.conf.yml

    Replace YOUR_INITIAL_CLUSTER with the following line. Replace etcd-server-1-ip, etcd-server-2-ip and etcd-server-3-ip with the corresponding IP addresses of your etcd servers.

    etcd1=http://etcd-server-1-ip:2380,etcd2=http://etcd-server-2-ip:2380,etcd3=http://etcd-server-3-ip:2380

    Replace YOUR_INSTANCE_NAME and YOUR_INSTANCE_ADDRESS with your etcd server name and IP Address.

    This article uses the following example YOUR_INSTANCE_NAME and YOUR_INSTANCE_ADDRESS for each server:

  • etcd-1: instance name is etcd-1 and instance address is etcd-server-1-ip

  • etcd-2: instance name is etcd-2 and instance address is etcd-server-2-ip

  • etcd-3: instance name is etcd-3 and instance address is etcd-server-3-ip

  1. Create the /usr/lib/systemd/system/etcd.service file with the following content.

    [Unit]

Description="etcd service"

After=network.target



[Service]

LimitNOFILE=65536

Restart=always

Type=notify

ExecStart=/usr/bin/etcd --config-file /etc/etcd/etcd.conf.yml

User=etcd

Group=etcd



[Install]

WantedBy=multi-user.target

  2. Allow the ports 2379 and 2380 through the firewall.

    $ sudo ufw allow 2379 && sudo ufw allow 2380

  3. Enable & start the etcd service.

    $ systemctl enable etcd.service

$ systemctl start etcd.service

Check the etcd Service

  1. Check etcd service logs to make sure there are no errors.

    $ journalctl -u etcd.service -f

  2. Get the list of members in the etcd cluster.

    $ etcdctl member list -w table

  3. Save a test key-value pair to the etcd cluster.

    $ etcdctl put greeting "Hello World!"

  4. Get the test key value from any etcd node.

    $ etcdctl get greeting

Configure the Patroni Cluster

This section shows how to set up a Patroni cluster on the patroni-1 and patroni-2 servers.

Install PostgreSQL

  1. Install PostgreSQL on both Patroni servers.

    $ sudo apt install -y postgresql postgresql-contrib

  2. Stop the PostgreSQL service.

    $ systemctl stop postgresql

  3. Disable the PostgreSQL service.

    $ systemctl disable postgresql

Install Patroni

  1. Install Patroni and other dependencies on both Patroni servers.

    $ sudo apt install -y python3-pip python3-dev libpq-dev

$ pip3 install --upgrade pip

$ pip install patroni python-etcd psycopg2

  2. Create the configuration file /etc/patroni.yml with the following configurations.

    scope: patroni_cluster

name: YOUR_INSTANCE_NAME

namespace: /service



restapi:

 listen: 'YOUR_INSTANCE_ADDRESS:8008'

 connect_address: 'YOUR_INSTANCE_ADDRESS:8008'



etcd:

 hosts: YOUR_ETCD_HOSTS



bootstrap:

 dcs:

   ttl: 30

   loop_wait: 10

   retry_timeout: 10

   maximum_lag_on_failover: 1048576

   postgresql:

     use_pg_rewind: true

     use_slots: true

     parameters:

       hot_standby: 'on'

       wal_keep_segments: 20

       max_wal_senders: 8

       max_replication_slots: 8

   slots:

     patroni_standby_leader:

       type: physical

 initdb:

   - encoding: UTF8

   - data-checksums

 pg_hba:

   - host replication replicator 0.0.0.0/0 md5

   - host all all 0.0.0.0/0 md5

 users:

   admin:

     password: admin%

     options:

       - createrole

       - createdb

postgresql:

 listen: 'YOUR_INSTANCE_ADDRESS:5432'

 connect_address: 'YOUR_INSTANCE_ADDRESS:5432'

 data_dir: /var/lib/postgresql/data

 pgpass: /tmp/pgpass0

 authentication:

   replication:

     username: replicator

     password: replicate%

   superuser:

     username: postgres

     password: postgres%

   rewind:

     username: rewind_user

     password: rewind_password%

tags:

 nofailover: false

 noloadbalance: false

 clonefrom: false

 nosync: false

  3. Open the file /etc/patroni.yml.

    $ sudo vim /etc/patroni.yml

    Replace YOUR_ETCD_HOSTS with the following line. Replace etcd-server-1-ip, etcd-server-2-ip and etcd-server-3-ip with the corresponding IP addresses of your etcd servers.

    etcd-server-1-ip:2379,etcd-server-2-ip:2379,etcd-server-3-ip:2379

    Replace YOUR_INSTANCE_NAME and YOUR_INSTANCE_ADDRESS with your patroni server name and IP address.

    This article uses the following example YOUR_INSTANCE_NAME and YOUR_INSTANCE_ADDRESS for each server:

    • patroni-1: instance name is patroni-1 and instance address is patroni-server-1-ip

    • patroni-2: instance name is patroni-2 and instance address is patroni-server-2-ip

  4. Create the /usr/lib/systemd/system/patroni.service file with the following contents.

    [Unit]

Description="patroni service"

After=syslog.target network.target



[Service]

Restart=no

Type=simple

ExecStart=/usr/local/bin/patroni /etc/patroni.yml

ExecReload=/bin/kill -s HUP $MAINPID

KillMode=process

TimeoutSec=30

User=postgres

Group=postgres



[Install]

WantedBy=multi-user.target

  5. Allow the ports 5432 and 8008 through the firewall.

    $ sudo ufw allow 5432

$ sudo ufw allow 8008

  6. Enable & start the Patroni service.

    $ sudo systemctl enable patroni.service

$ sudo systemctl start patroni.service

Check the Patroni Service

  1. Check Patroni service logs to make sure there are no errors.

    $ sudo journalctl -u patroni.service -f

  2. Get the list of members in the Patroni cluster.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  3. On the Patroni primary node, connect to the PostgreSQL with psql command.

    $ psql -U postgres;

  4. Create a table to test the data replication.

    create table mydata(key text, value text);

insert into mydata values('foo', 'bar');

  5. On the Patroni replica node, query the data from the new table.

    $ psql -U postgres -c "select * from mydata;"

Deploy Vultr Load Balancer

This section shows how to deploy a Vultr Load Balancer to forward traffic to the PostgreSQL primary. The Load balancer performs health checks on the Patroni REST API in each Patroni instance to know which instance is the PostgreSQL primary.

Create Vultr Load Balancer

  1. Log in to your Customer Portal and deploy a new Vultr Load Balancer.

  2. Label the Load Balancer Configuration as patroni-primary.

  3. In Forwarding Rules, set the following configuration:

    • Load Balancer: Protocol to TCP and Port to 5432

    • Instance: Protocol to TCP and Port to 5432

  4. In Health Checks, set the following configuration:

    • Protocol: HTTP

    • Port: 8008

    • Interval: 3

    • HTTP Path: /

  5. In Firewall Rules, click Add firewall rule and set the following configuration:

    • Port: 5432

    • IP type: IPv4

    • Source: 0.0.0.0/0

  6. Go to your Customer Portal and open your Load Balancer.

  7. Click Add instance and select your Patroni instances.

Connect to the PostgreSQL Cluster using the Load Balancer

Connect to PostgreSQL using psql. Replace <LOAD_BALANCER_IP> with your Load Balancer IP. Enter the password from the /etc/patroni.yml file in the previous step.

$ psql -h <LOAD_BALANCER_IP> -U postgres

Prepare a Vultr Object Storage

This section describes how to deploy a Vultr Object Storage to store the Write-AHead-Logging files and daily backups of your Postgres cluster.

  1. Create a Vultr Object Storage.

  2. Create a bucket patroni-demo-bucket inside the Object Storage.

  3. Get your Vultr Object Storage Hostname, Access Key and Secret Key.

Backup with pgBackRest

This section describes how to set up a backup strategy with pgBackRest in the Patroni cluster on the patroni-1, and patroni-2 servers.

Install pgBackRest

  1. Install pgBackRest on both Patroni servers.

    $ sudo apt install -y pgbackrest

  2. Prepare a directory for pgBackRest.

    $ sudo mkdir -p /var/lib/pgbackrest

$ sudo chmod 0750 /var/lib/pgbackrest

$ sudo chown -R postgres:postgres /var/lib/pgbackrest

$ sudo chown -R postgres:postgres /var/log/pgbackrest

  3. Create a new file /etc/pgbackrest.conf with the following contents.

    [demo-cluster]  

pg1-path=/var/lib/postgresql/data

pg1-port=5432

pg1-user=postgres



[global]  

start-fast=y

process-max=4

archive-timeout=300



repo1-type=s3

repo1-s3-uri-style=path

repo1-path=/pgbackrest/patroni-demo

repo1-retention-full=4

repo1-s3-bucket=<REPO_S3_BUCKET_NAME>

repo1-s3-endpoint=<REPO_S3_ENDPOINT>

repo1-s3-key=<REPO_S3_ACCESS_KEY>

repo1-s3-key-secret=<REPO_S3_KEY_SECRET>

repo1-s3-region=default  



[global:archive-push]  

compress-level=3

    Replace <REPO_S3_ENDPOINT> with your Vultr Object Storage hostname, <REPO_S3_BUCKET_NAME> with the bucket name, <REPO_S3_ACCESS_KEY> and <REPO_S3_KEY_SECRET> with your Vultr Object Storage Access Key and Secret Key respectively.

    Save and close the file.

  4. Edit the /etc/patroni.yml file and add create_replica_methods, pgbackrest, basebackup under the postgresql section as below:

    postgresql:

  create_replica_methods:

    - pgbackrest

    - basebackup

  pgbackrest:

    command: pgbackrest --stanza=demo-cluster restore --type=none

    keep_data: True

    no_params: True

  basebackup:

    checkpoint: 'fast'

  5. Reload the Patroni service.

    $ systemctl reload patroni

  6. On the Patroni primary server, run the following command to edit the Patroni configuration.

    $ patronictl -c /etc/patroni.yml edit-config

  7. Add archive_command, archive_mode and recovery_conf under the postgresql section as below.

    postgresql:

  parameters:

    hot_standby: 'on'

    max_replication_slots: 8

    max_wal_senders: 8

    wal_keep_segments: 20

    archive_command: pgbackrest --stanza=demo-cluster archive-push "%p"

    archive_mode: 'on'

  recovery_conf:

    recovery_target_timeline: latest

    restore_command: pgbackrest --stanza=demo-cluster archive-get %f "%p"

  use_pg_rewind: false

  use_slots: true

retry_timeout: 10

ttl: 30

  8. Reload the Patroni cluster.

    $ sudo -u postgres patronictl -c /etc/patroni.yml reload patroni_cluster

Perform a Manual Backup

  1. Get the list of members of the Patroni cluster.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  2. Connect to the Patroni Leader server over SSH.

    $ ssh user@patroni-server-ip

  3. Create pgBackRest Stanza.

    $ sudo -u postgres pgbackrest --stanza=demo-cluster stanza-create

  4. Back up with pgBackRest.

    $ sudo -u postgres pgbackrest --stanza=demo-cluster backup

  5. Check the backup information.

    $ sudo -u postgres pgbackrest info

Schedule Automatic Backups

Run crontab -e on both Patroni servers and enter the following configurations:

00 01 * * 0 sudo -u pgbackrest --type=full --stanza=demo-cluster backup &> /dev/null

00 01 * * 1-6 sudo -u pgbackrest --type=diff --stanza=demo-cluster backup &> /dev/null

0 */4 * * * sudo -u pgbackrest --type=incr --stanza=demo-cluster backup &> /dev/null

Below are some commands to verify the backup status:

  1. Check the backup information.

    $ sudo -u postgres pgbackrest info

  2. Check cron service logs to make sure there are pgBackRest jobs.

    $ journalctl -u cron.service -f

  3. View the list of PostgreSQL Write-Ahead Logging (WAL) files.

    $ ls /var/lib/postgresql/data/pg_wal/

  4. View the list of WAL archive status files.

    $ ls /var/lib/postgresql/data/pg_wal/archive_status/

Restore a Replica using pgBackRest

This section shows how to restore a replica from the backup. This section simulates a disaster situation by removing the data directory.

  1. Get the list of members of the Patroni cluster.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  2. Connect to the Patroni Replica server over SSH.

    $ ssh user@patroni-replica-ip

  3. Stop the patroni service.

    $ sudo systemctl stop patroni

  4. Remove its data directory.

    $ sudo rm -rf /var/lib/postgresql/data/*

  5. Start the Patroni service.

    $ sudo systemctl start patroni

  6. Check Patroni service logs to make sure there are no errors.

    $ sudo journalctl -u patroni.service -f

  7. Query the test table from previous steps.

    $ psql -U postgres -c "select * from mydata;"

Maintenance

This section describes how to perform maintenance on each of the members of the cluster.

Perform a Manual Switchover

  1. Check the status of the Patroni cluster. A zero value in Lag in MB shows that the replica has the latest data.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  2. Perform the manual switchover.

    $ sudo -u postgres patronictl -c /etc/patroni.yml switchover

Reinitialize a Patroni Node

  1. Check the status of the Patroni cluster. A big value in Lag in MB shows that the replica doesn't work normally. A common reason is the database can't write updates to disk.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  2. Reinitialize the affected node.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list patroni_cluster

Upgrade etcd Instances

This section describes how to upgrade the plan of each member of the etcd cluster.

  1. Check the etcd cluster status.

    $ etcdctl endpoint health

  2. Navigate to your Customer Portal and select one etcd instance.

  3. Click the Settings tab on the server information page.

  4. Select the Change Plan menu.

  5. Select the new plan, then click Upgrade.

  6. Check the etcd cluster status and wait until all nodes are healthy.

    $ etcdctl endpoint health

  7. Repeat the steps for the remaining nodes.

Upgrade Patroni Instances

This section describes how to upgrade the plan of each member of the Patroni cluster.

  1. Get the list of members of the Patroni cluster.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  2. (Optional) Perform a switchover if you want to upgrade the Patroni leader.

  3. Navigate to your Customer Portal and select one Patroni instance.

  4. Click the Settings tab on the server information page.

  5. Select Change Plan menu.

  6. Select the new plan, then click Upgrade.

  7. Check the Patroni cluster status and wait until all nodes are healthy.

    $ sudo -u postgres patronictl -c /etc/patroni.yml list

  8. Repeat the steps for the remaining nodes.

Conclusion

In this article cyou have deployed a high-availability PostgreSQL cluster with Patroni and etcd. This setup ensures the high availability of your database and protects you from any pontential data loss by streaming data between multiple servers and keeping backups on Vultr Object Storage.

Want to contribute?

You could earn up to $600 by adding new articles.

Submit your article Request an article