PlaatSoft

Step 1: Update debian
sudo apt-get update
sudo apt-get upgrade

Step 2: Install docker
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh

Step 3: create docker group / user
sudo usermod -aG docker pi

Step 4: Final check
sudo docker version

To enable a Letsencrypt wildcard certificate do the following steps

Create NEW wildcard certificate

1. Stop Apache or HaProxy
2. Run following certbot command to create a wildcard certificate
sudo certbot certonly -d '*.plaatsoft.nl' --manual
3. Certbot will ask you to add an extra DNS entry (TXT) with a unique token
Add this record in your DNS
4. Check with dig tool if DNS record is available (This can take some time)
sudo dig _acme-challenge.plaatsoft.nl TXT
5. Press "enter". Then wildcard certifate is created
6. Add new certificate to Apache or HaProxy.
7. Remove DNS TXT record (Updating it does not work, is my experience)
8. Done

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
	

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
	

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
	

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