Keri sisuni

PostgreSQL-i klastri tõrkeotsing

Teave

Kogu protseduur tuleb läbi viia kasutajana root. Oma privileegide tõstmiseks root-i tasemele saate kasutada käsku su -.

Hoiatus

Selle protseduuri järgimiseks peate olema teinud patronictl-i lisakonfiguratsioonid.

CyberElements Bastion lahenduse PostgreSQL-i klastri andmebaasiga ühenduse loomise tõrgetel võib olla mitmesuguseid põhjusi. Selles peatükis esitatakse üldine tõrkeotsingu meetod, mida tuleb kohandada teie olukorrale.

Klastri oleku kontrollimine

Teave

Klastri oleku kontrollimine aitab kiiresti leida tõrkuva sõlme või sõlmed.

Klastri olekut saate kontrollida järgmise käsuga, mis tuleb käivitada PostgreSQL-i klastri igas sõlmes:

1
patronictl -c /etc/patroni/config.yml topology
Väljundi näide toimival platvormil 

1
2
3
4
5
6
7
+ 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 |
+------------------+----------------+---------+---------+----+-----------+
Ülaltoodud väljundis viitab kõik sellele, et PostgreSQL-i klaster töötab: mahajäämus on kõigi Replica-sõlmede puhul 0 MB ja kõik sõlmed on loendis olekuga running. Selle väljundi peate saama kõigis sõlmedes, et kinnitada, et patroni ja etcd töötavad.

Väljundi näide, kui sõlmede vahel on sideprobleem 

1
2
3
4
5
6
7
+ 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 |
+------------------+----------------+---------+---------+----+-----------+
Ülaltoodud väljundis on 230 MB mahajäämus ebanormaalne: see võib viidata sideprobleemile sõlme 3 ja sõlme 1 vahel.
Kui probleem püsib ka mitme tunni pärast, kinnitab see seda.

Väljundi näide, kui sõlm pole korralikult käivitatud või peatatud 

1
2
3
4
5
6
7
+ 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 |
+------------------+----------------+---------+---------+----+-----------+
Ülaltoodud väljundis näitab olek (veerg State), et sõlm 1 on käivitumas. Kui olek started püsib mitu minutit või isegi tunde, võib selle sõlme teenusega patroni või etcd olla probleem.
Sõlm 2 on olekus stopped: see on peatatud. Kui klastri sõlm tõrgub, kuvatakse seda teavet vaid ajutiselt, enne kui sõlm loendist eemaldatakse.

Väljundi näide, kui etcd tõrgub praeguses sõlmes 

1
2
3
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'))")
Ülaltoodud väljund näitab ühendusetõrget teenusega etcd, mis takistab PostgreSQL-i klastri oleku hankimist.

Väljundi näide, kui üks sõlmedest on sünkroonist väljas 

1
2
3
4
5
6
7
+ 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 |
+------------------+----------------+---------+---------+----+-----------+
Ülaltoodud väljundis pole sõlm PSQL_1 enam samal TL-ajateljel kui teised. Klastri toimimiseks peavad kõik sõlmed olema samal TL-ajateljel: erinevus näitab, et üks sõlmedest on sünkroonist väljas.
Selle probleemi lahendamiseks järgige sõlme sünkroonist väljumise protseduuri.

Teenuste oleku kontrollimine

Kui tõrkuv sõlm on tuvastatud, peate tuvastama valesti töötava teenuse. PostgreSQL-i klastris kasutatavad kaks peamist teenust on patroni ja etcd.

Teave

Teenus patroni sõltub teenusest etcd: kui etcd ei tööta, ei tööta ka patroni.

Tõrkuva teenuse tuvastamiseks saate käivitada järgmised käsud:

1
2
systemctl status etcd
systemctl status patroni

Need käsud näitavad teenuse olekut koos selle viimase 10 logireaga.

Hoiatus

Teenus olekuga active (running) ei pruugi tegelikult töötada: see võib olla käimas ja samal ajal tõrkelogisid kirjutada. Seetõttu on oluline lugeda teenuse logisid, et mõista selle tegelikku olekut.

Teenuse etcd väljundi näide, kui teenus töötab 
 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)
Teenuse etcd väljundi näide, kui teenuses on tõrge 

 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.
Ülaltoodud väljundis on teenuse etcd olek failed ja selle käivitumist takistanud tõrge on real 17: teenus ei saa lugeda kataloogi /var/lib/etcd/cleanroom sisu. Sel juhul kohandage kataloogi lubasid.

Teenuse patroni väljundi näide, kui teenus töötab 
 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)
Teenuse patroni väljundi näide, kui teenuses on tõrge 

 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.
Ülaltoodud juhul on teenuse olek active (running), kuigi see tegelikult ei tööta: logid näitavad mitmesuguseid ühendusetõrkeid teenusega etcd, mida patroni vajab.
Selles näites ei pruugi süü olla teenusel patroni: kuna see sõltub teenusest etcd, parandage esmalt teenuse etcd probleemid ja seejärel kontrollige uuesti teenuse patroni olekut.

Teenuste tõrkeotsingu protseduurid

Allpool on iga teenuse jaoks üks tõrkeotsingu protseduur. Kui nii etcd kui ka patroni töötavad valesti, alustage teenusest etcd.