PlaatSoft

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
	

# 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

Zie https://upstreamwithoutapaddle.com/home-lab/nexus-pi/

<?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>