架構概覽
flowchart LR Client[應用服務] -->|連線請求| PgBouncer[PgBouncer<br/>連線池 :6432] PgBouncer -->|受控連線| PG[PostgreSQL<br/>:5432] PG --> WAL[WAL<br/>Write-Ahead Log] WAL --> DataDir[(Data Volume<br/>資料儲存)] PG --> Backup[pg_dump<br/>每日備份] Backup --> MinIO[MinIO / NAS<br/>備份歸檔]
Database：PostgreSQL 的穩健基線
資料庫是所有應用服務的核心依賴。如果 Nginx 掛了，服務暫時不可用但資料還在；如果資料庫的資料丟了，那才是真正的災難。這篇文章建立 PostgreSQL 的單節點部署基線，涵蓋安裝設定、連線池、備份還原、效能調校、以及升級策略。目標是在一台 Host 上建立一個穩健的 PostgreSQL，能應付大部分中小型應用的需求。
架構概覽
flowchart TD App[Application Services] -->|connection| PgBouncer[PgBouncer\n連線池 :6432] PgBouncer --> PG[PostgreSQL\n:5432] PG --> Data[(Data Volume\n/var/lib/postgresql/data)] Backup[Backup Script\ncron daily] -->|pg_dump| PG Backup -->|upload| MinIO[MinIO / NAS\n備份儲存] Monitor[Prometheus\npg_exporter] -->|metrics| PG Admin[DBA / Admin] -->|psql / pgAdmin| PG
應用服務透過 PgBouncer 連線池連到 PostgreSQL，減少直接連線數。備份腳本每天執行 pg_dump 並上傳到 MinIO。Prometheus 透過 pg_exporter 收集效能指標。
核心概念
-
連線池（PgBouncer）：PostgreSQL 的每個連線會 fork 一個 process，佔用約 10MB 記憶體。如果 10 個微服務各開 20 個連線，就是 200 個 process、2GB 記憶體。PgBouncer 作為中間層，維持少量的實際連線（例如 50 個），把應用端的大量連線請求排隊分配，大幅降低 PostgreSQL 的資源消耗。設定
pool_mode = transaction是最常用的模式，每個 transaction 結束後連線就釋放回 pool。 -
備份策略：備份分兩種：邏輯備份（
pg_dump）和物理備份（pg_basebackup+ WAL archiving）。pg_dump產出 SQL 或 custom format 的備份檔，優點是可以選擇性還原特定 table，缺點是大資料庫 dump 很慢。pg_basebackup是整個資料目錄的複製，搭配 WAL archiving 可以做到 Point-in-Time Recovery（PITR），適合需要精確回復到某個時間點的場景。小型到中型資料庫（<50GB）用pg_dump足矣；超過 50GB 建議搭配pg_basebackup。 -
postgresql.conf 調校：PostgreSQL 的預設設定非常保守（假設機器只有 128MB RAM），需要根據實際硬體調整。關鍵參數：
shared_buffers（建議設為 RAM 的 25%）、work_mem（排序和 hash 操作的記憶體，建議 64-256MB）、effective_cache_size（告訴 query planner 有多少快取可用，建議 RAM 的 75%）、wal_buffers（WAL 寫入緩衝，建議 64MB）。不要把所有 RAM 都給 PostgreSQL，要留給 OS cache 和其他服務。 -
升級策略：PostgreSQL 的 minor version 升級（例如 15.4 → 15.5）只需要重啟，不需要動資料。Major version 升級（例如 15 → 16）需要用
pg_upgrade或 dump/restore。建議在 staging 環境先執行升級、驗證應用相容性後再到 production 執行。升級前一定要做完整備份。
使用情境
-
標準部署：一台 8GB RAM 的 Host 上部署 PostgreSQL，搭配 PgBouncer。API 服務和 Worker 服務透過 PgBouncer 連線，實際連線數控制在 50 個以內。每天凌晨 3 點
pg_dump備份，上傳到 MinIO，保留 30 天。 -
備份還原演練：每季做一次備份還原演練（Disaster Recovery Drill）。從 MinIO 下載最近的備份檔，在一台空白的 Host 上還原，驗證資料完整性。如果還原失敗，代表備份流程有問題，必須立即修復。不做演練的備份等於沒有備份。
-
效能排查：應用回應變慢，懷疑是資料庫瓶頸。用
pg_stat_statements找出最慢的查詢、用EXPLAIN ANALYZE分析執行計畫、用 Prometheus 看連線數和 cache hit ratio 是否異常。
實作範例 / 設定範例
PostgreSQL + PgBouncer 部署（docker-compose）
version: "3.8"
services:
postgres:
image: postgres:16-alpine
restart: unless-stopped
environment:
POSTGRES_DB: myapp
POSTGRES_USER: myapp
POSTGRES_PASSWORD: ${DB_PASSWORD}
volumes:
- pg-data:/var/lib/postgresql/data
- ./postgresql.conf:/etc/postgresql/postgresql.conf
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
command: postgres -c config_file=/etc/postgresql/postgresql.conf
ports:
- "127.0.0.1:5432:5432" # 只允許 localhost 連線
healthcheck:
test: ["CMD-SHELL", "pg_isready -U myapp"]
interval: 10s
timeout: 5s
retries: 5
pgbouncer:
image: edoburu/pgbouncer:latest
restart: unless-stopped
environment:
DATABASE_URL: postgresql://myapp:${DB_PASSWORD}@postgres:5432/myapp
POOL_MODE: transaction
MAX_CLIENT_CONN: 200
DEFAULT_POOL_SIZE: 50
MIN_POOL_SIZE: 10
ports:
- "127.0.0.1:6432:6432"
depends_on:
postgres:
condition: service_healthy
volumes:
pg-data:postgresql.conf 調校（8GB RAM Host）
# postgresql.conf - 8GB RAM 基線
# 連線
max_connections = 100
listen_addresses = '*'
# 記憶體
shared_buffers = 2GB # RAM 的 25%
work_mem = 128MB # 排序/hash 操作
maintenance_work_mem = 512MB # VACUUM/CREATE INDEX
effective_cache_size = 6GB # RAM 的 75%
wal_buffers = 64MB
# WAL
wal_level = replica # 支援未來做 replication
max_wal_size = 2GB
min_wal_size = 1GB
# Query Planner
random_page_cost = 1.1 # SSD 環境
effective_io_concurrency = 200 # SSD 環境
# 日誌
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000 # 記錄超過 1 秒的查詢
# 統計
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60備份腳本
#!/bin/bash
# scripts/backup-postgresql.sh
set -euo pipefail
DB_HOST=${DB_HOST:-localhost}
DB_NAME=${DB_NAME:-myapp}
DB_USER=${DB_USER:-myapp}
BACKUP_DIR=/tmp/pg-backups
DATE=$(date +%Y-%m-%d_%H%M)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
mkdir -p "$BACKUP_DIR"
echo "=== Starting backup: $DB_NAME ==="
# pg_dump + gzip 壓縮
PGPASSWORD="$DB_PASSWORD" pg_dump \
-h "$DB_HOST" \
-U "$DB_USER" \
-d "$DB_NAME" \
-Fc \
--no-owner \
--no-privileges \
| gzip > "$BACKUP_FILE"
FILESIZE=$(du -h "$BACKUP_FILE" | cut -f1)
echo "Backup created: $BACKUP_FILE ($FILESIZE)"
# 上傳到 MinIO
mc cp "$BACKUP_FILE" myminio/db-backups/"$(date +%Y/%m)"/
# 清理本地暫存
rm -f "$BACKUP_FILE"
echo "=== Backup complete and uploaded to MinIO ==="# crontab - 每天凌晨 3 點備份
0 3 * * * /opt/scripts/backup-postgresql.sh >> /var/log/pg-backup.log 2>&1還原操作
# 從 MinIO 下載備份
mc cp myminio/db-backups/2024/09/myapp_2024-09-14_0300.sql.gz /tmp/
# 還原到目標資料庫
gunzip /tmp/myapp_2024-09-14_0300.sql.gz
PGPASSWORD="$DB_PASSWORD" pg_restore \
-h localhost \
-U myapp \
-d myapp_restore \
--clean --create \
/tmp/myapp_2024-09-14_0300.sql
# 驗證還原結果
psql -h localhost -U myapp -d myapp_restore -c "SELECT count(*) FROM users;"常見問題與風險
-
備份從來沒還原測試過：備份腳本跑了半年都沒問題，直到真的要還原時才發現備份檔損壞、或缺少必要的 extension。避免方式：每季做一次還原演練，在空白環境驗證備份的完整性。
-
連線數爆掉：沒有用 PgBouncer，每個服務直接連 PostgreSQL。某天流量高峰時連線數超過
max_connections，新的連線全部被拒絕，應用服務批次報錯。避免方式：一律透過 PgBouncer 連線，設定合理的DEFAULT_POOL_SIZE。 -
慢查詢拖垮整個資料庫：一個沒有加 index 的查詢做了 full table scan，佔住大量 I/O 和記憶體，影響其他正常查詢。避免方式：設定
log_min_duration_statement = 1000（記錄超過 1 秒的查詢）、啟用pg_stat_statements監控查詢效能、定期 review 慢查詢。 -
磁碟空間不足：WAL 檔案和資料表增長超出磁碟容量，PostgreSQL 直接 crash 且無法啟動。避免方式：監控磁碟使用率、設定
max_wal_size限制 WAL 大小、定期執行VACUUM FULL回收空間（注意 VACUUM FULL 會鎖表）。 -
Major version 升級失敗：直接在 production 做 major version 升級，結果某個 extension 不相容，資料庫起不來。避免方式：先在 staging 用相同的資料做升級測試、確認所有 extension 相容性、upgrade 前做完整備份。
優點
- PostgreSQL 功能完整，支援 JSON、Full-text Search、PostGIS 等擴充
- PgBouncer 有效控制連線數，減少資源消耗
pg_dump備份簡單直觀，小型資料庫夠用
缺點 / 限制
- 單節點沒有 High Availability，掛了就要手動還原
pg_dump大型資料庫（>50GB）備份和還原時間很長- Major version 升級需要停機，對 24/7 服務有影響