PostgreSQL cluster

Servers are combined into a Primary/Replica(s) cluster. One server acts as the main one and sends its data to the others. Read more about setting up and working with a PostgreSQL cluster in the official documentation.

Basic information

начало внимание

You need at least 2 servers to create the cluster.

конец внимание

In the example below, we are working with 3 nodes with the following hostnames and IP addresses:

• postgres-server-1, 192.168.1.1
• postgres-server-2, 192.168.1.2
• postgres-server-3, 192.168.1.3

Step 1: Install the etcd cluster

1. Install etcd to all nodes:

sudo apt-get install etcd -y

2. Move the default configuration file:

sudo mv /etc/default/etcd{,.original}

3. Create a new configuration file and open it for editing:

sudo nano /etc/default/etcd

4. Add a configuration example to the file for node 1:

ETCD_NAME="postgres-server1"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.1:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.1:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.1.1:12380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.1:12380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-postgres-cluster"
ETCD_INITIAL_CLUSTER="postgres-server1=http://192.168.1.1:12380,postgres-server2=http://192.168.1.2:12380,postgres-server3=http://192.168.1.3:12380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_ELECTION_TIMEOUT="5000"
ETCD_HEARTBEAT_INTERVAL="1000"
ETCD_INITIAL_ELECTION_TICK_ADVANCE="false"
ETCD_ENABLE_V2="true"

5. Add a configuration example to the file for node 2:

ETCD_NAME="postgres-server2"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.2:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.2:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.1.2:12380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.2:12380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-postgres-cluster"
ETCD_INITIAL_CLUSTER="postgres-server1=http://192.168.1.1:12380,postgres-server2=http://192.168.1.2:12380,postgres-server3=http://192.168.1.3:12380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_ELECTION_TIMEOUT="5000"
ETCD_HEARTBEAT_INTERVAL="1000"
ETCD_INITIAL_ELECTION_TICK_ADVANCE="false"
ETCD_ENABLE_V2="true"

6. Add a configuration example to the file for node 3:

ETCD_NAME="postgres-server3"
ETCD_LISTEN_CLIENT_URLS="http://192.168.1.3:2379,http://127.0.0.1:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.1.3:2379"
ETCD_LISTEN_PEER_URLS="http://192.168.1.3:12380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.1.3:12380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-postgres-cluster"
ETCD_INITIAL_CLUSTER="postgres-server1=http://192.168.1.1:12380,postgres-server2=http://192.168.1.2:12380,postgres-server3=http://192.168.1.3:12380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_ELECTION_TIMEOUT="5000"
ETCD_HEARTBEAT_INTERVAL="1000"
ETCD_INITIAL_ELECTION_TICK_ADVANCE="false"
ETCD_ENABLE_V2="true"

Description of the parameters used:

• ETCD_NAME. The name of the node in the cluster. It has to be unique within the cluster.
• ETCD_LISTEN_CLIENT_URLS. The connection point for the cluster’s clients.
• ETCD_ADVERTISE_CLIENT_URLS. The list of URL addresses that the other nodes of the cluster can use to find this node.
• ETCD_LISTEN_PEER_URLS. The connection point for other cluster nodes.
• ETCD_INITIAL_ADVERTISE_PEER_URLS. The initial list of URL addresses that the other nodes of the cluster can use to find this node.
• ETCD_INITIAL_CLUSTER_TOKEN. The cluster’s token. It has to be the same for all the cluster’s nodes.
• ETCD_INITIAL_CLUSTER. The list of nodes in the cluster at the time pf launch.
• ETCD_INITIAL_CLUSTER_STATE. The cluster’s state. This parameter has two possible values: “new” and “existing”.
• ETCD_DATA_DIR. The location of the catalog with the cluster’s data.
• ETCD_ELECTION_TIMEOUT. This timeout set in milliseconds is how long a follower node goes without hearing a heartbeat message from the leader of the cluster before attempting to become a leader itself.
• ETCD_HEARTBEAT_INTERVAL. This is the frequency in milliseconds with which the leader notifies followers that it is still the leader.

7. Restart etcd on all nodes:

sudo systemctl restart etcd

8. Check the state of the cluster:

sudo etcdctl cluster-health

Step 2: Install PostgreSQL

начало внимание

For correct operation of the system, PostgreSQL 10 is required.

конец внимание

1. To install PostgreSQL, add the postgresql official repository:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

2. Update the packages’ cache:

sudo apt update

3. Install PostgreSQL to all nodes:

sudo apt install postgresql-10 -y

Step 3: Configure PostgreSQL

Actions to be performed on the primary node

1. Create the elma365 role with SecretPassword as password:

