Category: General

RDF Graph example (Query language: SPARQL)

As a framework for representing the Web, Resource Description Framework (RDF) captures structure using a triple, the basic unit in RDF. A triple is a statement with three elements: two nodes connected by an edge (also known as a relationship). Each triple is identified by a Uniform Resource Identifier (URI) as subject-predicate-object:

– The subject is a resource (node) in the graph;
– The predicate represents an edge (relationship)
– The object is another node or a literal value.

More information:
https://rdf4j.org/documentation/tutorials/getting-started/

Property Graph example (Query Language: Cypher)

Information is organized as nodes, relationships, and properties in a property graph. Nodes are tagged with one or more labels, identifying their role in the network. Nodes can also store any number of properties as key-value pairs. Relationships provide directed, named connections between two nodes. Relationships always have a direction, a type, a start node, and an end node, and they can have properties, just like nodes. Although relationships are always directed, they can be navigated efficiently in either direction.

What is the difference between SPARQL and Cypher Query Language?
– SPARQL is the query language for accessing data in the Resource  description Framework (RDF) GRAPH database.
– Cypher is the corresponding language for the data represented in property GRAPH database

More information:
https://neo4j.com/blog/rdf-vs-property-graphs-knowledge-graphs/

INSERT
CREATE (diana:Person {name: “Diana”})
CREATE (melissa:Person {name: “Melissa”, twitter: “@melissa”})
CREATE (xyz:Company {name: “XYZ”})
CREATE (diana)-[:WORKS_FOR]->(xyz)
CREATE (diana)-[:IS_FRIENDS_WITH]->(melissa)

UPDATE
MATCH (p:Person {name: ‘Diana’}) SET p.birthdate = date(‘1980-01-01’)
MATCH (:Person {name: ‘Diana’})-[rel:WORKS_FOR]-(:Company {name: ‘XYZ’}) SET rel.startYear = date({year: 2018})

DELETE
MATCH (j:Person {name: ‘Diana’})-[r:IS_FRIENDS_WITH]->(m:Person{name: ‘Melissa’}) DELETE j,r,m

SELECT
MATCH (p:Product) RETURN p.productName, p.unitPrice ORDER BY p.unitPrice DESC LIMIT 10;

INSERT

PREFIX plaat: <http://www.plaatsoft.nl/>
INSERT DATA {
<http://leerling/1> plaat:firstName “Kees”.
<http://leerling/2> plaat:firstName “Jan”.
<http://leerling/3> plaat:firstName “Piet”.
<http://leerling/4> plaat:firstName “Klaas”.
<http://school/1> plaat:name “Bospark”.
<http://school/1> plaat:level “LTS”.
<http://school/1> plaat:city “Alphen aan den Rijn”.
<http://school/2> plaat:name “Lammerschans”.
<http://school/2> plaat:level “MTS”.
<http://school/2> plaat:city “Leiden”.
<http://school/3> plaat:name “Hogeschool van Utrecht”.
<http://school/3> plaat:level “HTS”.
<http://school/3> plaat:city “Utrecht”.
<http://leerling/1> plaat:school <http://school/1>.
<http://leerling/1> plaat:school <http://school/2>.
<http://leerling/1> plaat:school <http://school/3>.
<http://leerling/2> plaat:school <http://school/1>.
<http://leerling/3> plaat:school <http://school/2>.
}

QUERY
# Show all leerlingen of school1
PREFIX plaat: <http://www.plaatsoft.nl/>
SELECT ?x ?firstName
WHERE
{
?x plaat:firstName ?firstName.
?x plaat:school <http://school/1>
}

DELETE
PREFIX plaat: <http://www.plaatsoft.nl/>
DELETE { ?x plaat:firstName ‘Kees’ }
WHERE { ?x plaat:firstName ‘Kees’ }

UPDATE (SPARQL use delete / insert pattern)
PREFIX plaat: <http://www.plaatsoft.nl/>
DELETE { ?x plaat:firstName ‘Kees’ }
INSERT { ?x plaat:firstName ‘William’ }
WHERE { ?x plaat:firstName ‘Kees’ }

Today i have migrated most of my websites to new hardware:
– Raspberry Pi 4B (2GB) / 32GB SDCard.

I have also upgrade the following software:
– Operating System to Debian Bookworm (kernel 6.1.0-rpi7-rpi-v8)
– WordPress to v6.4.2
– php to v8.2.7

If you detect any problem, please let me known!

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)
	

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