# install packages
zypper install -y gcc
zypper install -y make
zypper install -y automake
zypper install -y readline-devel
zypper install -y zlib-devel
zypper install -y openssl-devel

# compile from sources
mkdir -p /app/kit/postgresql
wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz

cd /app/kit/postgresql/postgresql-15.3

mkdir -p /app/postgres/15.3

./configure \
        --prefix=/app/postgres/15.3 \
        --datarootdir=/data \
        --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

# create/opdate .bash_profile for postgres user:
------------------------------------------------------------
alias listen='lsof -i -P | grep -i "listen"'

export POSTGRES_HOME=/app/postgres/15.3
export PGDATA=/data/postgresql/dbf

export LD_LIBRARY_PATH=$POSTGRES_HOME/lib:$LD_LIBRARY_PATH
export PATH=$POSTGRES_HOME/bin:$PATH
------------------------------------------------------------

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

# sinitialize and start PostgreSQL server
mkdir -p $PGDATA
pg_ctl -D /data/postgresql/dbf -l /home/postgresql.log start


# add to .bash_profile

export PS1="\u@\h:\w> "
alias listen='lsof -i -P | grep -i "listen"'

export POSTGRES_HOME=/app/postgres/15.3
export PGDATA=/data/postgresql/dbf

export LD_LIBRARY_PATH=$POSTGRES_HOME/lib:$LD_LIBRARY_PATH
export PATH=$POSTGRES_HOME/bin:$PATH


# init database and startup
initdb
pg_ctl -D /data/postgresql/dbf -l /home/postgres/postgres.log start

# test local connection
psql
\db

# define listening interfaces and ports in $PGDATA/postgresql.conf
listen_addresses = '127.0.0.1,192.168.0.101,192.168.1.101'
port = 5432

# change postgres user password
psql
alter user postgres password 'secret';

# activate password authentification on all interfaces, from any host, to any database, using any user
# add lines in $PGDATA/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/24            md5
host    all             all             192.168.0.101/24        md5
host    all             all             192.168.1.101/24        md5

# tst a remote connection:
psql -h aquaris -U postgres


# create systemd service
# it was not possible for me to use environement variable do define the path of pg_ctl binary

cat /usr/lib/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

Environment=PGDATA=/data/postgresql/dbf
Environment=PGLOG=/home/postgres/postgresql.log

ExecStart=/app/postgres/15.3/bin/pg_ctl -D ${PGDATA} -l ${PGLOG} start
ExecStop=/app/postgres/15.3/bin/pg_ctl stop 

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target


# start/stop/status and enable service for automatic startup
systemctl start postgresql
systemctl stop postgresql
systemctl status postgresql
systemctl enable postgresql


# to enable WAL archiving, set following parameters in $PGDATA/postgresql.conf
wal_level = replica 
archive_mode = on
archive_command = ''test ! -f /backup/postgresql/wal/%f && cp %p /backup/postgresql/wal/%f''
archive_timeout = 3600 # optional, to force a switch every 1 hour

# https://public.dalibo.com/exports/formation/manuels/modules/i2/i2.handout.html