support/notes
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
notes/postgresql/postgres_16_on_Rocky_9_setup_01.txt
Valeriu PLESNILA 26296b6d6a 2026-03-12 21:01:38
2026-03-12 22:01:38 +01:00

366 lines
9.8 KiB
Plaintext
Raw Permalink Blame History

# 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
**************************************************
Reference in New Issue View Git Blame Copy Permalink