KerkinGouda Android App v1.2.0

PlaatSoft has released a new version of the KerkinGouda Android App in the Google Play Store.

Many thanks leonard for building this new version during your trainee ship – You Rock!

The following changes were made:
– Added support for pdf content
– Added support for email content
– Added support for phone content

Click here to download the latest version.

Raspberry Pi Farm upgrade

Today i have upgraded my Raspberry Pi farm:
– Upgrade OS (bullseye) to latest version
– Created wildcard *.plaatsoft.nl HTTP certificate
– Added HaProxy software load balancer (disable Apache reverse proxy)
– Now load balancer is taking care of the HTTPS offloading
– Now load balancer route HTTP traffic to correct apache node
– Upgrade WordPress to v6.0.2 and wordpress plugins

Letsencrypt wildcard certificate

To enable a Letsencrypt wildcard certificate do the following steps

Create NEW wildcard certificate

1a. Create a DNS entry *.[DOMAIN_NAME].[DOMAIN_EXTENSION]
for example *.plaatsoft.nl

2a. Run following certbot command to create a wildcard certificate
sudo certbot certonly -d ‘*.[DOMAIN_NAME]’ –manual

3a. Certbot will ask you to add an extra DNS entry (TXT) with a unique token
Add this record in your DNS

4a. Check with dig tool if DNS record is available (This can take some time)
sudo dig _acme-challenge.[DOMAIN_NAME].[DOMAIN_EXTENSION]

5a. Press “yes”. Then wildcard certifate is created

6a. Add new certificate to Apache or HaProxy.

7a. Case closed

UPGRADE existing wildcard certifcate

1b. Remove DNS TXT record (Updating it does not work, is my experience)

2b. Check with dig tool if DNS record is really removed (This can take some time)
sudo dig _acme-challenge.[DOMAIN_NAME].[DOMAIN_EXTENSION]

3b. Go to Step 2a

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

Ansible on Raspberry Pi example

# Create master ansible node
sudo apt-get install ansible
sudo apt-get install sshpass -y

# Check version if ansible
ansible --version

# Create host file and add below lines
sudo /etc/ansible/hosts

[plaatsoft]
pi1
pi4
pi5
pi9

# update hostfile (Add all ansible target clients)
sudo vi /etc/hosts
pi1 192.168.2.101
pi4 192.168.2.104
pi5 192.168.2.105
pi9 192.168.2.109

# Generate private/public ssh key
ssh-keygen -t rsa
cat /home/pi/.ssh/id_rsa.pub

# Login each target client / created authorized_keys and insert public ssh key of ansible master node
vi .ssh/authorized_keys

# Ping all clients nodes
ansible all -m ping

# expected output
pi9 | SUCCESS => {
    "changed": false,
    "ping": "pong"
}
pi5 | SUCCESS => {
    "changed": false,
    "ping": "pong"
}
pi1 | SUCCESS => {
    "changed": false,
    "ping": "pong"
}
pi4 | SUCCESS => {
    "changed": false,
    "ping": "pong"
}

# Now create first playbook

mkdir /etc/ansible/playbook
mkdir /etc/ansible/playbook/files

vi /etc/ansible/playbook/hello1.yml

---
- name: first playbook example
  hosts: pi1 pi4 pi5 pi9
  tasks:
    - name: Create a file called '/tmp/hello1.txt' with the content 'hello world'.
      copy:
        content: hello world
        dest: /tmp/hello1.txt

# Execute first playbook
ansible-playbook hello1.yml

PLAY [This is a hello-world example] ************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************
ok: [pi5]
ok: [pi9]
ok: [pi1]
ok: [pi4]

TASK [Create a file called '/tmp/hello.txt' with the content 'hello world'.] ********************************************************************
ok: [pi9]
ok: [pi5]
ok: [pi1]
ok: [pi4]

PLAY RECAP **************************************************************************************************************************************
pi1                        : ok=2    changed=1    unreachable=0    failed=0
pi4                        : ok=2    changed=1    unreachable=0    failed=0
pi5                        : ok=2    changed=1    unreachable=0    failed=0
pi9                        : ok=2    changed=1    unreachable=0    failed=0

# Now create second playbook

vi /etc/ansible/playbook/files/hello.txt 

ENTER SOME TEXT

vi /etc/ansible/playbook/hello2.yml

---
- name: This is a second example
  hosts: pi1 pi4 pi5 pi9
  tasks:
    - name: Create a file called '/tmp/hello.txt' with content
      copy:
        src: ./files/hello2.txt
        dest: /tmp/hello2.txt

# Execute second playbook
ansible-playbook hello2.yml

PLAY [This is a second example] *****************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************
ok: [pi5]
ok: [pi9]
ok: [pi1]
ok: [pi4]

TASK [Create a file called '/tmp/hello.txt' with content] ***************************************************************************************
changed: [pi9]
ok: [pi5]
changed: [pi1]
changed: [pi4]

PLAY RECAP **************************************************************************************************************************************
pi1                        : ok=2    changed=1    unreachable=0    failed=0
pi4                        : ok=2    changed=1    unreachable=0    failed=0
pi5                        : ok=2    changed=0    unreachable=0    failed=0
pi9                        : ok=2    changed=1    unreachable=0    failed=0

Maven settings.xml with Nexus entry

<?xml version="1.0" encoding="UTF-8"?>
<settings xmlns="http://maven.apache.org/SETTINGS/1.1.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.1.0 http://maven.apache.org/xsd/settings-1.1.0.xsd">

<mirrors>
<mirror>
<id>nexus</id>
<mirrorOf>*</mirrorOf>
<url>https://nexus.plaatsoft.nl/repository/maven-public/</url>
</mirror>
</mirrors>

<profiles>
<profile>
<id>nexus</id>
<properties>
<altSnapshotDeploymentRepository>nexus::default::https://nexus.plaatsoft.nl/repository/maven-snapshots/</altSnapshotDeploymentRepository>
<altReleaseDeploymentRepository>nexus::default::https://nexus.plaatsoft.nl/repository/maven-releases/</altReleaseDeploymentRepository>
</properties>
</profile>
</profiles>

<activeProfiles>
<activeProfile>nexus</activeProfile>
</activeProfiles>

<servers>
<server>
<id>nexus</id>
<username>XXX</username>
<password>XXX</password>
</server>
</servers>

</settings>

BassieMusic Android App 2.3

PlaatSoft has released a new version of the BassieMusic Android App in the Google Play Store.

The following changes were made:
– Added fast alphabetic scrollbar
– Fixed music sort order to be case insensitive
– Added more animations and polished the UI experience more
– Added remember last song feature
– Cleaned up the source code a lot

Click here to download the latest version.