dexter/DBAdmin
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
DBAdmin/inbox/DataBase/PostgreSQL/20211012094858-postgresql_cluster_install_howto.md
T
Paweł Domański 64944cf004 May 17, 2026, 11:40 PM
2026-05-18 06:40:19 +00:00

368 lines
18 KiB
Markdown
Raw Permalink Blame History

Notatka sporządzona na podstawie informacji ze SLack instrukcję przygotował [@MichalBulawa]
GSNI -> Postgres 13 + ETCD + Patroni + HAproxy + confd + keepalived
# Informacje ogólne
Postgres - wiadomo
etcd -> klaster do przechowywania kluczy dla rozwiązania
patroni -> coś co utrzymuje całość przy życiu ( manual później )
haproxy -> wiadomo / używane do komunikacji z “Leaderem klastra”, oraz do umożliwienia load-balancingu na replikach
confd -> odpowiedzialny za pilnowanie konfiguracji plików ( nie pozwala wprowadzić samemu zmian
keepalived -> usługa do utrzymywania VIP-a
( GSNI TO: plpa2ps01srrm, plpa2ps02srrm, plwa2ps03srrm )
1. Prereq
/etc/hosts ( na każdym serwerze wyglądają podobnie )
-sh-4.4$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
158.98.154.129 plpa2ps01srrm.plf.esni.ibm.com plpa2ps01srrm pgsql-01
158.98.154.130 plpa2ps02srrm.plf.esni.ibm.com plpa2ps02srrm pgsql-02
158.98.154.131 plwa2ps03srrm.plf.esni.ibm.com plwa2ps03srrm pgsql-03
158.98.154.122 plpa2ps00srrm.plf.esni.ibm.com plpa2ps00srrm pgsql-vip
-sh-4.4$
2. Postgres ( pre-install )
-sh-4.4$ ls -l /etc/yum.repos.d/pgdg-redhat-all.repo
-rw-r--r--. 1 root root 11072 Sep 30 16:28 /etc/yum.repos.d/pgdg-redhat-all.repo
-sh-4.4$ head /etc/yum.repos.d/pgdg-redhat-all.repo -n 15
#######################################################
# PGDG Red Hat Enterprise Linux / CentOS repositories #
#######################################################
# PGDG Red Hat Enterprise Linux / CentOS stable common repository for all PostgreSQL versions
[pgdg-common]
name=PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
repo_gpgcheck = 1
proxy=http://172.25.1.10:8080 <--------------- taka sztuczka jak dodać repozytorium postgresowe na gsni by działało
-sh-4.4$
3. squid ( opcja za firewallem więc proxy potrzebne )
[root@plpa4gp01psrm acls]# cat pgsql.services
postgresql.org
github.com
pypi.org
python.org
pythonhosted.org
[root@plpa4gp01psrm acls]#
4. sysctl
sysctl svm.swappiness=1
sysctl vm.dirty_vm.min_free_kbytes=102400
sysctl vm.dirty_expire_centisecs=1000
sysctl vm.dirty_background_bytes=67108864
sysctl vm.dirty_bytes=536870912
sysctl vm.nr_hugepages=9510
sysctl vm.zone_reclaim_mode=0
sysctl kernel.numa_balancing=0
sysctl kernel.sched_migration_cost_ns=5000000
sysctl kernel.sched_autogroup_enabled=0
sysctl net.ipv4.ip_nonlocal_bind=1
sysctl net.ipv4.ip_forward=1
sysctl net.core.netdev_max_backlog=10000
sysctl net.ipv4.tcp_max_syn_backlog=8192
sysctl net.core.somaxconn=65535
sysctl net.ipv4.tcp_tw_reuse=1
5. yum install && pip3 update
yum install -y git
yum install -y keepalived
yum install -y libyaml
yum install -y python3-psycopg2
yum install -y python2-psycopg2
yum install -y python3
yum install -y python2
yum install -y haproxy
HTTPS_PROXY=http://172.25.1.10:8080 pip3 install --upgrade pip
6. SSL
Sprawdzenie czy są zaimportowane w CA
7. System HDD + Postgres
[root@plpa2ps01srrm ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 50G 0 disk
├─sda1 8:1 0 512M 0 part /boot
└─sda2 8:2 0 49.5G 0 part
└─rootvg-backup_lv 253:12 0 10G 0 lvm /backup
sdb 8:16 0 100G 0 disk
└─databasevg-data_lv 253:13 0 100G 0 lvm /database
sr0 11:0 1 1024M 0 rom
[root@plpa2ps01srrm ~]#
8. Postgres install
<https://www.postgresql.org/download/linux/redhat/> <&#x2013; tutaj też mamy dodanie repozytorium / a je ( o ile trzeba ) edytujemy dodając proxy no i jak podaje manual na stronie
dnf -qy module disable postgresql
dnf install -y postgresql13-server
dnf install -y pgaudit15_13.x86_64
dnf install -y postgresql13-contrib
taka instalacja powoduje dodanie użytkownika w systemie o nazwie postgres oraz binarki które lądują w:
-bash-4.4$ ls -l /usr/pgsql-13/bin/
total 11104
-rwxr-xr-x. 1 root root 73192 Aug 12 12:04 clusterdb
9. Pre-Konfiguracja Porsgres-a
Pre-Konfiguracja Porsgres-a
<https://www.pgconfig.org/> &#x2014;> generuje kawałek pliku postgres.conf w zależności od tego co mamy za sprzęt na GSNI to: RHEL8 x86-64, NS-SAN, 8cpu, 16mem, max<sub>con</sub> 100, ver.13, ERP or long&#x2026; ( zapotrzebowanie dla Zabbix )
10. Uruchomienie bazy
[root@plpa2ps01srrm ~]# cd /database/
[root@plpa2ps01srrm database]# mkdir data
[root@plpa2ps01srrm database]# chown postgres:postgres data/
[root@plpa2ps01srrm database]# su - postgres
Last login: Thu Oct 7 10:59:49 CEST 2021 on pts/1
-bash-4.4$ /usr/pgsql-13/bin/initdb --pgdata=/database/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /database/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Warsaw
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-13/bin/pg_ctl -D /database/data -l logfile start
-bash-4.4$ cd /database/data/
-bash-4.4$ ls
base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf
pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal
pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact
-bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /database/data -l logfile start
waiting for server to start.... done
server started
-bash-4.4$ psql
psql (13.4)
Type "help" for help.
postgres=#
11. Hasło dla użytkownika postgres oraz na potrzeby replikacji, użytkownik do replikacji z hasłem
postgres=# ALTER USER postgres PASSWORD 'bo zupa była za słona'; // :D
ALTER ROLE
postgres=# \q
-bash-4.4$ createuser -U postgres replicator -P -c 5 --replication
Enter password for new role: 'bo zupa była za słona' // :D
Enter it again: 'bo zupa była za słona' // :D
-bash-4.4$
12. wyłączamy bazę -> bo już nam narazie nie będzie potrzebna
-bash-4.4$ /usr/pgsql-13/bin/pg_ctl stop -D /database/data
waiting for server to shut down.... done
server stopped
-bash-4.4$
13. ETCD
wget https://github.com/etcd-io/etcd/releases/download/v3.5.0/etcd-v3.5.0-linux-amd64.tar.gz
tar -xzf etcd-v3.5.0-linux-amd64.tar.gz
cp etcd-v3.5.0-linux-amd64/etcd etcd-v3.5.0-linux-amd64/etcdctl etcd-v3.5.0-linux-amd64/etcdutl /usr/bin/
[root@plwa2ps03srrm ~]# /usr/bin/etcd --version
etcd Version: 3.5.0
Git SHA: 946a5a6f2
Go Version: go1.16.3
Go OS/Arch: linux/amd64
[root@plwa2ps03srrm ~]# /usr/bin/etcdctl version
etcdctl version: 3.5.0
API version: 3.5
[root@plwa2ps03srrm ~]# /usr/bin/etcdutl version
etcdutl version: 3.5.0
API version: 3.5
[root@plwa2ps03srrm ~]#
14. konfiguracja ( serwis uruchamiany jako root / więc uprawnienia aż tak nie są ważne )
vim /usr/lib/systemd/system/etcd.service
vim /etc/etcd/etcd.conf
mkdir /etc/etcd
mkdir /var/lib/etcd
vim /etc/etcd/etcd.conf
systemctl enable etcd.service
systemctl start etcd.service
15. Patroni
vim requirements.txt
pip3 install -r requirements.txt
pip3 install patroni[etcd]
mkdir -p /etc/patroni/
chmod go-rwx /etc/patroni/
chown postgres:postgres /etc/patroni/
vim /etc/patroni/patroni.yml
vim /usr/lib/systemd/system/patroni.service
systemctl daemon-reload
echo "export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml" >> /etc/profile
cat requirements
urllib3>=1.19.1,!=1.21
ipaddress; python<sub>version</sub>==&ldquo;2.7&rdquo;
boto
PyYAML
six >= 1.7
kazoo>=1.3.1
python-etcd>=0.4.3,<0.5
python-consul>=0.7.1
click>=4.1
prettytable>=0.7
python-dateutil
pysyncobj>=0.3.8
cryptography>=1.4
psutil>=2.0.0
ydiff>=1.2.0
[root@plpa2ps01srrm ~]# patronictl version
patronictl version 2.1.1
[root@plpa2ps01srrm ~]#
16. Keepalived
zainstalowany był wcześniej, konfiguracja w plikach na serverze:
vim /etc/keepalived/keepalived.conf
vim /usr/libexec/keepalived/leader<sub>check.sh</sub>
chmod u+x /usr/libexec/keepalived/leader<sub>check.sh</sub>
standardowe rozwiązanie pgSQL+etcd+Patroni+HAProxy wykorzystuje do VIP-y keepalived i opiera go o działający proces haproxy natomiast moim zdaniem lepszym rozwiązaniem jest oprzeć go o inny mechanizm sprawdzający / o sam klaster patroni, co też zastosowane jest w GSNI ( skrypt leader<sub>check.sh</sub> )
17. HAProxy
rozwiązanie zakłada że:
- połączenie do vip:5000 - łączy nas do lidera i daje prawo zapisu i odczytu
- połączenie do vip:5001 - łączy nas do jednej z replik z prawem tylko do odczytu
moja modyfikacja daje możliwość podłączenia do vip:5432 - łączy do lidera i daje prawo do odczytu i zapisu (różnice pomiędzy podłączeniem via 5000 a 5432 są w działaniu pg<sub>hba.conf</sub>)
vim /etc/haproxy/haproxy.conf
[root@plpa2ps01srrm ~]# systemctl start patroni.service
[root@plpa2ps01srrm ~]# patronictl list
+---------------+----------------+--------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: PLPA2PS00SRRM (7016247082177975663) -----+----+-----------+
| plpa2ps01srrm | 158.98.154.129 | Leader | running | 2 | |
+---------------+----------------+--------+---------+----+-----------+
[root@plpa2ps01srrm ~]#
18. Stworzenie / edycja / wyświetlanie konfiguracji w klastrze:
defakto stworzony to on już jest, bo część z niego jest brana z pliku patroni.yml
patronictl edit-config / patronictl show-config
19. Uruchomienie pozostałych nodów klastra:
jak już odpowiednio wyedytujemy hosty dla replikacji danych to po odpaleniu na 2 nodzie patroni mamy efekt:
[root@plpa2ps02srrm patroni]# patronictl list
+---------------+----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: PLPA2PS00SRRM (7016247082177975663) ------+----+-----------+
| plpa2ps01srrm | 158.98.154.129 | Leader | running | 3 | |
| plpa2ps02srrm | 158.98.154.130 | Replica | running | 3 | 0 |
+---------------+----------------+---------+---------+----+-----------+
[root@plpa2ps02srrm patroni]#
i ostatni
[root@plwa2ps03srrm patroni]# patronictl list
+---------------+----------------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+ Cluster: PLPA2PS00SRRM (7016247082177975663) ------+----+-----------+
| plpa2ps01srrm | 158.98.154.129 | Leader | running | 3 | |
| plpa2ps02srrm | 158.98.154.130 | Replica | running | 3 | 16 |
| plwa2ps03srrm | 158.98.154.131 | Replica | running | 3 | 0 |
+---------------+----------------+---------+---------+----+-----------+
[root@plwa2ps03srrm patroni]#
20. Start usługi keepalived
[root@plpa2ps01srrm ~]# systemctl start keepalived.service
[root@plpa2ps01srrm ~]# systemctl status keepalived.service
● keepalived.service - LVS and VRRP High Availability Monitor
Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2021-10-11 13:02:39 CEST; 5s ago
Process: 3677958 ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 3677959 (keepalived)
Tasks: 2 (limit: 102175)
Memory: 4.4M
CGroup: /system.slice/keepalived.service
├─3677959 /usr/sbin/keepalived -D
└─3677960 /usr/sbin/keepalived -D
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: Registering Kernel netlink command channel
Oct 11 13:02:39 plpa2ps01srrm systemd[1]: Started LVS and VRRP High Availability Monitor.
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: Opening file '/etc/keepalived/keepalived.conf>
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: Assigned address 158.98.154.129 for interface>
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: Registering gratuitous ARP shared channel
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: (VI_1) removing VIPs.
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: (VI_1) Entering BACKUP STATE (init)
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: VRRP sockpool: [ifindex( 2), family(IPv4), p>
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: VRRP_Script(haproxy_check) succeeded
Oct 11 13:02:39 plpa2ps01srrm Keepalived_vrrp[3677960]: (VI_1) Changing effective priority from 100 t>
[root@plpa2ps01srrm ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
link/ether 00:50:56:8e:3a:3f brd ff:ff:ff:ff:ff:ff
inet 158.98.154.129/24 brd 158.98.154.255 scope global noprefixroute ens192
valid_lft forever preferred_lft forever
inet 158.98.154.122/32 scope global ens192
valid_lft forever preferred_lft forever
[root@plpa2ps01srrm ~]#
21. start haproxy
[root@plwa2ps03srrm ~]# systemctl enable haproxy.service && systemctl status haproxy
Created symlink /etc/systemd/system/multi-user.target.wants/haproxy.service → /usr/lib/systemd/system/haproxy.service.
● haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
Active: inactive (dead)
[root@plwa2ps03srrm ~]# systemctl start haproxy
[root@plwa2ps03srrm ~]# systemctl status haproxy.service
● haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2021-10-11 13:15:52 CEST; 54s ago
Process: 3365621 ExecStartPre=/usr/sbin/haproxy -f $CONFIG -c -q $OPTIONS (code=exited, status=0/SU>
Main PID: 3365624 (haproxy)
Tasks: 2 (limit: 102175)
Memory: 14.4M
CGroup: /system.slice/haproxy.service
├─3365624 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
└─3365627 /usr/sbin/haproxy -Ws -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
Oct 11 13:15:52 plwa2ps03srrm systemd[1]: Starting HAProxy Load Balancer...
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy stats started.
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy stats started.
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy master started.
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy master started.
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy replicas started.
Oct 11 13:15:52 plwa2ps03srrm haproxy[3365624]: Proxy replicas started.
Oct 11 13:15:52 plwa2ps03srrm systemd[1]: Started HAProxy Load Balancer.
[root@plwa2ps03srrm ~]#
Reference in New Issue View Git Blame Copy Permalink