I often get asked if it’s possible to build a resilient system with PostgreSQL.

Considering that resilience should feature cluster high-availability, fault tolerance, and self-healing, it’s not an easy answer. But there is a lot to be said about this.

As of today, we can’t achieve that level of resilience with the same ease as MongoDB built-in features. But let’s see what we can in fact do with the help of repmgr and some other tooling.

At the end of this exercise, we will have achieved some things that come in handy, such as:

• a few Ansible roles that can be reused for production
• a Vagrantfile for single-command cluster deployment
• a development environment that’s more realistic; being close to production state is good to foresee “production-exclusive issues”

Objectives

• build a local development environment PostgreSQL cluster with fault tolerance capabilities;
• develop configuration management code to reuse in production.

Pre-requisites

Install Vagrant, VirtualBox and Ansible

Java

1

sudo apt install vagrant

2

sudo apt install virtualbox && sudo apt install virtualbox-dkms

3

sudo apt install ansible

Note: An alternative to installing Ansible on your host machine would be using the ansible-local Vagrant provider, which needs Ansible installed on the generated virtual machine instead.

Configuration

1. Write a Vagrantfile

You can use vagrant init to generate the file or simply create it and insert our first blocks.

Ruby

1

Vagrant.configure("2") do |config|

2

  (1..3).each do |n|

3

    config.vm.define "node#{n}" do |define|

4

      define.ssh.insert_key = false

5

      define.vm.box = "ubuntu/bionic64"

6

      define.vm.hostname = "node#{n}"

7

      define.vm.network :private_network, ip: "172.16.1.1#{n}"

8

9

      define.vm.provider :virtualbox do |v|

10

        v.cpus = 2

11

        v.memory = 1024

12

        v.name = "node#{n}"

13

      end

14

    end

15

  end

16

end

Let’s go block by block:

• the 1st block is where we set up the Vagrant version;
• on the 2nd block, we iterate the following code so we reuse it to generate 3 equal VMs;
• OS, hostname and network settings are set in the 3rd block;
• the 4th block contains VirtualBox specific settings.

You can create the servers with:

Java

1

## create all 3 VMs

2

vagrant up

3

## or create only a specific VM

4

vagrant up node1

2. Add a provisioner

Just by doing the first step alone, we can already launch 3 working virtual machines. A little exciting, but the best is yet to come.

Launching virtual machines is a nice feature of Vagrant, but we want these servers to have PostgreSQL and repmgr configured, so we will use configuration management software to help us. This is the moment Ansible walks in to amaze us.

Vagrant supports several providers, two of them being Ansible and Ansible Local. The difference between them is where Ansible runs, or in other words, where it must be installed. By Vagrant terms, the Ansible provider runs on a host machine (your computer) and the Ansible Local provider runs on guest machines (virtual machines). As we already installed Ansible in the prerequisites section, we’ll go with the first option.

Let’s add a block for this provisioner in our Vagrantfile.

Ruby

1

Vagrant.configure("2") do |config|

2

  (1..3).each do |n|

3

    config.vm.define "node#{n}" do |define|

4

      define.ssh.insert_key = false

5

      define.vm.box = "ubuntu/bionic64"

6

      define.vm.hostname = "node#{n}"

7

      define.vm.network :private_network, ip: "172.16.1.1#{n}"

8

9

      define.vm.provider :virtualbox do |v|

10

        v.cpus = 2

11

        v.memory = 1024

12

        v.name = "node#{n}"

13

      end

14

15

      if n == 3

16

        define.vm.provision :ansible do |ansible|

17

          ansible.limit = "all"

18

          ansible.playbook = "provisioning/playbook.yaml"

19

20

          ansible.host_vars = {

21

            "node1" => {:connection_host => "172.16.1.11",

22

                        :node_id => 1,

23

                        :role => "primary" },

24

25

            "node2" => {:connection_host => "172.16.1.12",

26

                        :node_id => 2,

27

                        :role => "standby" },

28

29

            "node3" => {:connection_host => "172.16.1.13",

30

                        :node_id => 3,

31

                        :role => "witness" }

32

          }

33

        end

34

      end

35

36

    end

37

  end

38

end

Ansible allows us to configure several servers simultaneously. To take advantage of this feature on Vagrant, we add ansible.limit = "all" and must wait until all 3 VMs are up. Vagrant knows they are all created because of the condition if n == 3, which makes Ansible only run after Vagrant iterated 3 times.

ansible.playbook is the configuration entry point and ansible.host_vars contains the Ansible host variables to be used on the tasks and templates we are about to create.

#database #devops #automation #postgresql #ansible #vagrant #postgres #fault tolerance #cluster management #database administrator