Soluzzjoni tal-problemi tal-cluster PostgreSQL
Informazzjoni
Il-proċedura kollha għandha titwettaq bħala root. Biex tgħolli l-privileġġi tiegħek għal root, tista’ tuża l-kmand su -.
L-iżbalji tal-konnessjoni mad-database tal-cluster PostgreSQL ta’ CyberElements Bastion jista’ jkollhom diversi kawżi. Dan il-kapitolu jippreżenta metodu ġenerali għas-soluzzjoni tal-problemi, li għandu jiġi adattat għall-każ tiegħek.
Verifika tal-istatus tal-cluster
Informazzjoni
Il-verifika tal-istatus tal-cluster tgħin biex jinstabu malajr in-nodu jew in-nodi li qed ifallu.
Tista’ tivverifika l-istatus tal-cluster bil-kmand li ġej, li għandu jitħaddem fuq kull nodu tal-cluster PostgreSQL:
| patronictl -c /etc/patroni/config.yml topology
|
Eżempju ta’ riżultat fuq pjattaforma li taħdem
| + Cluster: 15-cleanroomvault5 ------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+------------------+----------------+---------+---------+----+-----------+
| PSQL_3 | psql_3 | Leader | running | 2 | |
| + PSQL_1 | psql_1 | Replica | running | 2 | 0 |
| + PSQL_2 | psql_2 | Replica | running | 2 | 0 |
+------------------+----------------+---------+---------+----+-----------+
|
Replica kollha, u n-nodi kollha jinsabu fil-lista, bl-istatus running. Trid tikseb dan ir-riżultat fuq in-nodi kollha biex tikkonferma li patroni u etcd qed jaħdmu.
Eżempju ta’ riżultat meta jkun hemm problema ta’ komunikazzjoni bejn in-nodi
| + Cluster: 15-cleanroomvault5 ------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+------------------+----------------+---------+---------+----+-----------+
| PSQL_3 | psql_3 | Leader | running | 2 | |
| + PSQL_1 | psql_1 | Replica | running | 2 | 230 |
| + PSQL_2 | psql_2 | Replica | running | 2 | 0 |
+------------------+----------------+---------+---------+----+-----------+
|
Jekk il-problema tippersisti wara diversi sigħat, dan jikkonfermaha.
Eżempju ta’ riżultat meta nodu ma jkunx inbeda jew twaqqaf sew
| + Cluster: 15-cleanroomvault5 ------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+------------------+----------------+---------+---------+----+-----------+
| PSQL_3 | psql_3 | Leader | running | 2 | |
| + PSQL_1 | psql_1 | Replica | started | 2 | unknown |
| + PSQL_2 | psql_2 | Replica | stopped | 2 | unknown |
+------------------+----------------+---------+---------+----+-----------+
|
State) jindika li n-nodu 1 qed jibda. Jekk l-istatus started jippersisti għal diversi minuti, jew saħansitra sigħat, jista’ jkun hemm problema fis-servizz patroni jew etcd fuq dak in-nodu.
In-nodu 2 jinsab fl-istat stopped: huwa mwaqqaf. Meta nodu tal-cluster ifalli, din l-informazzjoni tintwera biss b’mod temporanju, qabel ma n-nodu jitneħħa mil-lista.
Eżempju ta’ riżultat meta etcd ifalli fuq in-nodu attwali
| 2026-04-09 16:21:39,482 - WARNING - Retrying (Retry(total=1, connect=None, read=None, redirect=0, status=None)) after connection broken by 'NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fcaa1618390>: Failed to establish a new connection: [Errno 111] Connection refused')': /version
2026-04-09 16:21:39,482 - WARNING - Retrying (Retry(total=0, connect=None, read=None, redirect=0, status=None)) after connection broken by 'NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fcaa1618c10>: Failed to establish a new connection: [Errno 111] Connection refused')': /version
2026-04-09 16:21:39,483 - ERROR - Failed to get list of machines from https://PSQL_3:2379/v2: MaxRetryError("HTTPSConnectionPool(host='psql_3', port=2379): Max retries exceeded with url: /version (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fcaa1619490>: Failed to establish a new connection: [Errno 111] Connection refused'))")
|
etcd, li ma jħallix jinkiseb l-istatus tal-cluster PostgreSQL.
Eżempju ta’ riżultat meta wieħed min-nodi mhuwiex sinkronizzat
| + Cluster: 15-cleanroomvault5 ------+---------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+------------------+----------------+---------+---------+----+-----------+
| PSQL_3 | psql_3 | Leader | running | 3 | |
| + PSQL_1 | psql_1 | Replica | running | 2 | 230 |
| + PSQL_2 | psql_2 | Replica | running | 3 | 0 |
+------------------+----------------+---------+---------+----+-----------+
|
PSQL_1 m’għadux fuq l-istess TL bħall-oħrajn. Biex il-cluster jaħdem, in-nodi kollha għandhom ikunu fuq l-istess TL: differenza tindika li wieħed min-nodi mhuwiex sinkronizzat.
Biex issolvi din il-problema, segwi l-proċedura ta’ diżinkronizzazzjoni ta’ nodu.
Verifika tal-istatus tas-servizzi
Ladarba jiġi identifikat in-nodu li qed ifalli, trid tidentifika s-servizz li mhux qed jaħdem sew. Iż-żewġ servizzi ewlenin użati fuq il-cluster PostgreSQL huma patroni u etcd.
Informazzjoni
Is-servizz patroni jiddependi fuq is-servizz etcd: jekk etcd ma jkunx qed jaħdem, lanqas patroni ma jaħdem.
Biex tidentifika s-servizz li qed ifalli, tista’ tħaddem il-kmandi li ġejjin:
| systemctl status etcd
systemctl status patroni
|
Dawn il-kmandi jagħtu l-istatus tas-servizz, flimkien mal-aħħar 10 linji tar-reġistru tiegħu.
Twissija
Servizz bl-istatus active (running) mhux neċessarjament qed jaħdem: jista’ jkun qed jitħaddem filwaqt li jikteb reġistri ta’ żbalji. Għalhekk huwa importanti li taqra r-reġistri tas-servizz biex tifhem l-istat reali tiegħu.
Eżempju ta’ riżultat għal etcd meta s-servizz ikun qed jaħdem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | ● etcd.service - etcd - highly-available key value store
Loaded: loaded (/lib/systemd/system/etcd.service; enabled; preset: enabled)
Active: active (running) since Thu 2026-04-09 16:40:01 CEST; 21h ago
Docs: https://etcd.io/docs
man:etcd
Main PID: 283425 (etcd)
Tasks: 7 (limit: 2227)
Memory: 73.2M
CPU: 51min 9.223s
CGroup: /system.slice/etcd.service
└─283425 /usr/bin/etcd
Apr 10 09:26:31 PSQL_1 etcd[283425]: store.index: compact 1624026
Apr 10 09:26:31 PSQL_1 etcd[283425]: finished scheduled compaction at 1624026 (took 325.098µs)
Apr 10 10:26:31 PSQL_1 etcd[283425]: store.index: compact 1624386
Apr 10 10:26:31 PSQL_1 etcd[283425]: finished scheduled compaction at 1624386 (took 417.797µs)
Apr 10 11:26:31 PSQL_1 etcd[283425]: store.index: compact 1624746
Apr 10 11:26:31 PSQL_1 etcd[283425]: finished scheduled compaction at 1624746 (took 1.706489ms)
Apr 10 12:26:31 PSQL_1 etcd[283425]: store.index: compact 1625106
Apr 10 12:26:31 PSQL_1 etcd[283425]: finished scheduled compaction at 1625106 (took 300.098µs)
Apr 10 13:26:31 PSQL_1 etcd[283425]: store.index: compact 1625466
Apr 10 13:26:31 PSQL_1 etcd[283425]: finished scheduled compaction at 1625466 (took 311.398µs)
|
Eżempju ta’ riżultat għal etcd meta s-servizz ikun fi żball
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | × etcd.service - etcd - highly-available key value store
Loaded: loaded (/lib/systemd/system/etcd.service; enabled; preset: enabled)
Active: failed (Result: exit-code) since Fri 2026-04-10 14:15:26 CEST; 5s ago
Duration: 21h 35min 20.382s
Docs: https://etcd.io/docs
man:etcd
Process: 454701 ExecStart=/usr/bin/etcd $DAEMON_ARGS (code=exited, status=1/FAILURE)
Main PID: 454701 (code=exited, status=1/FAILURE)
CPU: 12ms
Apr 10 14:15:26 PSQL_1 etcd[454701]: [WARNING] Deprecated '--logger=capnslog' flag is set; use '--logger=zap' flag instead
Apr 10 14:15:26 PSQL_1 etcd[454701]: etcd Version: 3.4.23
Apr 10 14:15:26 PSQL_1 etcd[454701]: Git SHA: Not provided (use ./build instead of go build)
Apr 10 14:15:26 PSQL_1 etcd[454701]: Go Version: go1.19.8
Apr 10 14:15:26 PSQL_1 etcd[454701]: Go OS/Arch: linux/amd64
Apr 10 14:15:26 PSQL_1 etcd[454701]: setting maximum number of CPUs to 1, total number of available CPUs is 1
Apr 10 14:15:26 PSQL_1 etcd[454701]: error listing data dir: /var/lib/etcd/cleanroom
Apr 10 14:15:26 PSQL_1 systemd[1]: etcd.service: Main process exited, code=exited, status=1/FAILURE
Apr 10 14:15:26 PSQL_1 systemd[1]: etcd.service: Failed with result 'exit-code'.
Apr 10 14:15:26 PSQL_1 systemd[1]: Failed to start etcd.service - etcd - highly-available key value store.
|
etcd għandu l-istatus failed, u l-iżball li ma ħallihx jibda huwa dak fil-linja 17: is-servizz ma jistax jaqra l-kontenut tad-direttorju /var/lib/etcd/cleanroom. F’dan il-każ, irranġa l-permessi fuq id-direttorju.
Eżempju ta’ riżultat għal patroni meta s-servizz ikun qed jaħdem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | ● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/lib/systemd/system/patroni.service; enabled; preset: enabled)
Drop-In: /etc/systemd/system/patroni.service.d
└─local.conf
Active: active (running) since Thu 2026-04-09 16:40:06 CEST; 22h ago
Process: 283510 ExecStartPre=/usr/bin/testetcd.py (code=exited, status=0/SUCCESS)
Main PID: 283517 (patroni)
Tasks: 13 (limit: 2227)
Memory: 56.1M
CPU: 26min 54.918s
CGroup: /system.slice/patroni.service
├─283517 /usr/bin/python3 /usr/bin/patroni /etc/patroni/config.yml
├─283537 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/cleanroomvault5 --config-file=/etc/postgresql/15/cleanroomvault5/postgresql.conf"--listen_addresses=*" --port=5432 --cluster_name=15-cleanroomvault5 --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10--max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on
├─283538 "postgres: 15-cleanroomvault5: logger "
├─283540 "postgres: 15-cleanroomvault5: checkpointer "
├─283541 "postgres: 15-cleanroomvault5: background writer "
├─283542 "postgres: 15-cleanroomvault5: startup recovering 00000003000000000000000F"
├─283545 "postgres: 15-cleanroomvault5: walreceiver "
└─283547 "postgres: 15-cleanroomvault5: postgres postgres [local] idle"
Apr 10 14:51:07 PSQL_1 patroni[283517]: 2026-04-10 14:51:07,381 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:51:17 PSQL_1 patroni[283517]: 2026-04-10 14:51:17,428 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:51:27 PSQL_1 patroni[283517]: 2026-04-10 14:51:27,381 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:51:37 PSQL_1 patroni[283517]: 2026-04-10 14:51:37,428 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:51:47 PSQL_1 patroni[283517]: 2026-04-10 14:51:47,381 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:51:57 PSQL_1 patroni[283517]: 2026-04-10 14:51:57,428 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:52:07 PSQL_1 patroni[283517]: 2026-04-10 14:52:07,381 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:52:17 PSQL_1 patroni[283517]: 2026-04-10 14:52:17,428 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:52:27 PSQL_1 patroni[283517]: 2026-04-10 14:52:27,381 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
Apr 10 14:52:37 PSQL_1 patroni[283517]: 2026-04-10 14:52:37,474 INFO: no action. I am (PSQL_1), a secondary, and following a leader (PSQL_2)
|
Eżempju ta’ riżultat għal patroni meta s-servizz ikun fi żball
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | ● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/lib/systemd/system/patroni.service; enabled; preset: enabled)
Drop-In: /etc/systemd/system/patroni.service.d
└─local.conf
Active: active (running) since Thu 2026-04-09 16:40:06 CEST; 23h ago
Process: 283510 ExecStartPre=/usr/bin/testetcd.py (code=exited, status=0/SUCCESS)
Main PID: 283517 (patroni)
Tasks: 12 (limit: 2227)
Memory: 61.7M
CPU: 28min 8.337s
CGroup: /system.slice/patroni.service
├─283517 /usr/bin/python3 /usr/bin/patroni /etc/patroni/config.yml
├─283537 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/cleanroomvault5 --config-file=/etc/postgresql/15/cleanroomvault5/postgresql.conf "--listen_addresses=*" --port=5432 --cluster_name=15-cleanroomvault5 --wal_level=replica --hot_standby=on --max_connections=100 --max_wal_senders=10 --max_prepared_transactions=0 --max_locks_per_transaction=64 --track_commit_timestamp=off --max_replication_slots=10 --max_worker_processes=8 --wal_log_hints=on
├─283538 "postgres: 15-cleanroomvault5: logger "
├─283540 "postgres: 15-cleanroomvault5: checkpointer "
├─283541 "postgres: 15-cleanroomvault5: background writer "
├─283542 "postgres: 15-cleanroomvault5: startup recovering 00000003000000000000000F"
└─283547 "postgres: 15-cleanroomvault5: postgres postgres [local] idle"
Apr 10 15:52:37 PSQL_1 patroni[283517]: 2026-04-10 15:52:37,680 WARNING: Loop time exceeded, rescheduling immediately.
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:37,681 INFO: Lock owner: PSQL_2; I am PSQL_1
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,069 ERROR: Request to server https://PSQL_3:2379 failed: ReadTimeoutError("HTTPSConnectionPool(host='psql_3', port=2379): Read timed out. (read timeout=3.3331721344341836)")
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,069 INFO: Reconnection allowed, looking for another server.
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,069 INFO: Retrying on https://PSQL_1:2379
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,073 ERROR: Request to server https://PSQL_1:2379 failed: MaxRetryError("HTTPSConnectionPool(host='psql_1', port=2379): Max retries exceeded with url: /v3/lease/keepalive (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fd77c411090>: Failed to establish a new connection: [Errno 111] Connection refused'))")
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,073 INFO: Reconnection allowed, looking for another server.
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,073 INFO: Retrying on https://PSQL_2:2379
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,074 ERROR: Request to server https://PSQL_2:2379 failed: MaxRetryError("HTTPSConnectionPool(host='psql_2', port=2379): Max retries exceeded with url: /v3/lease/keepalive (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0x7fd77c411090>: Failed to establish a new connection: [Errno 111] Connection refused'))")
Apr 10 15:52:41 PSQL_1 patroni[283517]: 2026-04-10 15:52:41,074 INFO: Reconnection allowed, looking for another server.
|
active (running) mingħajr ma fil-fatt qed jaħdem: ir-reġistri juru diversi żbalji tal-konnessjoni ma’ etcd, li patroni għandu bżonnu.
F’dan l-eżempju, patroni mhux neċessarjament il-ħati: peress li jiddependi fuq etcd, l-ewwel irranġa l-problemi ta’ etcd, imbagħad ivverifika mill-ġdid l-istatus ta’ patroni.
Proċeduri għas-soluzzjoni tal-problemi tas-servizzi
Hawn taħt hemm proċedura waħda ta’ soluzzjoni tal-problemi għal kull servizz. Jekk kemm etcd kif ukoll patroni mhumiex qed jaħdmu sew, ibda minn etcd.