Category: Notes

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

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>

GeoServer + PostgreSQL + OpenStreetMap

To load an openstreetmap datasource in PostgreSQL/PostGIS use the following command.

# osm2pgsql –slim –username XXX –password –database streetmap ./zuid-holland-latest.osm.pbf

osm2pgsql version 0.96.0 (64 bit id space)

Password:
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, cache=800
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: planet_osm_point
Setting up table: planet_osm_line
Setting up table: planet_osm_polygon
Setting up table: planet_osm_roads

Reading in file: ./zuid-holland-latest.osm.pbf
Using PBF parser.
Processing: Node(15439k 270.9k/s) Way(2349k 71.20k/s) Relation(17020 1547.27/s) parse time: 101s
Node stats: total(15439302), max(6336341363) in 57s
Way stats: total(2349653), max(676644693) in 33s
Relation stats: total(17679), max(9386429) in 11s
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_nodes
Sorting data and creating indexes for planet_osm_roads
Sorting data and creating indexes for planet_osm_polygon
Stopping table: planet_osm_ways
Sorting data and creating indexes for planet_osm_line
Stopping table: planet_osm_rels
Stopped table: planet_osm_nodes in 0s
Building index on table: planet_osm_ways
Building index on table: planet_osm_rels
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Stopped table: planet_osm_rels in 2s
Creating osm_id index on planet_osm_roads
Creating indexes on planet_osm_roads finished
All indexes on planet_osm_roads created in 3s
Completed planet_osm_roads
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating osm_id index on planet_osm_line
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 22s
Completed planet_osm_line
Creating osm_id index on planet_osm_point
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 65s
Completed planet_osm_point
Stopped table: planet_osm_ways in 98s
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
Creating osm_id index on planet_osm_polygon
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 252s
Completed planet_osm_polygon

Osm2pgsql took 355s overall
node cache: stored: 15439302(100.00%), storage efficiency: 50.00% (dense blocks: 0, sparse nodes: 15439302), hit rate: 100.00%

Create GIS database in PostgreSQL

1. Install PostgreSQL database (for example 9.6)

2. Install postgis module (for example 2.5.1)

3. Start pgAdmin 4

4. Create database

5. Enable GIS features in database with following command:
CREATE EXTENSION postgis;

6. Now shapefile can be loaded with PostGIS 2.0 shapefile tool

7. Set GEO input format (for example Rijksdriehoek)
SELECT UpdateGeometrySRID(‘wegvakken’,’geom’, 28992);

8. Create WGS84 geog column and fill it.
ALTER TABLE wegvakken ADD COLUMN geog geography(geometry);
UPDATE wegvakken SET geog = ST_Transform(geom, 4326);
CREATE INDEX wegvakken_geog_idx ON wegvakken USING GIST (geog);

9. Now pgAdmin4 can show GEO layer with OpenStreetMap layer as base.

10. Set center point of RijksDriehoek (Onze Lieve Vrouwetoren (‘Lange Jan’) in Amersfoort.)
SELECT * FROM wegvakken ORDER BY geom <-> ‘SRID=28992;POINT(155000 463000)’::geometry LIMIT 1;

11. select ST_X(geom), ST_Y(geom) from shapefile

Windows 10 upgrade takes hours

This afternoon my Quad Core 3GHZ Desktop finally received the Windows 10 creator patch. It took my desktop more than 4 hours to install it. Why is a Windows 10 patch always so slow? If i upgrade a Raspberry Pi Quad Core 1.2GHZ Linux kernel it’s only take 10 minutes. Strange that low end linux hardware upgrades are must faster than high end Windows hardware. I think that the Microsoft staff must go on training to learn how to patch Operating Systems fast and without reboots 🙂

Raspberry Pi Noobs cleanup

If you want to have a nice and clean Raspberry Pi Noobs installation. Execute te followings commands to remove unless tools:

sudo apt-get purge wolfram-engine (Reduce image size with 683MB)
sudo apt-get purge sonic-pi (Reduce image size with 151MB)
sudo apt-get purge scratch (Reduce image size with 93MB)
sudo apt-get purge scratch2 (Reduce image size with 147MB)
sudo apt-get purge scratch3 (Reduce image size with 147MB)
sudo apt-get autoclean

This initial Noobs image size will now be arround 4GB 🙂

Google Charts zoom in dataset example

