目標____HTMLTAG_66__HTMLTAG_67___完成本課程後，您將：____HTMLTAG_68__HTMLTAG_69__HTMLTAG_70___最佳化 HA 叢集的 Postgrecer 配置_HTMLTAG_71__HTMLTAG_72___設定連線池___HTMLTAG_73__HTMLTAG_74___使用下列指令實現負載平衡HAProxy___HTMLTAG_75__HTMLTAG_76___使用多個副本擴充讀取____HTMLTAG_7 7__HTMLTAG_78___調整查詢和索引____HTMLTAG_79__HTMLTAG_80___監視和解決效能問題___HTMLTAG_81__HTMLTAG_82__HTMLTAG_83___1。 PostgreSQL 設定調整___HTMLTAG_84__HTMLTAG_85___1.1。記憶體設定___HTMLTAG_86__HTMLTAG_87___shared_buffers___HTMLTAG_88__CODEBLOCK_0__HTMLTAG_89 effective_cache_size___HTMLTAG_90__CODEBLOCK _1__HTMLTAG_91___work_mem___HTMLTAG_92__CODEBLOCK_2__HTMLTAG_93___maintenance_work_mem___HTMLTAG_94__CODEBLOCK_3__HTMLTAG_95___1.2。檢查點調整___HTMLTAG_96__CODEBLOCK_4__HTMLTAG_97___1.3。 WAL 設定___HTMLTAG_98__CODEBLOCK_5__HTMLTAG_99___1.4。查詢規劃器___HTMLTAG_100__CODEBLOCK_6__HTMLTAG_101___1.5。連線設定___HTMLTAG_102__CODEBLOCK_7__HTMLTAG_103___1.6。自動真空調整___HTMLTAG_104__CODEBLOCK_8__HTMLTAG_105___1.7。記錄效能___HTMLTAG_106__CODEBLOCK_9__HTMLTAG_107___1.8。應用配置
-- Reload configuration (no restart needed for most) SELECT pg_reload_conf();-- Check what requires restart: SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart = true;
-- Restart if needed:
sudo systemctl restart patroni
2。使用 PgBouncer 的連線池___HTMLTAG_110__HTMLTAG_111___2.1。為什麼要使用連線池？ ___HTMLTAG_112__HTMLTAG_113__HTMLTAG_114___沒有池的問題：___HTMLTAG_116__CODEBLOCK_12__HTML TAG_117__HTMLTAG_118___解決方案PgBouncer：___HTMLTAG_120__CODEBLOCK_13__HTMLTAG_121___2.2。安裝 PgBouncer____HTMLTAG_122__CODEBLOCK_14__HTMLTAG_123___2.3。設定 PgBouncer____HTMLTAG_124__CODEBLOCK_15__HTMLTAG_125__HTMLTAG_126___池模式說明：___HTMLTAG_128__CODEBLOCK_16__HTMLTAG_129___2.4。使用者驗證___HTMLTAG_130__CODEBLOCK_17__HTMLTAG_131___2.5。啟動 PgBouncer___HTMLTAG_132__CODEBLOCK_18__HTMLTAG_133___2.6。測試連接___HTMLTAG_134__CODEBLOCK_19__HTMLTAG_135___2.7。應用程式配置___HTMLTAG_136__CODEBLOCK_20__HTMLTAG_137___2.8。監控 PgBouncer___HTMLTAG_138__CODEBLOCK_21__HTMLTAG_139___3。使用 HAProxy 進行負載平衡___HTMLTAG_140__HTMLTAG_141___3.1。 HAProxy 架構___HTMLTAG_142__CODEBLOCK_22__HTMLTAG_143___3.2.安裝 HAProxy
___CODEBLOCK_23__HTMLTAG_145___3.3。設定HAProxy___HTMLTAG_147__HTMLTAG_148___設定說明：___HTMLTAG_150__CODEBLOCK_25__HTMLTAG_151___3.4。用於執行狀況檢查的 Patroni REST API 端點____HTMLTAG_152__CODEBLOCK_26__HTMLTAG_153___3.5。啟動 HAProxy___HTMLTAG_154__CODEBLOCK_27__HTMLTAG_155___3.6。測試負載平衡___HTMLTAG_156__CODEBLOCK_28__HTMLTAG_157___3.7。應用程式使用___HTMLTAG_158__CODEBLOCK_29__HTMLTAG_159___3.8。監控 HAProxy___HTMLTAG_160__CODEBLOCK_30__HTMLTAG_161___4。閱讀擴充策略___HTMLTAG_162__HTMLTAG_163___4.1。新增更多唯讀副本# /etc/haproxy/haproxy.cfg sudo tee /etc/haproxy/haproxy.cfg <<'EOF' global log /dev/log local0 log /dev/log local1 notice chroot /var/lib/haproxy stats socket /run/haproxy/admin.sock mode 660 level admin stats timeout 30s user haproxy group haproxy daemondefaults log global mode tcp option tcplog option dontlognull timeout connect 5000 timeout client 50000 timeout server 50000
Stats page
listen stats mode http bind *:7000 stats enable stats uri / stats refresh 10s stats admin if TRUE
Frontend for write (primary)
frontend postgres_write bind *:5000 mode tcp default_backend postgres_primary
Backend for primary (writes)
backend postgres_primary mode tcp option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server node1 10.0.1.11:5432 check port 8008 check-ssl verify none server node2 10.0.1.12:5432 check port 8008 check-ssl verify none backup server node3 10.0.1.13:5432 check port 8008 check-ssl verify none backup
Frontend for read (replicas)
frontend postgres_read bind *:5001 mode tcp default_backend postgres_replicas
Backend for replicas (reads)
backend postgres_replicas mode tcp balance roundrobin option httpchk http-check expect status 200 http-check send meth GET uri /replica default-server inter 3s fall 3 rise 2 server node2 10.0.1.12:5432 check port 8008 check-ssl verify none server node3 10.0.1.13:5432 check port 8008 check-ssl verify none server node1 10.0.1.11:5432 check port 8008 check-ssl verify none backup EOF
# Add 4th node as read replicaOn node4:
Install PostgreSQL + Patroni (same as before)
Configure patroni.yml with tags:
tags: nofailover: true # Don't promote to primary noloadbalance: false # Include in load balancing priority: 0 # Lowest priority
Start Patroni
sudo systemctl start patroni
Verify joined cluster
patronictl list postgres
Before (3 nodes): Write: 100% → Primary Read: 50% → Replica1, 50% → Replica2
After (4 nodes): Write: 100% → Primary Read: 33% → Replica1, 33% → Replica2, 33% → Replica3 ✅
4.2。級聯複製
# For geographically distributed replicasnode4 (remote datacenter) replicates from node2 instead of primary
In node4's patroni.yml:
bootstrap: dcs: postgresql: parameters: primary_conninfo: 'host=node2 port=5432 user=replicator...'
Topology:
Primary (node1)
↓
├─→ Replica (node2)
│ ↓
│ └─→ Replica (node4 - cascading) ← Reduces load on primary
└─→ Replica (node3)
4.3。應用離子級讀取路由___HTMLTAG_168__CODEBLOCK_35__HTMLTAG_169___4.4。監控讀取分佈___HTMLTAG_170__CODEBLOCK_36__HTMLTAG_171___5。查詢最佳化___HTMLTAG_172__HTMLTAG_173___5.1。啟用 pg_stat_statements_
-- Add to postgresql.conf ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- Restart required
sudo systemctl restart patroni
-- Create extension CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- View top queries by time SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;