sudo -u postgres psql -c \
"CREATE ROLE elma365 WITH login password 'SecretPassword';"

2. Create the elma365 database with elma365 as the owner:

sudo -u postgres psql -c \
"CREATE DATABASE elma365 WITH OWNER elma365;"

3. Add the necessary extensions for the elma365 database:

sudo -u postgres psql -d elma365 -c \
"CREATE EXTENSION \"uuid-ossp\"; CREATE EXTENSION pg_trgm;"

4. Create the replicator role with ReplicatorPassword as password. It will be used to work with replicas. This has to correspond with the settings of patroni from postgresql - authentication - replication and the list of allowed postgresql hosts from the pg_hba.conf file:

sudo -u postgres psql -c \
"CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'ReplicatorPassword';"

5. Set a password for the postgres user:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'PostgresPassword';"

6. The pg_hba.conf file has to be configured on all the database’s servers to allow access from different servers.

Add information about all the nodes to the pg_hba.conf file:

#Patroni
host all all 0.0.0.0/0 md5
host replication replicator 192.168.1.1/32 md5
host replication replicator 192.168.1.2/32 md5
host replication replicator 192.168.1.3/32 md5

7. Restart PostgreSQL on all nodes:

systemctl restart postgresql

Step 4: Install Patroni

1. Install Patroni and PIP to all the nodes:

sudo apt-get install python3-pip python3-dev libpq-dev -y
sudo apt-get install patroni -y

2. Set dependencies for Patroni on all the nodes:

pip3 install psycopg2-binary
pip3 install wheel
pip3 install python-etcd

Actions to be first performed on the primary node

1. Create a configuration file:

sudo nano /etc/patroni/config.yml

2. Place the initial configuration example in the /etc/patroni/config.yml file that you created. You need to replace the IP addresses to yours on each of the cluster’s nodes. See comments in this file:

Initial configuration example

3. Make the postgres user the owner of the settings catalog:

sudo chown postgres:postgres -R /etc/patroni
sudo chmod 700 /etc/patroni

4. Run the Patroni service:

sudo systemctl enable --now patroni.service

Add a new Patroni node

To add a new node to the Patroni cluster, perform the same actions as for the primary node.

1. Edit the /etc/patroni/config.yml file using the values of the current server.

2. If the service has been started before, delete the data catalog to make the replica work:

rm -rf /var/lib/postgresql/10/main

3. Run the Patroni service:

sudo systemctl enable --now patroni.service

4. Check the state of the cluster:

patronictl -c /etc/patroni/config.yml list

Step 5: HAProxy configuration (postgres)

Example of a configuration intended for load balancing using HAProxy:

listen postgres_master
    bind haproxy-server.your_domain:5000
    option tcplog
    option httpchk OPTIONS /master
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions
    server postgres-server1 postgres-server1.your_domain:5432 check port 8008
    server postgres-server2 postgres-server2.your_domain:5432 check port 8008
    server postgres-server3 postgres-server3.your_domain:5432 check port 8008
 
listen postgres_replicas
    bind haproxy-server.your_domain:5001
    option tcplog
    option httpchk OPTIONS /replica
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres-server1 postgres-server1.your_domain:5432 check port 8008
    server postgres-server2 postgres-server2.your_domain:5432 check port 8008
    server postgres-server3 postgres-server3.your_domain:5432 check port 8008
 
listen postgres_replicas_sync
    bind haproxy-server.your_domain:5002
    option tcplog
    option httpchk OPTIONS /sync
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres-server1 postgres-server1.your_domain:5432 check port 8008
    server postgres-server2 postgres-server2.your_domain:5432 check port 8008
    server postgres-server3 postgres-server3.your_domain:5432 check port 8008
 
listen postgres_replicas_async
    bind haproxy-server.your_domain:5003
    option tcplog
    option httpchk OPTIONS /async
    balance roundrobin
    http-check expect status 200
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres-server1 postgres-server1.your_domain:5432 check port 8008
    server postgres-server2 postgres-server2.your_domain:5432 check port 8008
    server postgres-server3 postgres-server3.your_domain:5432 check port 8008

Step 6: Connect to ELMA365

1. To set up a connection with the Postgres cluster, run:

psqlUrl: postgresql://elma365:SecretPassword@haproxy-server.your_domain:5000/elma365?sslmode=disable

2. To set up a read-only connection with the Postgres cluster, run:

roPsqlUrl: postgresql://elma365:SecretPassword@haproxy-server.your_domain:5001/elma365?sslmode=disable

Was this helpful?

Thanks for your feedback!

Please specify why:

Recommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

How we can improve it?

YesNo

Please specify whyRecommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

Found a typo? Highlight the text, press ctrl + enter and notify us