notes/postgresql/postgres_16_on_Rocky_9_setup_01.txt

# create VM with Rocky Linux 9

dd if=/dev/zero of=/vm/ssd0/aquaris/boot_01.img bs=1G count=1
dd if=/dev/zero of=/vm/ssd0/aquaris/root_01.img bs=1G count=8
dd if=/dev/zero of=/vm/ssd0/aquaris/swap_01.img bs=1G count=2
dd if=/dev/zero of=/vm/ssd0/aquaris/app_01.img  bs=1G count=8

virt-install \
  --graphics vnc,password=secret,listen=0.0.0.0 \
  --name=aquaris \
  --vcpus=2 \
  --memory=4096 \
  --network bridge=br0 \
  --network bridge=br0 \
  --cdrom=/vm/hdd0/_kit_/Rocky-9.3-x86_64-minimal.iso \
  --disk /vm/ssd0/aquaris/boot_01.img \
  --disk /vm/ssd0/aquaris/root_01.img \
  --disk /vm/ssd0/aquaris/swap_01.img \
  --disk /vm/ssd0/aquaris/app_01.img \
  --os-variant=rocky9

# VM network setup after creation

nmcli connection show
nmcli connection show --active

nmcli connection modify enp1s0 ipv4.address 192.168.0.101/24
nmcli connection modify enp1s0 ipv4.method manual ipv6.method ignore
nmcli connection modify enp1s0 ipv4.gateway 192.168.0.1
nmcli connection modify enp1s0 ipv4.dns 192.168.0.8
nmcli connection modify enp1s0 ipv4.dns-search swgalaxy

nmcli connection modify enp2s0 ipv4.address 192.168.1.101/24 ipv4.method manual ipv6.method ignore

# list host interfaces
hostname -I

# set host name
hostnamectl hostname aquaris.swgalaxy


# install packages
dnf install -y gcc make automake readline-devel zlib-devel openssl-devel libicu-devel.x86_64
dnf install -y zip.x86_64 tar.x86_64 libzip.x86_64 unzip.x86_64 bzip2.x86_64 bzip2-devel.x86_64 pigz.x86_64
dnf install -y wget.x86_64 lsof.x86_64 bind-utils tree.x86_64 python3-devel.x86_64 rsync.x86_64

# add data and backup disks 

# on VM get next letter for devices
lsblk

# on Dom0 create and attach the disk to VM
dd if=/dev/zero of=/vm/ssd0/aquaris/data_01.img   bs=1G count=8
dd if=/dev/zero of=/vm/ssd0/aquaris/backup_01.img bs=1G count=4

virsh attach-disk aquaris /vm/ssd0/aquaris/data_01.img   vde --driver qemu --subdriver raw --targetbus virtio --persistent
virsh attach-disk aquaris /vm/ssd0/aquaris/backup_01.img vdf --driver qemu --subdriver raw --targetbus virtio --persistent

# to list the disk of VM
virsh domblklist aquaris --details

# on VM create partitions, format and mount devices
fdisk /dev/vde
fdisk /dev/vdf 

lsblk 
pvs
pvcreate /dev/vde1
pvcreate /dev/vdf1
vgs
vgcreate vgdata /dev/vde1
vgcreate vgbackup /dev/vdf1
vgs
lvs
lvcreate -n data -l 100%FREE vgdata
lvcreate -n backup -l 100%FREE vgbackup
lvs
mkfs.xfs /dev/mapper/vgdata-data
mkfs.xfs /dev/mapper/vgbackup-backup 

mkdir -p /data /backup


echo "/dev/mapper/vgdata-data   /data    xfs     defaults        1 1" >> /etc/fstab
echo "/dev/mapper/vgbackup-backup   /backup    xfs     defaults        1 1" >> /etc/fstab

systemctl daemon-reload

mount -a
df -hT

