keepalived service

How the setup Virtual IP addres over two rapberry Pi nodes
pi6 192.168.2.106
pi7 192.168.2.107

pi6
sudo apt-get install keepalived

sudo vi /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 255
advert_int 5
virtual_ipaddress {
192.168.2.200/32
}
notify /usr/local/bin/keepalivednotify.sh
}

vi /usr/local/bin/keepalivednotify.sh

#!/bin/bash
type=$1
name=$2
state=$3

case $state in
"MASTER") echo $state > /etc/keepalived/state
exit 0
;;
"BACKUP") echo $state > /etc/keepalived/state
exit 0
;;
"FAULT") echo $state > /etc/keepalived/state
exit 0
;;
*) echo $state > /etc/keepalived/state
exit 1
;;
esac

sudo chmod a+x /usr/local/bin/keepalivednotify.sh
sudo systemctl start keepalived
sudo journalctl -u keepalived -f

pi7
sudo apt-get install keepalived

sudo vi /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 254
advert_int 5
virtual_ipaddress {
192.168.2.200/32
}
notify /usr/local/bin/keepalivednotify.sh
}

vi /usr/local/bin/keepalivednotify.sh

#!/bin/bash
type=$1
name=$2
state=$3

case $state in
"MASTER") echo $state > /etc/keepalived/state
exit 0
;;
"BACKUP") echo $state > /etc/keepalived/state
exit 0
;;
"FAULT") echo $state > /etc/keepalived/state
exit 0
;;
*) echo $state > /etc/keepalived/state
exit 1
;;
esac

sudo chmod a+x /usr/local/bin/keepalivednotify.sh
sudo systemctl start keepalived
sudo journalctl -u keepalived -f

Migrate MySQL db to PostgreSQL db

mysql
CREATE USER 'family'@'192.168.2.106' IDENTIFIED BY 'family';
GRANT ALL PRIVILEGES ON family . * TO 'family'@'192.168.2.106';
flush privileges;

psql
CREATE DATABASE family;
CREATE USER family WITH SUPERUSER ENCRYPTED PASSWORD 'family';
GRANT ALL PRIVILEGES ON DATABASE family TO family;

sudo apt-get install pgloader
pgloader mysql://family:family@pi4/family postgresql://family:family@pi6/family

Postgres automatic failover

sudo vi /usr/lib/postgresql/13/bin/pgfailover

#!/bin/bash

# Postgres HA (Master / Replication) monitoring script
# This script must only be installed on postgres slave (hot standby) node

POSTGRES_MASTER_IP=pi6
POSTGRES_SLAVE_IP=127.0.0.1
POSTGRES_CHECK_PORT=23267
POSTGRES_FAILOVER='/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data promote'

status_master=$(curl --write-out %{http_code} --silent --output /dev/null $POSTGRES_MASTER_IP:$POSTGRES_CHECK_PORT)
status_slave=$(curl --write-out %{http_code} --silent --output /dev/null $POSTGRES_SLAVE_IP:$POSTGRES_CHECK_PORT)

if [[ "$status_master" -eq 200 ]]
then
if [[ "$status_slave" -eq 206 ]]
then
echo "Master up - Slave Standbye - Do nothing"
else
echo "Master up - Slave down - Do nothing"
fi
else
if [[ "$status_slave" -eq 200 ]]
then
echo "Slave is Master - Do nothing"
elif [[ "$status_slave" -eq 206 ]]
then
echo "Slave becomes Master - Do action"
eval $POSTGRES_FAILOVER
exit 1;
else
echo "Master and Slave down - Do nothing"
fi
fi

exit 0;

sudo chown postgres:postgres /usr/lib/postgresql/13/bin/pgfailover
sudo chmod a+x /usr/lib/postgresql/13/bin/pgfailover

sudo crontab -u postgres -e
* * * * * /usr/lib/postgresql/13/bin/pgfailover

Postgres manually failover

How to failover from Master to Replication node and visa versa
Pi6 192.168.2.106 (MASTER)
Pi7 192.168.2.107 (REPLICATION)

Make Replication node master
# Goto pi7
su postgres
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data promote

