PostgreSQL klastera problēmu novēršana
Informācija
Visa procedūra jāveic kā root. Lai paaugstinātu savas privilēģijas līdz root, varat izmantot komandu su -.
Savienojuma kļūdām ar CyberElements Bastion PostgreSQL klastera datu bāzi var būt dažādi iemesli. Šajā nodaļā aprakstīta vispārīga problēmu novēršanas metode, kas jāpielāgo jūsu situācijai.
Klastera statusa pārbaude
Informācija
Klastera statusa pārbaude ļauj ātri atrast kļūmi piedzīvojušo mezglu vai mezglus.
Klastera statusu varat pārbaudīt ar šo komandu, kas jāizpilda katrā PostgreSQL klastera mezglā:
| patronictl -c /etc/patroni/config.yml topology
|
Izvades piemērs darbojošā platformā
| + 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 mezgliem aizkave ir 0 MB, un visi mezgli ir sarakstā ar statusu running. Šāda izvade jāiegūst visos mezglos, lai apstiprinātu, ka patroni un etcd darbojas.
Izvades piemērs, ja starp mezgliem ir saziņas problēma
| + 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 |
+------------------+----------------+---------+---------+----+-----------+
|
Ja problēma saglabājas arī pēc vairākām stundām, tas to apstiprina.
Izvades piemērs, ja mezgls nav pareizi startēts vai apturēts
| + 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) norāda, ka mezgls 1 tiek startēts. Ja statuss started saglabājas vairākas minūtes vai pat stundas, iespējams, šajā mezglā ir problēma ar patroni vai etcd.
Mezgls 2 ir stāvoklī stopped: tas ir apturēts. Ja klastera mezglam rodas kļūme, šī informācija tiek rādīta tikai īslaicīgi, pirms mezgls tiek noņemts no saraksta.
Izvades piemērs, ja pašreizējā mezglā rodas etcd kļūme
| 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, kas neļauj izgūt PostgreSQL klastera statusu.
Izvades piemērs, ja kāds no mezgliem nav sinhronizēts
| + 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 vairs nav tajā pašā TL kā pārējie. Lai klasteris darbotos, visiem mezgliem jābūt tajā pašā TL: atšķirība norāda, ka kāds no mezgliem nav sinhronizēts.
Lai novērstu šo problēmu, izpildiet mezgla desinhronizācijas procedūru.
Pakalpojumu statusa pārbaude
Kad kļūmi piedzīvojušais mezgls ir identificēts, ir jāidentificē nepareizi strādājošais pakalpojums. Divi galvenie PostgreSQL klasterī izmantotie pakalpojumi ir patroni un etcd.
Informācija
Pakalpojums patroni ir atkarīgs no pakalpojuma etcd: ja etcd nedarbojas, nedarbosies arī patroni.
Lai identificētu kļūdaino pakalpojumu, varat izpildīt šīs komandas:
| systemctl status etcd
systemctl status patroni
|
Šīs komandas parāda pakalpojuma statusu, kā arī tā pēdējās 10 žurnāla rindas.
Brīdinājums
Pakalpojums ar statusu active (running) ne vienmēr darbojas pareizi: tas var darboties, vienlaikus ierakstot kļūdu žurnālus. Tāpēc ir svarīgi izlasīt pakalpojuma žurnālus, lai izprastu tā faktisko stāvokli.
Izvades piemērs pakalpojumam etcd, ja tas darbojas
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)
|
Izvades piemērs pakalpojumam etcd, ja tajā ir kļūda
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 ir statuss failed, un kļūda, kas neļāva tam startēt, ir 17. rindā: pakalpojums nevar nolasīt direktorija /var/lib/etcd/cleanroom saturu. Šādā gadījumā pielāgojiet direktorija atļaujas.
Izvades piemērs pakalpojumam patroni, ja tas darbojas
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)
|
Izvades piemērs pakalpojumam patroni, ja tajā ir kļūda
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), lai gan tas faktiski nedarbojas: žurnālos redzamas dažādas savienojuma kļūdas ar etcd, kas ir nepieciešams patroni.
Šajā piemērā vaina ne vienmēr ir patroni: tā kā tas ir atkarīgs no etcd, vispirms novērsiet etcd problēmas un pēc tam vēlreiz pārbaudiet patroni statusu.
Pakalpojumu problēmu novēršanas procedūras
Tālāk ir sniegta viena problēmu novēršanas procedūra katram pakalpojumam. Ja nepareizi darbojas gan etcd, gan patroni, sāciet ar etcd.