Setup a PostgreSQL cluster with repmgr and pgbouncer

December 22, 2018
PostgreSQL replication pgen repmgr pgbouncer

Setup a PostgreSQL cluster with repmgr and pgbouncer

Recently I had to setup a PostgreSQL cluster and one of the prerequisites was to use repmgr.

In this post, I will explain you the work I did and how to setup this kind of cluster.

For the curious, all the documentation for repmgr is available here.


The architecture

My setup is:

node0 to node2 are the PostgreSQL machines with one primary and two secondaries.

The pgbouncer machine will receive all the queries and send them to the actual primary.


Setup the machines

For this post, all my machines will be on Debian 9 with PostgreSQL 10.


Install the packages

We need to stop PostgreSQL because we have to do some configuration.


Setup SSH passwordless

Passwordless SSH connectivity between all machines is not mandatory but is required for these cases:

We will use it for pgbouncer too (as you can see later).

Follow these steps:

Now we have a SSH passwordless connectivity between all machines for the postgres user.


Configuring repmgr

In order to configure repmgr, you will need to:

In order to bootstrap your cluster, you will have to choose a server that will be the primary.

In my case, the network subnet is : 192.168.1.0/24 and the primary will be node0.

HINT: all the repmgr commands must be run as user postgres.


Changes to apply to all PostgreSQL machines

These changes must be applied on all the PostgreSQL machines (both primary and secondaries).

At first, we need to configure sudo.

Then we will modify the /etc/postgresql/10/main/postgresql.conf file.

Of course, you can adjust these values to your setup.


Configuring the primary PostgreSQL server

Now that you applied the changes above, follow these steps:

Now our primary server is up and running.


Configuring the secondaries PostgreSQL servers

Follow these steps to configure each of your secondaries servers:


Playing with repmgr

Now all our servers are up and running and we have a running PostgreSQL cluster.

We will see how to do basic operations with repmgr.

REMINDER: all the repmgr commands must be run as postgres user.


Cluster status

  $ repmgr -f /etc/repmgr.conf cluster show
  ID | Name                  | Role    | Status    | Upstream              | Location | Connection string                                                   
  ----+-----------------------+---------+-----------+-----------------------+----------+----------------------------------------------------------------------
  1  | "node0.raveland.priv" | primary | * running |                       | default  | host=node0.raveland.priv user=repmgr dbname=repmgr connect_timeout=2
  2  | "node1.raveland.priv" | standby |   running | "node0.raveland.priv" | default  | host=node1.raveland.priv user=repmgr dbname=repmgr connect_timeout=2
  3  | "node2.raveland.priv" | standby |   running | "node0.raveland.priv" | default  | host=node2.raveland.priv user=repmgr dbname=repmgr connect_timeout=2


My primary server has failed, what’s happen ?

Now we will see what to do when your secondary will stop for an unkown reason.

Now we are fine (the old primary is still stopped). But as you can see it’s a bit complicated..


My old primary is back.. what will happen ??

One thing to know about repmgr is that when an old primary comes back, i will stay primary !

Here are the steps to follow to switch your old primary as a new secondary.


I want to do a maintenance on my primary server. How can i do ?

As you can see, when you loose a primary, you will have some manual operations to do.

There is an easier way to switch primary server : switchover.

Remember, now the primary is node1 on our cluster. But we need to shutdown it for maintenance.

Enjoy !!!

Introducing repmgrd: replication manager daemon

Now that you have your PostgreSQL cluster with repmgr, you are happy. But maybe you would like to have a bit of automation.. That’s where repmgrd comes !

As well said in the documentation: repmgrd is a management and monitoring daemon which runs on each node in a replication cluster.

It can automate actions such as failover and updating standbys to follow the new primary, as well as providing monitoring information about the state of each standby.


Configure repmgrd

In this setup, you will just need to modify the file /etc/default/repmgrd to enable repmgrd at boot.


Playing with repmgrd


The primary fails

Let see what happens when the primary fails for an unknown reason (like the example above).

Everything is fine !


The old primary comes back

The problem we had with repmgr is the same with repmgrd. The old primary will stay primary !!

The solution is the same as before:


Pgbouncer joins the dance

Now that we have a PostgreSQL cluster with automation thanks to repmgrd, it could be usefull to add pgbouncer into the architecture.

Like this, we will have only one entrypoint for our applications.


Basic configuration for pgbouncer

At the beginning of this post, we already installed pgbouncer on the machine. Now we will do a basic configuration.


Create a database and a user in PostgreSQL

In order to test our setup, we need to create an user and a database in the PostgreSQL cluster (on the primary of course).

  $ psql
  psql (10.6 (Debian 10.6-1.pgdg90+1))
  Type "help" for help.
  postgres=# create role pea login ;
  CREATE ROLE
  postgres=# \password pea
  Enter new password:
  Enter it again:
  postgres=# create database pea owner pea ;
  CREATE DATABASE


Adapt first configuration for pgbouncer

Now that we have a user, a database and a primary server, we will finish the first configuration for pgbouncer.

Everything works !


Integrate pgbouncer with repmgr

The purpose is to have something automatic and each time the cluster’s topology is modified, pgbouncer must be notified.

As you may have understood, when the topology will change, we will generate a new file called pgbouncer.database.ini and send it to pgbouncer.

For this, we need to adjust the configuration of repmgr.

But let me explain you how we will make this happen:


Update repmgr configuration

The first thing to do is to write a small script that will :


Let’s try

At this point, you should have the script /usr/local/bin/promote.sh deployed on all your PostgreSQL machines and the file /etc/repmgr.conf updated with the latest change.

Victory !!!


Conclusion

The purpose of this (long) post was to show you how to setup a PostgreSQL cluster with repmgr and pgbouncer.

Of course, I can’t detail all the options of repgmr and you have to do your homeworks :)

But now, at least, you have the basis.

Enjoy 😉

Setup a streaming replication with PostgreSQL 10

March 11, 2018
postgresql pgen

OpenBSD / PostgreSQL / Authentication

November 29, 2017
openbsd postgresql pgen

Postgresql et la réplication logique

November 27, 2017
PostgreSQL docker replication pgfr