YYS

Kamis, 26 Desember 2024

Example Config Nginx

Redirect dari server lain

#nano /etc/nginx/conf.d/example.yoniys.com.conf

server {

listen 443 ssl;

server_name example.yoniys.com;

access_log /var/log/nginx/example.yoniys.com.access.log;

error_log /var/log/nginx/example.yoniys.com.error.log;

ssl_certificate /ssl/example.yoniys.com_ecc/example.yoniys.com.cer; # directory ssl

ssl_certificate_key /ssl/example.yoniys.com_ecc/example.yoniys.com.key;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

ssl_prefer_server_ciphers on;

ssl_ciphers 'EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH';

ssl_stapling on;

ssl_stapling_verify on;

location / {

proxy_set_header Host $host;

proxy_set_header X-Forwarded-Proto $scheme;

proxy_set_header X-Forwarded-Port $server_port;

proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

proxy_ssl_verify off;

# config ini mengarah ke ip public suatu server dimana service running di port 9000

proxy_pass http://192.168.5.234:9000;

#proxy_pass xxx;

proxy_http_version 1.1;

}

server {

listen 80;

server_name example.yoniys.com;

# redirects both www and non-www to https

return 301 https://$host$request_uri;

}

Config nginx untuk expose service dikubernetes menggunakan Node Port.

#nano /etc/nginx/conf.d/wildcard.yoniys.com.conf

map $http_upgrade $connection_upgrade {

default Upgrade;

'' close;

}

upstream yoniyscom {

server 172.16.1.12:31733; #diisi dengan ip cluster kubernetes (biasanya ip master), port merupakan config pada NodePort di service kubernetes

server 172.16.1.13:31733;

server 172.16.1.14:31733;

}

server {

listen 443 ssl http2;

server_name ~^(?<subdomain>.+)\.yoniys\.com yoniys.com;

access_log /var/log/nginx/yoniys.com.access.log;

error_log /var/log/nginx/yoniys.com.error.log;

ssl_certificate /etc/nginx/ssl/star-yoniys.com/yoniys.com.cert;

ssl_certificate_key /etc/nginx/ssl/star-yoniys.com/yoniys.com.key;

# Temuan pentest

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

ssl_prefer_server_ciphers on;

#ssl_ciphers "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256 EECDH+aRSA+SHA384 EECDH+aRSA+SHA256 EECDH+aRSA+RC4 EECDH EDH+aRSA HIGH !RC4 !aNULL !eNULL !LOW !3DES !MD5 !EXP !PSK !SRP !DSS";

# Enable OSCP

ssl_stapling on;

ssl_stapling_verify on;

ssl_trusted_certificate /etc/nginx/ssl/star-yoniys.com/yoniys.com.chain;

# Improvement dari NGINX server development

# SSL session cache

ssl_session_cache shared:TLS:2m;

ssl_buffer_size 4k;

# HSTS

# instruct browsers to enforce secure connections

#add_header Strict-Transport-Security "max-age=63072000" always;

# Prevents the webpage from being embedded in frames from other domains.

#add_header X-Frame-Options SAMEORIGIN;

# Prevents browsers from interpreting files as a different MIME type than declared by the server.

#add_header X-Content-Type-Options nosniff;

# Enables the browser's Cross-Site Scripting (XSS) filter.

#add_header X-XSS-Protection "1; mode=block";

location / {

proxy_set_header Host $host;

proxy_set_header X-Forwarded-Proto $scheme;

proxy_set_header X-Forwarded-Port $server_port;

proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

proxy_ssl_verify off;

proxy_pass http://yoniyscom;

proxy_http_version 1.1;

proxy_request_buffering off;

proxy_set_header Upgrade $http_upgrade;

proxy_set_header Connection $connection_upgrade;

# This allows the ability for the execute shell window to remain open for up to 15 minutes. Without this parameter, the default is 1 minute and will automatically close.

proxy_read_timeout 900s;

#client_max_body_size 100M;

#post_max_size 30M;

#upload_max_filesize 30M;

#memory_limit 30M;

#proxy_cache PROXYCACHE;

#proxy_cache_valid 200 15m;

#proxy_cache_valid 404 2m;

}

server {

listen 80;

server_name ~^(?<subdomain>.+)\.yoniys\.com yoniys.com;

# redirects both www and non-www to https

return 301 https://$host$request_uri;

}

Example Config Apache HTTPD

Redirect to https

#nano /etc/httpd/conf.d/example.conf

ProxyPreserveHost On

ProxyRequests Off

AllowEncodedSlashes NoDecode

ServerName example.yoniys.com

ServerAlias example.yoniys.com

Redirect permanent / https://example.yoniys.com

</VirtualHost>

DocumentRoot /var/www/html/example

ServerName example.yoniys.com

SSLEngine on

SSLProtocol all -SSLv2 -SSLv3

SSLCipherSuite ALL:!ADH:!EXPORT:!SSLv2:RC4+RSA:+HIGH:+MEDIUM:+LOW

ErrorLog logs/example.yoniys.com_error_log

TransferLog logs/example.yoniys.com_access_log

LogLevel warn

SSLOptions +StdEnvVars

</Files>

Options -Indexes +FollowSymLinks

AllowOverride None

Require all granted

</Directory>

SetEnvIf User-Agent ".*MSIE.*" nokeepalive ssl-unclean-shutdown downgrade-1.0 force-response-1.0

CustomLog logs/ssl_request_log "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"

SSLCertificateFile /etc/httpd/ssl/apache/STAR_yoniys_com.crt

SSLCertificateKeyFile /etc/httpd/ssl/apache/STAR_yoniys_com.key

#Include /etc/letsencrypt/options-ssl-apache.conf

SSLCertificateChainFile /etc/httpd/ssl/apache/STAR_yoniys_com_chain.crt

</VirtualHost>

Example config container docker.

ServerName example.yoniys.com

SSLEngine on

SSLProxyEngine On

ProxyRequests Off

ProxyPreserveHost On

SSLProxyVerify none

SSLProxyCheckPeerCN off

SSLProxyCheckPeerName off

SSLProxyCheckPeerExpire off

SSLCertificateFile /etc/httpd/certificates/STAR_yoniys_com.crt

SSLCertificateKeyFile /etc/httpd/certificates/STAR_yoniys_com.key

SSLCertificateChainFile /etc/httpd/certificates/STAR_yoniys_com_chain.crt

ProxyPass / https://192.168.10.203:8081/ # example container running di port 8081, ip tersebut merupakan ip private vm

ProxyPassReverse / https://192.168.10.203:8081/

</VirtualHost>

ETCD Fixing - NoSpace

Bash Script Fixing ETCD No Space

#!/bin/bash

CA=/opt/apps/certificate/ca.pem

CRT=/opt/apps/certificate/postgres.pem

KEY=/opt/apps/certificate/postgres-key.pem

DATE=`date +%Y%m%d`

HOST_1=192.168.56.11

HOST_2=192.168.56.12

HOST_3=192.168.56.13

DIR=/opt/apps/etcd/backup/

echo "----------Start Backup ETCD----------"

etcdctl snapshot save /opt/apps/etcd/backup/${DATE}_etcd.db --endpoints=https://$HOST_1:2379 --cacert=$CA --cert=$CRT --key=$KEY

echo "----------End Backup ETCD----------"

echo "----------Start Compact ETCD----------"

#obtain the latest revision to compact

#REVISION=`etcdctl endpoint status --write-out=json --endpoints=https://$HOST_2:2379 --cacert=$CA --cert=$CRT --key=$KEY | egrep -o '"revision":[0-9]*' | egrep -o '[0-9]*'`

REVISION=`etcdctl endpoint status --write-out=json --endpoints=https://192.168.56.11:2379 --cacert=/opt/apps/certificate/ca.pem --cert=/opt/apps/certificate/postgres.pem --key=/opt/apps/certificate/postgres-key.pem | egrep -o '"revision":[0-9]*' | egrep -o '[0-9]*'`

#check etcd status snapshot

etcdutl --write-out=table snapshot status /opt/apps/etcd/backup/${DATE}_etcd.db

etcdctl compact $REVISION --endpoints=https://$HOST_1:2379 --cacert=$CA --cert=$CRT --key=$KEY

echo "----------End Compact Etcd----------"

echo "----------Start Fixing Error : NOSPACE----------"

#Defrag-Disarm

etcdctl --endpoints=https://192.168.56.11:2379,https://192.168.56.12:2379,https://192.168.56.13:2379 --cacert=$CA --cert=$CRT --key=$KEY --command-timeout=120s defrag

etcdctl --endpoints=https://192.168.56.11:2379,https://192.168.56.12:2379,https://192.168.56.13:2379 --cacert=$CA --cert=$CRT --key=$KEY --command-timeout=120s alarm disarm

echo "----------End Fixing Error : NOSPACE----------"

echo "----------Start remove OLD backup db ETCD----------"

#remove backup after 15 days

find ${DIR} -type f -iname "*.db" -mtime +14 -exec rm {} \;

echo "----------End remove OLD backup db ETCD----------"

Senin, 23 Desember 2024

Setup High Availability PostgreSQL Cluster - Patroni Cluster

Persiapan

Menggunakan 3 vm atau host :

VM1 : hostname: postgresql-1, OS: Rocky Linux 9.2 => ip : 192.168.56.11
VM2 : hostname: postgresql-2, OS: Rocky Linux 9.2 => ip : 192.168.56.12
VM3 : hostname: postgresql-3, OS: Rocky Linux 9.2 => ip : 192.168.56.13

Service atau Aplikasi :

Postgresql-16
Etcd
Keepalive
Haproxy
Patroni

Pembagian IP :

Hostname dan ip berikut bisa ditambahkan di /etc/hosts

192.168.56.11 postgres-1 etcd-1
192.168.56.12 postgres-2 etcd-2
192.168.56.13 postgres-3 etcd-3
192.168.56.5 ip-vip #merupakan ip virtual yang akan diconfig di keepalive

Directory

Disetiap vm kita buatkan directory untuk menyimpan config maupun file yg dibutuhkan, berikut list directory :

mkdir -p /opt/apps/certificate
mkdir -p /opt/apps/etcd
mkdir -p /opt/apps/haproxy
mkdir -p /opt/apps/keepalive
mkdir -p /opt/apps/patroni/log
mkdir -p /var/lib/pgsql/etcd/

Open Port

sudo firewall-cmd --permanent --zone=public --add-port=5432/tcp
sudo firewall-cmd --permanent --zone=public --add-port=2380/tcp
sudo firewall-cmd --permanent --zone=public --add-port=2379/tcp
sudo firewall-cmd --permanent --zone=public --add-port=2376/tcp
sudo firewall-cmd --permanent --zone=public --add-port=8008/tcp
sudo firewall-cmd --permanent --zone=public --add-port=5431/tcp
sudo systemctl reload firewalld && firewall-cmd --list-ports

Installasi Postgresql-16

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install postgresql16-server postgresql16-contrib postgresql16-libs pg_cron_16 pg_checksums_16
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

NB : installasi postgres cukup sampai initdb saja, N VBkarena kita tidak akan running service postgres

Install ETCD

Generate certificate untuk setup etcd.

Download dan install cfssl

curl -OL https://pkg.cfssl.org/R1.2/cfssl_linux-amd64 -OL https://pkg.cfssl.org/R1.2/cfssljson_linux-amd64

chmod +x cfssl_linux-amd64 cfssljson_linux-amd64

sudo mv cfssl_linux-amd64 /usr/local/bin/cfssl

sudo mv cfssljson_linux-amd64 /usr/local/bin/cfssljson

#nano /opt/apps/certificate/ca-config.json

{

"signing": {

"default": {

"expiry": "876000h"

"profiles": {

"postgres": {

"usages": ["signing", "key encipherment", "server auth", "client auth"],

"expiry": "87600h"

}

nb : cukup dibuat di salah satu host

#nano /opt/apps/certificate/ca-csr.json

{

"CN": "Postgres",

"key": {

"algo": "rsa",

"size": 2048

"names": [

{

"C": "IN",

"L": "Yogyakarta",

"O": "Postgres",

"OU": "CA",

"ST": "Yoni"

}

]

}

nb : cukup dibuat di salah satu host

cp -ar ca-csr.json postgres-csr.json

#Generate dengan perintah berikut :

cfssl gencert -ca=ca.pem -ca-key=ca-key.pem -config=ca-config.json -hostname=192.168.56.11,192.168.56.12,192.168.56.13,192.168.56.5,127.0.0.1,postgres-1,postgres-2,postgres-3,etcd-1,etcd-2,etcd-3 -profile=postgres postgres-csr.json | cfssljson -bare postgres

#Copy kesemua server

scp /opt/apps/certificate/* postgres-2:/opt/apps/certificate/

scp /opt/apps/certificate/* postgres-3:/opt/apps/certificate/

Create config etcd

#nano /opt/apps/etcd/etcd.conf

ETCD_NAME=etcd-1

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.11:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.11:2379,https://127.0.0.1:2379"

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.11:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.11:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

nb : config etcd host postgres-1

ETCD_NAME=etcd-2

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.12:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.12:2379,https://127.0.0.1:2379"

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.12:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.12:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

nb : config etcd host postgres-2

ETCD_NAME=etcd-3

ETCD_DATA_DIR="/var/lib/pgsql/etcd/"

ETCD_LISTEN_PEER_URLS="https://192.168.56.13:2380"

ETCD_LISTEN_CLIENT_URLS="https://192.168.56.13:2379,https://127.0.0.1:2379"

# [cluster]

ETCD_INITIAL_ADVERTISE_PEER_URLS="https://192.168.56.13:2380"

ETCD_ADVERTISE_CLIENT_URLS="https://192.168.56.13:2379"

ETCD_INITIAL_CLUSTER="etcd-1=https://192.168.56.11:2380,etcd-2=https://192.168.56.12:2380,etcd-3=https://192.168.56.13:2380"

ETCD_INITIAL_CLUSTER_STATE="new"

ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-controller"

ETCD_QUOTA_BACKEND_BYTES="8589934592"

#ETCD_ENABLE_V2="true"

#[security]

ETCD_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_TRUSTED_CA_FILE="/opt/apps/certificate/ca.pem"

ETCD_PEER_CERT_FILE="/opt/apps/certificate/postgres.pem"

ETCD_PEER_KEY_FILE="/opt/apps/certificate/postgres-key.pem"

ETCD_PEER_CLIENT_CERT_AUTH="true"

nb : config etcd host postgres-3

Create systemd etcd.

# nano /etc/systemd/system/etcd.service

[Unit]

Description=ETCD key-value store

Documentation=https://github.com/etcd-io/etcd

After=network.target

[Service]

Type=notify

User=etcd

Group=etcd

EnvironmentFile=/opt/apps/etcd/etcd.conf

ExecStart=/usr/local/bin/etcd

Restart=always

RestartSec=10s

LimitNOFILE=40000

[Install]

WantedBy=multi-user.target

Install etcd, metode yang dilakukan download binary etcd.

wget https://github.com/etcd-io/etcd/releases/download/v3.5.17/etcd-v3.5.17-linux-amd64.tar.gz

tar zxvf etcd-v3.5.17-linux-amd64.tar.gz

cp -ar etcd-v3.5.17-linux-amd64/etcd* /usr/local/bin/

groupadd -f -g 1501 etcd

useradd -c "etcd user" -d /opt/apps/etcd -s /bin/false -g etcd -u 1501 etcd

chown -R etcd:etcd /var/lib/pgsql/etcd/ /opt/apps/etcd/ /opt/apps/certificate/

chmod 666 /opt/apps/certificate/

systemctl daemon-reload

systemctl start etcd

nb : dilakukan disemua host

Pengecekan cluster etcd.

etcdctl endpoint status --write-out=table --endpoints=192.168.56.11:2379,192.168.56.13:2379,192.168.56.13:2379 --cacert=/opt/apps/certificate/ca.pem --cert=/opt/apps/certificate/postgres.pem --key=/opt/apps/certificate/postgres-key.pem

hasil pengecekan cluster etcd

Install Keepalived

Install keepalived

sudo dnf install -y keepalived

Create config keepalived.

# nano /opt/apps/keepalive/keepalived.conf

! Configuration File for keepalived

global_defs {

}

vrrp_script chk_haproxy {

script "killall -0 haproxy" # check the haproxy process

interval 2 # every 2 seconds

weight 2 # add 2 points if OK

}

vrrp_instance VI_1 {

interface enp0s3 # interface to monitor

state MASTER # MASTER on haproxy1, BACKUP

# on haproxy2

virtual_router_id 2 # Set to last digit of IP

priority 101 # 101 on haproxy1, 100 on

# haproxy2 etc

authentication {

auth_type PASS

auth_pass Pa55w@rd!

}

virtual_ipaddress {

192.168.56.5 # virtual ip address

}

track_script {

chk_haproxy

}

nb : config keepalived di host postgres-1

! Configuration File for keepalived

global_defs {

}

vrrp_script chk_haproxy {

script "killall -0 haproxy" # check the haproxy process

interval 2 # every 2 seconds

weight 2 # add 2 points if OK

}

vrrp_instance VI_1 {

interface enp0s3 # interface to monitor

state BACKUP # MASTER on haproxy1, BACKUP

# on haproxy2

virtual_router_id 2 # Set to last digit of IP

priority 100 # 101 on haproxy1, 100 on

# haproxy2 etc

authentication {

auth_type PASS

auth_pass Pa55w@rd!

}

virtual_ipaddress {

192.168.56.5 # virtual ip address

}

track_script {

chk_haproxy

}

nb : config keepalived di host postgres-2

! Configuration File for keepalived

global_defs {

}

vrrp_script chk_haproxy {

script "killall -0 haproxy" # check the haproxy process

interval 2 # every 2 seconds

weight 2 # add 2 points if OK

}

vrrp_instance VI_1 {

interface enp0s3 # interface to monitor

state BACKUP # MASTER on haproxy1, BACKUP

# on haproxy2

virtual_router_id 2 # Set to last digit of IP

priority 100 # 101 on haproxy1, 100 on

# haproxy2 etc

authentication {

auth_type PASS

auth_pass Pa55w@rd!

}

virtual_ipaddress {

192.168.56.5 # virtual ip address

}

track_script {

chk_haproxy

}

nb : config keepalived di host postgres-3

Create symlink config keepalived

ln -s /opt/apps/keepalived/keepalived.conf /etc/keepalived/

nb : dilakukan disemua host

Create systemd keepalived.
#nano /etc/systemd/system/keepalived.service

[Unit]

Description=LVS and VRRP High Availability Monitor

After=network-online.target syslog.target

Wants=network-online.target

[Service]

Type=forking

PIDFile=/run/keepalived.pid

KillMode=process

EnvironmentFile=-/etc/sysconfig/keepalived

ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS

ExecReload=/bin/kill -HUP $MAINPID

[Install]

WantedBy=multi-user.target

nb : dilakukan disemua host

Reload systemd dan start keepalived dilakukan disemua host.

systemctl daemon-reload

systemctl start keepalived

Pengecekan ip address apakah ip virtual (192.168.56.5) sudah ke bind, cek disemua host.

Install Patroni

Install package dan patroni.

dnf install python3-pip python3-psycopg2 python3-pip python3 gcc python3-devel python3-etcd -y

pip3 install --upgrade setuptools patroni patroni[etcd]

Create config patroni.
# nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

log:

traceback_level: INFO

level: INFO

dir: /opt/apps/patroni/log/

file_num: 5

file_size: 104857600

restapi:

listen: postgres-1:8008

connect_address: postgres-1:8008

etcd3:

hosts: 192.168.56.11:2379,192.168.56.12:2379,192.168.56.13:2379

protocol: https

cacert: /opt/apps/certificate/ca.pem

cert: /opt/apps/certificate/postgres.pem

key: /opt/apps/certificate/postgres-key.pem

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

master_start_timeout: 300

postgresql:

use_pg_rewind: true

parameters:

max_connections: 200

max_worker_processes: 32

postgresql:

listen: 127.0.0.1,192.168.56.11:5432

bin_dir: /usr/pgsql-16/bin

connect_address: 192.168.56.11:5432

data_dir: /var/lib/pgsql/16/data

pgpass: /var/lib/pgsql/.pgpass

authentication:

replication:

username: replicate

password: password123456

superuser:

username: patroni

password: password123456

parameters:

unix_socket_directories: '.'

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

nb : config patroni-1

# nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

log:

traceback_level: INFO

level: INFO

dir: /opt/apps/patroni/log/

file_num: 5

file_size: 104857600

restapi:

listen: postgres-2:8008

connect_address: postgres-2:8008

etcd3:

hosts: postgres-1:2379,postgres-2:2379,postgres-3:2379

protocol: https

cacert: /opt/apps/certificate/ca.pem

cert: /opt/apps/certificate/postgres.pem

key: /opt/apps/certificate/postgres-key.pem

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

master_start_timeout: 300

postgresql:

use_pg_rewind: true

parameters:

max_connections: 200

max_worker_processes: 32

postgresql:

listen: 127.0.0.1,192.168.56.12:5432

bin_dir: /usr/pgsql-16/bin

connect_address: 192.168.56.12:5432

data_dir: /var/lib/pgsql/16/data

pgpass: /var/lib/pgsql/.pgpass

authentication:

replication:

username: replicate

password: password123456

superuser:

username: patroni

password: password123456

parameters:

unix_socket_directories: '.'

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

nb : config patroni-2

# nano /opt/apps/patroni/patroni.yml

scope: postgres

namespace: /db/postgres16/

log:

traceback_level: INFO

level: INFO

dir: /opt/apps/patroni/log/

file_num: 5

file_size: 104857600

restapi:

listen: postgres-3:8008

connect_address: postgres-3:8008

etcd3:

hosts: postgres-1:2379,postgres-2:2379,postgres-3:2379

protocol: https

cacert: /opt/apps/certificate/ca.pem

cert: /opt/apps/certificate/postgres.pem

key: /opt/apps/certificate/postgres-key.pem

bootstrap:

dcs:

ttl: 30

loop_wait: 10

retry_timeout: 10

maximum_lag_on_failover: 1048576

master_start_timeout: 300

postgresql:

use_pg_rewind: true

parameters:

max_connections: 200

max_worker_processes: 32

postgresql:

listen: 127.0.0.1,192.168.56.13:5432

bin_dir: /usr/pgsql-16/bin

connect_address: 192.168.56.13:5432

data_dir: /var/lib/pgsql/16/data

pgpass: /var/lib/pgsql/.pgpass

authentication:

replication:

username: replicate

password: password123456

superuser:

username: patroni

password: password123456

parameters:

unix_socket_directories: '.'

tags:

nofailover: false

noloadbalance: false

clonefrom: false

nosync: false

nb : config patroni-2

Create systemd patroni.

#nano /etc/systemd/system/patroni.service

[Unit]

Description=Runners to orchestrate a high-availability PostgreSQL

After=syslog.target network.target

[Service]

Type=simple

User=postgres

Group=postgres

ExecStart=/usr/local/bin/patroni /opt/apps/patroni/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

[Install]

WantedBy=multi-user.target

nb : create semua host

Start patroni host postgres-1

#Nyalakan terlebih dahulu service postgres di host postgres-1, kita perlu membuat user database untuk patroni dan replikasi

systemctl start postgresql-16

su - postgres

psql # masuk database postgres

create role replicate with REPLICATION LOGIN encrypted password 'password123456';

create role patroni WITH SUPERUSER LOGIN ENCRYPTED PASSWORD 'password123456';

exit

#Tambahkan config di pg_hba

nano /var/lib/pgsql/16/data/pg_hba.conf

host all all 192.168.56.0/24 md5

host replication replicate 192.168.56.0/24 md5

#Edit config postgres agar dapat diakses dari host lain, enable dan ubah dari localhost menjadi *

nano /var/lib/pgsql/16/data/postgresql.conf

listen_addresses = '*'

#Start patroni di postgres-1

systemctl stop postgresql-16

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni

Start patroni host postgres-2

#hapus data dir postgres pada host postgres-2

rm -rf /var/lib/pgsql/16/data

#Start patroni di postgres-2

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni

Start patroni host postgres-3

#hapus data dir postgres pada host postgres-3

rm -rf /var/lib/pgsql/16/data

#Start patroni di postgres-3

systemctl daemon-reload

systemctl start patroni

systemctl enable patroni

Pengecekan cluster patroni

patronictl -c /opt/apps/patroni/patroni.yml list

Install haproxy.

Install package dan haproxy disemua host.

#install package

dnf install -y curl gcc openssl-devel libnl3-devel net-snmp-devel

#install haproxy
wget https://www.haproxy.org/download/3.1/src haproxy-3.1.1.tar.gz

tar zxvf haproxy-3.1.1.tar.gz

cd haproxy-3.1.1

make TARGET=linux-glibc USE_OPENSSL=1 USE_ZLIB=1 USE_CRYPT_H=1 USE_LIBCRYPT=1 USE_SYSTEMD=1

make install

groupadd -f -g 1502 haproxy

useradd -c "haproxy user" -d /opt/apps/haproxy/ -s /bin/false -g haproxy -u 1502 haproxy

Create config haproxy disemua host.

#nano /opt/apps/haproxy/haproxy.cfg

global

user haproxy

group haproxy

pidfile /var/run/haproxy.pid

stats socket /var/run/haproxy.stats

maxconn 20480

defaults

mode tcp

log global

option dontlognull

option redispatch

retries 2

timeout client 10m

timeout connect 4s

timeout server 10m

timeout check 5s

listen production

bind 192.168.56.5:5432

option httpchk OPTIONS/master

http-check expect status 200

default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

server db-postgresql-1 192.168.56.11:5432 maxconn 1280 check port 8008

server db-postgresql-2 192.168.56.12:5432 maxconn 1280 check port 8008

server db-postgresql-3 192.168.56.13:5432 maxconn 1280 check port 8008

listen standby

bind 192.168.56.5:5431

option httpchk OPTIONS/replica

http-check expect status 200

default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions

server db-postgresql-1 192.168.56.11:5432 maxconn 1280 check port 8008

server db-postgresql-2 192.168.56.12:5432 maxconn 1280 check port 8008

server db-postgresql-3 192.168.56.13:5432 maxconn 1280 check port 8008

Create systemd haproxy disemua host.

#nano /etc/systemd/system/haproxy.service

[Unit]

Description=HAProxy Load Balancer

After=network-online.target

Wants=network-online.target

[Service]

EnvironmentFile=-/etc/sysconfig/haproxy

Environment="CONFIG=/opt/apps/haproxy/haproxy.cfg" "PIDFILE=/run/haproxy.pid"

ExecStartPre=/bin/sleep 4

ExecStartPre=/usr/local/sbin/haproxy -f $CONFIG -c -q $OPTIONS

ExecStart=/usr/local/sbin/haproxy -Ws -f $CONFIG -p $PIDFILE $OPTIONS

ExecReload=/usr/local/sbin/haproxy -f $CONFIG -c -q $OPTIONS

ExecReload=/bin/kill -USR2 $MAINPID

KillMode=mixed

SuccessExitStatus=143

Type=notify

[Install]

WantedBy=multi-user.target

Start haproxy semua host.

systemctl daemon-reload

systemctl start haproxy

systemctl enable haproxy

Testing login postgres menggunakan ip floating atau ip virtual (192.168.56.5).

Coba login postgres sebagai write-read, dan test create database, harusnya berhasil create database.

psql -Upatroni -h192.168.56.5 -p5432 -d postgres

Coba login postgres sebagai read, dan test create database, harusnya gagal create database.

psql -Upatroni -h192.168.56.5 -p5431 -d postgres