How to setup a PostgreSQL cluster with repmgr

Vagrant for local testing

Create two servers

Configure DNS / Hosts

  • 10.0.15.21 for server1 (master node)
  • 10.0.15.22 for server2 (standby node)
10.0.15.21 server1
10.0.15.21 server2

Postgresql & repmgr installation

sudo apt-get install -y postgresql
# Check your postgresql version and install repmgr
psql --version
wget -qO - | sudo apt-key add -
sudo add-apt-repository 'deb http://packages.2ndquadrant.com/repmgr3/apt/ xenial-2ndquadrant main'
sudo apt update
sudo apt-get install postgresql-9.5-repmgr
sudo -i -u postgres
createuser --replication --createdb --createrole --superuser repmgr
psql -c 'ALTER USER repmgr SET search_path TO repmgr_test, "$user", public;'
createdb repmgr --owner=repmgr
# Generate id_rsa ssh key
ssh-keygen
# Paste ssh key id_rsa.pub on other servers
vim .ssh/authorized_keys
# On server1
ssh server2
# On server2
ssh server1
wal_keep_segments = 5000
hot_standby = on
archive_mode = on
# Update your ips according to your cluster hosts
listen_addresses = '127.0.0.1,10.0.15.21,10.0.15.22'
max_wal_senders = 18
wal_level = hot_standby
hot_standby = on
shared_preload_libraries = 'repmgr_funcs'
host    repmgr repmgr 10.0.15.21/32 trust
host replication repmgr 10.0.15.21/32 trust
host repmgr repmgr 10.0.15.21/32 trust
host replication repmgr 10.0.15.22/32 trust
psql 'host=server1 dbname=repmgr user=repmgr'
psql 'host=server2 dbname=repmgr user=repmgr'

Cluster creation

cluster=cluster
node=1
node_name=server1
conninfo='host=server1 user=repmgr dbname=repmgr connect_timeout=2'
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file'
logfile='/var/log/postgresql/repmgr.log'
loglevel=NOTICE
reconnect_attempts=4
reconnect_interval=5
REPMGRD_ENABLED=yesREPMGRD_CONF="/etc/repmgr.conf"
sudo ln -s /usr/lib/postgresql/9.5/bin/pg_ctl /usr/bin/pg_ctl

Cluster registration

repmgr primary register
sudo -i -u repmgr
rm -rf /var/lib/postgresql/9.5/main
repmgr -h server1 -U repmgr -d repmgr standby clone
exit
sudo service postgresql start
sudo -i -u repmgr
repmgr standby register

Failover

sudo -i -u repmgr
rm -rf /var/lib/postgresql/9.5/main
repmgr -h server2 -U repmgr -d repmgr standby clone
exit
sudo service postgresql start
sudo -i -u repmgr
repmgr standby register

Conclusion

--

--

--

DevOps engineer & SRE

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

What are the common bugs in mobile apps

Common bugs in mobile apps

An Introduction to Flutter Clean Architecture

OpenCV configuration in Android Studio.

Kogito Tooling history and custom Envelopes

Why did we launch the Mobile release train?

A short tale about environment variables, dotenv, nodeJS and shell

Environments and links everywhere

Installing Joomla! has never been so easy and so fast

Facts on Micro-services Architectural Style

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Victor Boissiere

Victor Boissiere

DevOps engineer & SRE

More from Medium

Colima — Docker & K8S for local development

Avoiding CI/CD dependency conflicts by using Containers

Manage Multiple Cron with Helm Flow Control

Problem DataBase PostgreSQL Hight Availability Postgres HA Issues