google-chartsI took me a while to understand how Google Charts can be extended with a zoom/drill in data set feature. You have to add then google.visualization.events.addListener method which fetch the selected datapoint of a graph. With some extra javascript code you can reload the chart with a more detailed dataset. See example below!

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["bar"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {

      var options = {
          bars: "vertical",
          bar: {groupWidth: "90%"},
          legend: { position: "none"},
          vAxis: {format: "decimal"},
          isStacked:true,colors: ["#0066cc", "#808080"],vAxis: { format:"decimal", viewWindow: { min: 0, max: ".round($max_forecast+100)." } }, series: {
            3: {
                targetAxisIndex: 1
            }
          }
        };

        var data = google.visualization.arrayToDataTable([['','Low (kWh)','Normal (kWh)','Solar (kWh)','Forecast (kWh)'],['2006',0,0,0,0],['2007',0,0,0,0],['2008',0,0,0,0],['2009',0,0,0,0],['2010',0,0,0,0],['2011',0,0,0,0],['2012',0,0,0,0],['2013',0,0,0,0],['2014',0,0,0,0],['2015',0,0,0,0],['2016',20,20,11,278.75]]);
        var chart = new google.charts.Bar(document.getElementById("chart_div"));
        chart.draw(data, google.charts.Bar.convertOptions(options));

        google.visualization.events.addListener(chart, "select", selectHandler);

        function selectHandler(e)     {
           var year = data.getValue(chart.getSelection()[0].row, 0);
           link("pid=30&eid=88&date="+year+"-1-1");
        }
      }

function link(value)
{
	var form = document.forms['plaatenergy'];
	var newInput = document.createElement('input');
	newInput.setAttribute('type','hidden');
	newInput.setAttribute('name','token');
	newInput.setAttribute('value',value);
	form.appendChild(newInput);		
		
	form.submit();
}

Oracle SQL Loader

control file dwh.ctl

LOAD DATA
APPEND INTO TABLE CDR_UNRATED
(
WK_BMP_USAGE INTEGER EXTERNAL TERMINATED BY “,”,
WK_USAGE_TYPE INTEGER EXTERNAL TERMINATED BY “,”,
WK_EXCHANGE_ID INTEGER EXTERNAL TERMINATED BY “,”,
WK_TRANS_DT INTEGER EXTERNAL TERMINATED BY “,”,
WK_TRANS_TM INTEGER EXTERNAL TERMINATED BY “,”,
CDR_SEQUENCE_NUMBER INTEGER EXTERNAL TERMINATED BY “,”,
TRANS_DT DATE “ddmmyyyyhh24miss” TERMINATED BY “,”,
UNITS INTEGER EXTERNAL TERMINATED BY “,”,
RELATED_CALL_NUMBER INTEGER EXTERNAL TERMINATED BY “,”,
A_NUMBER CHAR TERMINATED BY “,”,
C_NUMBER CHAR TERMINATED BY “,”,
IN_SERVICE_IND CHAR TERMINATED BY “,”,
CALL_STATUS CHAR TERMINATED BY “,”,
MARKETING_CALL_STATUS CHAR TERMINATED BY “,”,
ANSWER_TIME INTEGER EXTERNAL TERMINATED BY “,”,
B_NUMBER CHAR TERMINATED BY “,”,
CHARGED_NUMBER CHAR TERMINATED BY “,”,
EXTERNAL_TYPE_ID INTEGER EXTERNAL TERMINATED BY “,”,
B_CATEGORY CHAR TERMINATED BY “,”,
SERVICE_TERMINATING CHAR TERMINATED BY “,”,
EXCEPTION_CODE CHAR TERMINATED BY “,”,
MEDIA_NUMBER CHAR TERMINATED BY “,”
)

CSV file
086,01,12345678912345,12345678912345,31122006,235959,1,31122006235959,12,0301234567,0301234567,10,U,A,5,182123451,282123451,1,15,I,abcdeabcdeabcde,1

Start SQL Loader
$ sqlldr TCT/TCT@ORCL control=dwh.ctl log=dwh_import.log data=DHW_20060501235959.csv

Oracle Application Server commands

Oracle Application Server

Show Status
$ opmnctl status

Show Cluster status
$ opmnctl @cluster status

Stop single process
$ opmnctl stopproc -process-type=XXX

Start single process
$ opmnctl startproc -process-type=XXX

Stop all processes for a cluster
$ opmnctl @cluster stopall

Start all processes for a cluster
$ opmnctl @cluster startall

———————————————

Create new instance
$ createinstance – instanceName XXXX
$ opmnctl reload
$ opmnctl startall
Note: New instance will create a new group!

Remove instance
$ removeinstance -instanceName XXXX

———————————————-

Cluster IP address = 225.0.0.2
Cluster Port Number = 6790

———————————————-