1. 架構(gòu)與部署‌

• ‌部署模式‌：

• 單機｜‌主從流復制‌（同步/異步）｜‌Patroni高可用集群‌（自動故障轉(zhuǎn)移）｜‌Citus分布式‌（水平分片）。

• 存儲優(yōu)化：表分區(qū)（Range/List/Hash）｜獨立表空間（隔離熱點數(shù)據(jù)到SSD）。

‌2. 監(jiān)控與調(diào)優(yōu)‌

• ‌關鍵監(jiān)控項‌：

• 連接池爭用（max_connections）、長事務（pg_stat_activity）、鎖等待（pg_locks）。

• ‌慢SQL分析‌：pg_stat_statements + EXPLAIN (ANALYZE, BUFFERS)。

• ‌性能調(diào)優(yōu)‌：

• 索引優(yōu)化：覆蓋索引｜部分索引（WHERE子句）｜監(jiān)控冗余索引（idx_scan=0）。

• 參數(shù)調(diào)優(yōu)：shared_buffers（25%內(nèi)存）、work_mem（按并發(fā)調(diào)整）、autovacuum（防表膨脹）。

‌3. 備份與恢復‌

• ‌物理備份‌：pg_basebackup + ‌WAL歸檔‌（支持PITR任意時間點恢復）。

• ‌邏輯備份‌：pg_dump/pg_dumpall（適合小數(shù)據(jù)量遷移）。

• ‌工具選型‌：pgBackRest（高效壓縮/增量備份）｜Barman（集中化管理）。

‌4. 高可用與容災‌

• ‌主從切換‌：基于流復制的promote命令｜‌Patroni‌（集成etcd/ZooKeeper）。

• ‌容災設計‌：延遲副本（防誤刪）｜邏輯復制（跨版本/跨云同步）。

‌5. 安全與權(quán)限‌

• ‌訪問控制‌：pg_hba.conf限制IP/用戶｜最小權(quán)限原則（避免SUPERUSER濫用）。

• ‌加密‌：SSL傳輸加密｜pgcrypto字段加密｜透明數(shù)據(jù)加密（TDE插件）。

• ‌審計‌：pgAudit記錄操作日志｜log_statement=ddl跟蹤DDL變更。

‌6. 日常維護‌

• ‌Vacuum機制‌：

• 自動清理（autovacuum）調(diào)優(yōu)：降低autovacuum_vacuum_scale_factor應對頻繁更新表。

• 手動清理：VACUUM FULL（謹慎使用，鎖表）｜REINDEX（修復索引膨脹）。

• ‌事務ID防環(huán)繞‌：監(jiān)控txid_age()，緊急時執(zhí)行VACUUM FREEZE。

‌7. 工具鏈與自動化‌

• ‌部署‌：Ansible/Terraform模板化配置。

• ‌版本升級‌：pg_upgrade原地升級｜邏輯復制（跨大版本遷移）。

• ‌Schema管理‌：Flyway/Liquibase實現(xiàn)CI/CD流水線。

‌核心口訣‌

1. ‌監(jiān)控先行‌：Prometheus + Grafana看板，實時跟蹤連接/鎖/事務。

2. ‌備份為王‌：物理備份保底，邏輯備份輔助，定期恢復驗證。

3. ‌索引即性能‌：覆蓋高頻查詢，消滅全表掃描。

4. ‌Vacuum即生命‌：防表膨脹，避事務ID環(huán)繞。

5. ‌安全無小事‌：最小權(quán)限，加密通信，審計留痕。