PlaatDomotica 1.1.0

PlaatSoft has released a new version of PlaatDomotica.

Version 1.1.0 (27-01-2023)
– First maintenance release
– Added custom login page
– Added background image
– Improve performance of energy and gas page
– Improve motion page (now resolution is seconds)
– Improve new version detection
– Improve solar page when there is no data
– Added bplaat native android look feature

More information click here

PlaatDomotica 1.0.0

PlaatSoft has released a new version of PlaatDomotica.

Version 1.0.0 (21-01-2023)
– First version for mass market
– Now all buttons have an icon
– Improve chart navigation + chart subtitle
– Added air quality sensor + air quality page
– Added bplaat styling changes
– Improve database cleanup job
– Added actor overview page
– Added sensor overview page
– Improve data model

More information click here

PlaatDomotica v0.9.0

PlaatSoft has released a new version of PlaatDomotica.

Version 0.9.0 (13-01-2023)
– Added cloud density page
– Alarm trigger event sent now email
– Alarm trigger event is now logged
– Bulb on/off event is now logged
– Resync event is now logged
– Login event is now logged
– Added alarm rule page
– Added alarm event page
– Added alarm bulb page
– Improve data model

More information click here

PlaatDomotica v0.8.0

PlaatSoft has released a new version of PlaatDomotica.

Version 0.8.0 (08-01-2023)
– Added toolkit popups to explain in app features
– Added resync feature with Hue base station configuration
– Added automation page
– Refactor java source code for better maintenance
– Improve solar page (performance)
– Improve gas page (performance)
– Improve electricity page (performance)
– Improve utility report
– Added energySensor
– Improve data model

More information click here

PlaatDomotica v0.7.0

PlaatSoft has released a new version of PlaatDomotica.

Version 0.7.0 (05-01-2023)
– Fix some security issues (URL fishing)
– Improve error page
– Reduce code duplication in html pages
– Added (override) properties file
– Improve page titles
– Home buttons are now visible depending on active sensors
– Added version sensor (Check for new version)
– Added current temperature report
– Added current solar report
– Improve error handling of sensors

More information click here

PlaatDomotica v0.6.0

PlaatSoft has released a new version of PlaatDomotica.

Version 0.6.0 (03-01-2023)
– Delete action needs now confirmation
– Improve role base access
– Now sensor can support multiple measureTypes
– Move settings to sensor and actor pages
– Improve data model
– Added actor pages
– Added email actor module
– Added database actor module
– Added portal actor module

More information click here

PlaatDomitica v0.4.0

PlaatSoft has released a new version of PlaatDomotica.

Version 0.4.0 (29-12-2022)
– Added Solar page
– Added Hosola Solar sensor module
– Added Email sensor (inform admin when IP address change)
– Added Dutch language support
– Added sensor frequency property
– Added sensor enable property
– Enabled persistence H2 database
– Added website icon
– Improve GUI for mobile use

More information click here

PlaatDomotica v0.3.0

PlaatSoft has released the new version of PlaatDomotica.

Version 0.3.0 (26-12-2022)
– First beta for public us
– Improve page navigation
– Added H2 Sensor (Cleanup database)
– Improve Home page
– Added Donate page
– Added Login page
– Added Logout page
– Added OAuth2 authenication logic
– Improve sensor data storage logic
– Added Setting Management pages

More information click here

PlaatDomotica v0.2.0

PlaatSoft has released the new version of PlaatDomotica.

Version 0.2.0 (21-12-2022)
– Added basic role base access feature
– Improve Home page
– Added Luminance page
– Added Pressure page
– Added Huminity page
– Added WindSpeed page
– Added Battery page
– Added Temperature page
– Added Sensor Management pages
– Added Error page
– Added Weather sensor module
– Added HUE motion sensor module

More information click here

PlaatDomotica v0.1.0

PlaatSoft has released an early draft version of PlaatDomotica.

Version 0.1.0 (18-12-2022)
– Initial version
– Added english language support
– Added Motion page
– Added Energy page
– Added User Management pages
– Added About page
– Added Release Notes page
– Added Home page
– Created basic CSS template

More information click here

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 *. –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