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.

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.

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.

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 : and the primary will be node0.

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

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.

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

Now our primary server is up and running.

Follow these steps to configure each of your secondaries servers:

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.

$ 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

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..

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.

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.

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

Everything is fine !

The solution is the same as before:

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.

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

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 ;
postgres=# \password pea
Enter new password:
Enter it again:
postgres=# create database pea owner pea ;

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

Everything works !

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:

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

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

Victory !!!

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