Setting up the PostgreSQL High Availability environment

Important

Configuring high availability for the database is the responsibility of your database team. The following information illustrates a possible setup using pg_auto_failover to create a high availability database cluster.

While all possible steps were taken to verify its accuracy, Men&Mice assumes no responsibility for the setup herein.

Install pg_auto-failover

Note

The setup presented here will establish resilience against losing any one of the configured three nodes. Refer to the pg_auto_failover documentation for more details about different configurations.

../../../_images/postgres_ha.png
Variables

Name

Description

Example value

[port]

The port number that will be used for communication between database nodes

5000

[monitor]

The monitor node’s machine hostname

postgresql-node-0

[node-1]

The primary node’s machine hostname

postgresql-node-1

[node-2]

The secondary node’s machine hostname

postgresql-node-2

[ip-address-monitor-machine]

The monitor node’s machine IP address

172.17.0.2

[ip-address-node-1]

The machine’s IP address of node-1

172.17.0.3

[ip-address-node-2]

The machine’s IP address of node-2

172.17.0.4

[monitor_node_password]

The monitor node’s password to its database. This password cannot contain the *@* character.

test123

[replication-password]

The password used for replication between nodes

vg8@urenHfhk

[postgres-password]

The password used to access the mmsuite database

postgres

[ip-address-of-central-primary]

The IP address of the primary machine running Central

172.17.0.5

[ip-address-of-central-secondary]

The IP address of the machine running the secondary Central

172.17.0.6

[pre-existing-database-port]

The port of the pre-existing database (if any)

5432

Make sure that the hostnames are resolvable between all three machines along with the machine running Central (and the second instance if Central is running in HA mode). If that is not possible, using the ip-addresses instead of hostnames is allowed.

Install requirements

Install sudo and which:

yum install sudo
yum install which

Enable the package repository that distributes pg_auto_failover:

curl https://install.citusdata.com/community/rpm.sh | sudo bash

Install pg_auto_failover for PostgreSQL 12:

sudo yum install -y pg-auto-failover10_12

If you will be using hostnames, edit /etc/hosts:

echo "[ip-address-monitor-machine] [monitor]" >> /etc/hosts
echo "[ip-address-node-1] [node-1]" >> /etc/hosts
echo "[ip-address-node-2] [node-2]" >> /etc/hosts

Note

You can use the commands pg_autoctl stop and pg_autoctl drop node --destroy to start with a clean slate and get rid of everything that might have been set up previously.

Machine: monitor

Switch to user postgres and export pgsql path:

sudo su - postgres

export PATH="$PATH:/usr/pgsql-12/bin"

Set up a monitor node:

pg_autoctl create monitor --pgdata ./[monitor] --pgport [port] --nodename [monitor] --auth scram-sha-256

Next, the pg_hba.conf file needs to be edited to allow connection in from the two nodes:

echo "host pg_auto_failover autoctl_node [ip-address-node-1]/32 scram-sha-256" >> ./[monitor]/pg_hba.conf

echo "host pg_auto_failover autoctl_node [ip-address-node-2]/32 scram-sha-256" >> ./[monitor]/pg_hba.conf

Edit the postgresql.conf file to allow scram-sha-256 authentication:

vi ./[monitor]/postgresql.conf
# uncomment the line and set
# password_encryption = 'scram-sha-256'
# uncomment the line and set
# listen_addresses = '*'
# save the file and restart

pg_ctl restart -D ./[monitor]

Still running as user postgres, set the database user password in the monitor database:

psql -p [port] -d pg_auto_failover
ALTER USER autoctl_node PASSWORD '[monitor_node_password]';
\q

Machine: node-1

Switch to user postgres and export pgsql path:

sudo su - postgres

export PATH="$PATH:/usr/pgsql-12/bin"

Set up a primary node:

pg_autoctl create postgres --pgdata ./[node-1] --pgport [port] --pgctl `which pg_ctl` --nodename [node-1] --monitor postgres://autoctl_node:[monitor_node_password]@[monitor]:[port]/pg_auto_failover --auth scram-sha-256

Set up a replication password

pg_autoctl config set replication.password [replication-password] --pgdata ./[node-1]

Edit the postgresql.conf file to allow scram-sha-256 authentication:

vi ./[node-1]/postgresql.conf
# uncomment the line and set
# password_encryption = 'scram-sha-256'
# uncomment the line and set
# listen_addresses = '*'
# save the file and restart

pg_ctl restart -D ./[node-1]

Still running as user postgres, set the database user password in the database:

psql -p [port]
ALTER USER pgautofailover_replicator PASSWORD [replication-password];
ALTER USER postgres PASSWORD [postgres-password];
\q

Run the primary node in the background:

pg_autoctl run --pgdata ./[node-1]/ &

Machine: node-2

sudo su - postgres
export PATH="$PATH:/usr/pgsql-12/bin"
pg_autoctl create postgres --pgdata ./[node-2] --pgport [port] --pgctl `which pg_ctl` --nodename [node-2] --monitor postgres://autoctl_node:[monitor_node_password]@[monitor]:[port]/pg_auto_failover --auth scram-sha-256
pg_autoctl config set replication.password [replication-password] --pgdata ./[node-2]
pg_autoctl run --pgdata ./[node-2]/ &

Machine: monitor

Show state to verify the setup:

pg_autoctl show state --pgdata ./[monitor]

Name                       |   Port    | Group |  Node |     Current State |    Assigned State
---------------------------+-----------+-------+-------+-------------------+------------------
[node-1]                   |   [port] |     0 |     1 |            primary |           primary
[node-2]                   |   [port] |     0 |     1 |          secondary |         secondary

Set up the mmsuite database and edit config files

Machine: node-1

Enter the postgresql database as user postgres:

psql -p [port]
> CREATE DATABASE mmsuite ENCODING = 'LATIN1' LC_CTYPE = 'POSIX' LC_COLLATE='POSIX' TEMPLATE template0;
> GRANT ALL PRIVILEGES ON DATABASE mmsuite TO postgres;

Edit the pg_hba.conf to allow access to the database from the outside:

echo "host mmsuite postgres [ip-address-of-central-primary]/32 scram-sha-256" >> ./[node-1]/pg_hba.conf
echo "host mmsuite postgres [ip-address-of-central-secondary]/32 scram-sha-256" >> ./[node-1]/pg_hba.conf
psql -p [port] -c 'SELECT pg_reload_conf();'

Machine: node-2

Edit the pg_hba.conf to allow access to the database from the outside:

echo "host mmsuite postgres [ip-address-of-central-primary]/32 scram-sha-256" >> ./[node-2]/pg_hba.conf
echo "host mmsuite postgres [ip-address-of-central-secondary]/32 scram-sha-256" >> ./[node-2]/pg_hba.conf
psql -p [port] -c 'SELECT pg_reload_conf();'

Further information