# build PostgreSQL from sources
mkdir -p /app/postgres/product/16.2
mkdir -p /app/staging_area
cd /app/staging_area
wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz
gunzip -c postgresql-16.2.tar.gz | tar -xvf -

cd postgresql-16.2
./configure \
        --prefix=/app/postgres/product/16.2 \
        --with-ssl=openssl
make
make install

# create user postres and change owner from binaries, data and backup directories
groupadd postgres
useradd postgres -G postgres -g postgres

chown -R postgres:postgres /app /data /backup

# create/opdate .bash_profile for postgres user:
export PS1="\u@\h:\w> "
alias listen='lsof -i -P | grep -i "listen"'
alias pgenv='source /app/postgres/admin/scripts/pgenv'

# create PostgreSQL instance on port 5501
mkdir -p /app/postgres/admin
mkdir -p scripts
cd /app/postgres/admin
mkdir -p aquaris_5501/divers
mkdir -p aquaris_5501/log
mkdir -p aquaris_5501/scripts

# create a script to source PostgeSQL instance varaiables
cat <<'EOF' > /app/postgres/admin/scripts/pgenv
export PGPORT=$1
export MYHOST=$(hostname -s)
export PGHOME=/app/postgres/product/16.2

export PGDATA=/data/${MYHOST}_${PGPORT}
export PGBACKUP=/backup/${MYHOST}_${PGPORT}
export PGLOG=/app/postgres/admin/${MYHOST}_${PGPORT}/log/${MYHOST}_${PGPORT}.log

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
EOF

# sinitialize and start PostgreSQL server
pgenv 5501
initdb -D $PGDATA

# update PostgreSQL instance configuration file
# $PGDATA/postgresql.conf
listen_addresses = '*'
port = 5501

# update $PGDATA/pg_hba.conf in order to allow remote connections using a password
cat <<'EOF' >> $PGDATA/pg_hba.conf
host	all		all		all			md5
EOF

# start PostgreSQL instance
pg_ctl start -D $PGDATA -l $PGLOG
or
pg_ctl start --pgdata $PGDATA --log $PGLOG

# stop PostgreSQL instance
pg_ctl stop -m immediate

# create a database + an owner from this database
psql
postgres=# create role jawa login password 'secret';
postgres=# create database jawa;
postgres=# alter database jawa owner to jawa;
postgres=# \l
postgres=# \du

# test connection
psql -p 5501 -h aquaris -U jawa

# create users for barman: barman(superuser) and streaming_barman(replication)
createuser --superuser --replication -P barman
createuser --replication -P streaming_barman

# update $PGDATA/pg_hba.conf in order to allow replication for streaming_barman user
cat <<'EOF' >> $PGDATA/pg_hba.conf
host	replication	streaming_barman	all	md5
EOF

# ensure that following parameter are >10
postgres=# Show max_wal_senders;
postgres=# Show max_replication_slots;
# otherwise update
postgres=# ALTER SYSTEM SET max_wal_senders = 10;
postgres=# ALTER SYSTEM SET max_replication_slots = 10;. 


# Barman can be installed on a remote machine where PosgeSQL binaries are installed
# customoze ~/.bashrc on remote machine

cat <<'EOF' >> ~/.bashrc
export PS1="\u@\h:\w> "
alias listen='lsof -i -P | grep -i "listen"'

export POSTGRES_HOME=/app/postgres/product/16.2
export LD_LIBRARY_PATH=$POSTGRES_HOME/lib:$LD_LIBRARY_PATH

export PATH=$POSTGRES_HOME/bin:$PATH
EOF


# barman install
mkdir /backup/barman
mkdir /app/barman
cd /app/barman
mkdir product conf log run scripts
mkdir conf/barman.d

mkdir /app/barman/product/barman_3.10.0

python -m venv /app/barman/product/barman_3.10.0
source /app/barman/product/barman_3.10.0/bin/activate

python -m pip install --upgrade pip

pip install psycopg2
pip install barman

barman -v