Restore old master
# Goto to pi6
sudo cp /data02/data/*.conf ../.
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data stop
rm -rf /data02/data/*
/usr/lib/postgresql/13/bin/pg_basebackup --pgdata=/data02/data --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=pi7 --port=5432 --username=plaatsoft
rm /data02/data/standby.signal
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start
psql -c "select * from pg_create_physical_replication_slot('standby1_slot');"

# Goto to pi7
sudo cp /data02/data/*.conf ../.
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data stop
rm -rf /data02/data/*
/usr/lib/postgresql/13/bin/pg_basebackup --pgdata=/data02/data --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=pi6 --port=5432 --username=plaatsoft
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start

Check replication

CHECK MASTER NODE
postgres=# \x
postgres=# SELECT * FROM pg_stat_replication;

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)

CHECK REPLICATION NODE
postgres=# \x
postgres=# SELECT * FROM pg_stat_wal_receiver;

postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

HaProxy for Postgres HA

Execute on each Postgresql node the following actions

# Create check script
sudo vi /usr/lib/postgresql/13/bin/pgsqlchk

#!/bin/bash
# This script checks if a postgres server is healthy running on localhost. It will return:
# "HTTP/1.x 200 OK\r" (if postgres is running smoothly)
# - OR -
# "HTTP/1.x 500 Internal Server Error\r" (else)
# The purpose of this script is make haproxy capable of monitoring postgres properly
# It is recommended that a low-privileged postgres user is created to be used by this script.
# For eg. create user healthchkusr login password 'hc321';

PGBIN=/usr/pgsql-10/bin
PGSQL_HOST="localhost"
PGSQL_PORT="5432"
PGSQL_DATABASE="postgres"
PGSQL_USERNAME="postgres"
export PGPASSWORD="passwd"
TMP_FILE="/tmp/pgsqlchk.out"
ERR_FILE="/tmp/pgsqlchk.err"

# We perform a simple query that should return a few results

VALUE=`/usr/lib/postgresql/13/bin/psql -t -h localhost -U postgres -p 5432 -c "select pg_is_in_recovery()" 2> /dev/null`
# Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything.

if [ $VALUE == "t" ]
then
/bin/echo -e "HTTP/1.1 206 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo "Standby"
/bin/echo -e "\r\n"
elif [ $VALUE == "f" ]
then
/bin/echo -e "HTTP/1.1 200 OK\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo "Primary"
/bin/echo -e "\r\n"
else
/bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
/bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
/bin/echo -e "\r\n"
/bin/echo "DB Down"
/bin/echo -e "\r\n"
fi

# Make script executable
sudo chmod a+x /usr/lib/postgresql/13/bin/pgsqlchk

# install xinetd and telnet
sudo apt-get install xinetd telnet -y

# Create xinitd file
sudo vi /etc/xinetd.d/pgsqlchk

service pgsqlchk
{
flags = REUSE
socket_type = stream
port = 23267
wait = no
user = nobody
server = /usr/lib/postgresql/13/bin/pgsqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0
per_source = UNLIMITED
}

sudo bash -c 'echo "pgsqlchk 23267/tcp # pgsqlchk" >> /etc/services'
sudo systemctl restart xinetd

# Check if service is working
telnet 127.0.0.1 23267

Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
HTTP/1.1 206 OK
Content-Type: Content-Type: text/plain
Standby
Connection closed by foreign host.

Execute on HaProxy following actions

# Install HaProxy
sudo apt-get install haproxy -y 

sudo vi /etc/hosts
Add pi6 192.168.2.106
Add pi7 192.168.2.107

sudo vi /etc/haproxy/haproxy.cfg  
  
global  
    maxconn 100  
   
defaults  
    log global  
    mode tcp  
    retries 2  
    timeout client 30m  
    timeout connect 4s  
    timeout server 30m  
    timeout check 5s  
   
listen stats  
    mode http  
    bind *:10000  
    stats enable   
    stats refresh 10s
    stats uri /
   
listen Postgres
    bind *:5432 
    option httpchk  
    http-check expect status 200  
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions  
    server pi6 pi6:5432 maxconn 100 check port 23267  
    server pi7 pi7:5432 maxconn 100 check port 23267  
   
systemctl restart haproxy  

Check HaProxy

# Install postgres client
sudo apt-get install postgresql-client

# Start psql client
psql -h localhost -U postgres  
  
postgres=# select pg_is_in_recovery();  
pg_is_in_recovery  
-------------------  
f  
(1 row)

Check HaProxy portal http://192.168.2.107:5000

PostgreSQL HA setup

How to setup High Available PostgreSQL on two nodes.
Pi6 192.168.2.106 (MASTER)
Pi7 192.168.2.107 (REPLICATION)

Run below commands on both Pis:

sudo vi /etc/hosts
Add pi6 192.168.2.106
Add pi7 192.168.2.107

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql

# Stop postgresql
sudo systemctl stop postgresql

# Create directories
sudo mkdir /data02
sudo mkdir /data02/data
sudo chmod a+wrx /data02
sudo chown postgres:postgres /data02/data
sudo chmod 0700 /data02/data

# Remove old databases
rm -rf /var/lib/postgresql/13/main
ln -s /data02/data /var/lib/postgresql/13/main
rm -rf /etc/postgresql/13/main
ln -s /data02/data /etc/postgresql/13/main

Run below commands only in Pi6 (MASTER):

# Create new database
/usr/lib/postgresql/13/bin/pg_ctl initdb -D /data02/data

# Update PostgresSQL configuration
vi /etc/postgresql/13/main/postgresql.conf
Change #listen_addresses = 'localhost' to listen_addresses = '*'
Change #wal_level = replica to wal_level = replica
Change #max_wal_senders = 10 to max_wal_senders = 10
Change #wal_keep_size = 0 to wal_keep_size = 0

vi /etc/postgresql/13/main/pg_hba.conf
Add next line
host all all 192.168.2.0/24 trust
host replication plaatsoft 192.168.2.0/24 trust

# Start database
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start
of
systemctl start postgresql

# Create replication user
psql -c "CREATE USER plaatsoft WITH PASSWORD 'plaatsoft' REPLICATION;"

5. Create replication slot on Primary Server
psql -c "select * from pg_create_physical_replication_slot('standby1_slot');"

Run below commands only in Pi7 (REPLICATION):

# Create replication database based on master
/usr/lib/postgresql/13/bin/pg_basebackup --pgdata=/data02/data --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=pi6 --port=5432 --username=plaatsoft

# Update PostgresSQL configuration
vi /etc/postgresql/13/main/postgresql.conf

# Update PostgresSQL configuration
vi /etc/postgresql/13/main/postgresql.conf
Change primary_conninfo to 'host=pi6 port=5432 user=plaatsoft password=plaatsoft'
Change primary_slot_name to 'standby1_slot'

# Start database
/usr/lib/postgresql/13/bin/pg_ctl -D /data02/data start
of
systemctl start postgresql

Final Check Pi6:

postgres 5211 1 0 13:04 ? 00:00:00 /usr/lib/postgresql/13/bin/postgres -D /data02/data
postgres 5215 5211 0 13:04 ? 00:00:00 postgres: checkpointer
postgres 5216 5211 0 13:04 ? 00:00:00 postgres: background writer
postgres 5218 5211 0 13:04 ? 00:00:00 postgres: walwriter
postgres 5219 5211 0 13:04 ? 00:00:00 postgres: autovacuum launcher
postgres 5220 5211 0 13:04 ? 00:00:00 postgres: stats collector
postgres 5221 5211 0 13:04 ? 00:00:00 postgres: logical replication launcher
postgres 5970 5211 0 13:13 ? 00:00:00 postgres: walsender plaatsoft 192.168.2.107(46242) streaming 0/12013F58

Final Check Pi7:

postgres 23167 1 0 13:13 ? 00:00:00 /usr/lib/postgresql/13/bin/postgres -D /data02/data
postgres 23168 23167 0 13:13 ? 00:00:00 postgres: startup recovering 000000010000000000000012
postgres 23169 23167 0 13:13 ? 00:00:00 postgres: checkpointer
postgres 23170 23167 0 13:13 ? 00:00:00 postgres: background writer
postgres 23171 23167 0 13:13 ? 00:00:00 postgres: stats collector
postgres 23173 23167 0 13:13 ? 00:00:00 postgres: walreceiver streaming 0/12013F58

Raspberry Pi NFS

pi6 (NFS SERVER)
sudo apt-get install nfs-kernel-server

# Create directory structure
sudo mkdir /data03
sudo chmod a+wrx /data03

# Create NFS server file
vi /etc/exports
# Add below line at the end of the file
/data03 pi7(rw,sync,no_subtree_check)

# Reinit NFS server
sudo exportfs -ra

pi7 (NFS CLIENT)
sudo mkdir /data03
sudo chmod a+wrx /data03

# Temporary mount
sudo mount -t nfs -o proto=tcp,port=2049 pi6:/data03 /data03

# Final mount
sudo vi /etc/fstab
pi6:/data03 /data03 nfs auto 0 0