137 lines
3.5 KiB
Plaintext
137 lines
3.5 KiB
Plaintext
# 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
|
|
|
|
|
|
|
|
|
|
|
|
|