# optinally, activate Barman in .bash_profile
cat <<'EOF' >> ~/.bash_profile
# Activate Barman
source /app/barman/product/barman_3.10.0/bin/activate
EOF


# store passwords
cat <<'EOF' >>~/.pgpass
aquaris:5501:*:barman:secret
aquaris:5501:*:streaming_barman:secret
EOF
chmod 0600 ~/.pgpass

# test connection
psql -h aquaris -p 5501 -U barman -d postgres
psql -h aquaris -p 5501 -U streaming_barman -d postgres

# create barman global configuration file
cat <<'EOF' > /app/barman/conf/barman.conf
[barman]
; System user
barman_user = postgres

; Directory of configuration files. Place your sections in separate files with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /app/barman/conf/barman.d

; Main directory
barman_home = /backup/barman

; Locks directory - default: %(barman_home)s
;barman_lock_directory = /app/barman/run

; Log location
log_file = /app/barman/log/barman.log

; Log level (see https://docs.python.org/3/library/logging.html#levels)
log_level = INFO

; Default compression level: possible values are None (default), bzip2, gzip, pigz, pygzip or pybzip2
compression = pigz
EOF

# for the global configuration file, create a symlync .barman.conf in the home directory
ln -s /app/barman/conf/barman.conf ~/.barman.conf

# target postgres instance example
cat <<'EOF' > /app/barman/conf/barman.d/aquaris_5501.conf
[aquaris_5501]
description =  "PostgreSQL instance on aquaris, port 5501"
conninfo = host=aquaris port=5501 user=barman dbname=postgres
streaming_conninfo = host=aquaris port=5501 user=streaming_barman dbname=postgres
backup_method = postgres
streaming_archiver = on
slot_name = barman
create_slot = auto
retention_policy = REDUNDANCY 4
EOF


# create replication slot
barman receive-wal --create-slot aquaris_5501


# create barman CRON script
cat <<'EOF' > /app/barman/scripts/barman_cron
# Setup environement
source ~/.bash_profile
# Run barmab CRON tasks
barman cron
EOF

chmod +x /app/barman/scripts/barman_cron

# scedule CRON script in crontab every 1 minute
crontab -l

* * * * * /app/barman/scripts/barman_cron > /app/barman/log/barman_cron.log

# force a switch wal on target PostgreSQL instance
barman switch-wal --force --archive aquaris_5501

# backup PostgreSQL instance and wait for all the required WAL files to be archived
barman backup aquaris_5501 --wait

# list registered PostgeSQL servers
barman list-servers

# list backup of one of all servers
barman list-backups all
barman list-backups aquaris_5501


# list files of backup required to restore a minimal consistent image
# one or more WAL will be included
barman list-files aquaris_5501 --target standalone 20240223T165330

# list only WAL that can be used in addition with the backup
# that will include necessary WAL to restore a consistent image (as in previous command) + all streamed (and automatically compressed) since the backup
barman list-files aquaris_5501 --target wal 20240223T165330

# list all files (base + WAL) availablle for restiore since the backup
barman list-files aquaris_5501 --target full 20240223T165330

# show backup informations
barman show-backup aquaris_5501 20240223T165330

# verify cecksums of backup files
barman verify-backup aquaris_5501 20240220T174149

***************************************************
barman \
	recover --remote-ssh-command 'ssh sembla' \
	aquaris_5501 latest \
	/data/restore


barman \
	recover --remote-ssh-command 'ssh sembla' \
	--get-wal \
	aquaris_5501 latest \
	/data/restore



/app/postgres/product/16.2/bin/pg_ctl \
    --pgdata=/data/restore \
    -l /tmp/restore.log \
    start


/app/postgres/product/16.2/bin/pg_ctl \
    --pgdata=/data/restore \
    -l /tmp/restore.log \
    stop




barman-wal-restore -U postgres exegol aquaris_5501 --test Bugs Bunny
**************************************************