PostgreSQL 安裝與部署：從套件管理器到 Docker 完整指南 | PostgreSQL

# 步驟 1：安裝必要工具
sudo apt install -y curl ca-certificates

# 步驟 2：新增 PostgreSQL APT Repository
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

# 步驟 3：更新並安裝
sudo apt update
sudo apt install -y postgresql-17

# 驗證安裝
psql --version
# 輸出：postgresql 17.x

# 確認服務狀態
sudo systemctl status postgresql@17-main

# 僅安裝 psql 等客戶端工具
sudo apt install -y postgresql-client-17

# 步驟 1：停用系統內建的 PostgreSQL 模組（RHEL8+）
sudo dnf -qy module disable postgresql

# 步驟 2：安裝 PGDG YUM Repository
sudo dnf install -y \
  https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 步驟 3：安裝 PostgreSQL 17
sudo dnf install -y postgresql17-server postgresql17-contrib

# 步驟 4：初始化資料庫（YUM 安裝不自動 initdb）
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

# 步驟 5：啟動並設定開機自啟
sudo systemctl enable --now postgresql-17

# 驗證
sudo -u postgres psql -c "SELECT version();"

# 安裝 PostgreSQL 17
brew install postgresql@17

# 將執行檔加入 PATH
echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# 啟動服務（以 launchd 管理）
brew services start postgresql@17

# 連線（macOS 預設使用 trust 認證本地連線）
psql postgres

# 確認版本
psql --version

# 步驟 1：安裝編譯相依套件（Debian/Ubuntu）
sudo apt install -y \
  build-essential libreadline-dev zlib1g-dev \
  libssl-dev libicu-dev libxml2-dev libxslt1-dev \
  libperl-dev python3-dev pkg-config

# 步驟 2：下載並解壓原始碼
wget https://ftp.postgresql.org/pub/source/v17.0/postgresql-17.0.tar.bz2
tar xjf postgresql-17.0.tar.bz2
cd postgresql-17.0

# 步驟 3：configure（設定編譯選項）
./configure \
  --prefix=/usr/local/pgsql \
  --with-openssl \
  --with-icu \
  --with-llvm \
  --with-perl \
  --with-python \
  --enable-debug

# 步驟 4：編譯並安裝
make -j$(nproc)
make check          # 執行回歸測試（建議）
sudo make install

# 步驟 5：建立 postgres 使用者並初始化
sudo useradd -r -m -d /var/lib/postgresql postgres
sudo mkdir -p /var/lib/postgresql/17/data
sudo chown postgres:postgres /var/lib/postgresql/17/data
sudo -u postgres /usr/local/pgsql/bin/initdb \
  -D /var/lib/postgresql/17/data \
  --encoding=UTF8 \
  --locale=en_US.UTF-8 \
  --data-checksums

# 啟動 PostgreSQL 17 容器
docker run -d \
  --name pg17 \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17

# 連線至容器內的 psql
docker exec -it pg17 psql -U myuser -d mydb

# 從主機連線
psql -h localhost -U myuser -d mydb

# docker-compose.yml
version: '3.9'

services:
  postgres:
    image: postgres:17-alpine
    container_name: pg17
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-admin}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}       # 必填
      POSTGRES_DB: ${POSTGRES_DB:-appdb}
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d  # 初始化 SQL 腳本
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    command: >
      postgres
        -c config_file=/etc/postgresql/postgresql.conf
        -c log_statement=all
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-admin}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

volumes:
  pgdata:
    driver: local

# 完整建議的初始化命令
sudo -u postgres initdb \
  --pgdata=/var/lib/postgresql/17/data \
  --encoding=UTF8 \
  --locale=en_US.UTF-8 \
  --lc-collate=en_US.UTF-8 \
  --lc-ctype=en_US.UTF-8 \
  --data-checksums \
  --auth-host=scram-sha-256 \
  --auth-local=peer

-- 確認是否已啟用 checksums
SHOW data_checksums;

# 若初始化時未啟用，PostgreSQL 12+ 可離線啟用
sudo systemctl stop postgresql@17-main
sudo -u postgres pg_checksums --enable -D /var/lib/postgresql/17/main
sudo systemctl start postgresql@17-main

# 方法 1：SQL 命令
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# 方法 2：systemd
sudo systemctl reload postgresql@17-main

# ─── 連線與認證 ───
listen_addresses = 'localhost'     # 開發環境
# listen_addresses = '*'           # 容器環境（配合 pg_hba.conf 限制）
port = 5432
max_connections = 100
superuser_reserved_connections = 3

# ─── 資源使用 ───
shared_buffers = 256MB             # 通常設為系統 RAM 的 25%
work_mem = 4MB                     # 每個 sort/hash 操作的記憶體
maintenance_work_mem = 64MB        # VACUUM、CREATE INDEX 等

# ─── WAL ───
wal_level = replica                # 支援備援（最低設定）

# ─── 日誌 ───
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_min_duration_statement = 1000  # 記錄超過 1 秒的慢查詢

log_line_prefix = '%m [%p] %q%u@%d '
log_timezone = 'Asia/Taipei'
timezone = 'Asia/Taipei'

# Debian/Ubuntu（服務名稱：postgresql@版本-叢集名稱）
sudo systemctl start postgresql@17-main
sudo systemctl stop postgresql@17-main
sudo systemctl restart postgresql@17-main    # 會中斷所有連線
sudo systemctl reload postgresql@17-main     # 僅重載設定，不中斷連線
sudo systemctl enable postgresql@17-main     # 開機自啟

# RHEL/CentOS（服務名稱：postgresql-版本）
sudo systemctl start postgresql-17
sudo systemctl enable postgresql-17

# 查看詳細狀態
sudo systemctl status postgresql@17-main
sudo journalctl -u postgresql@17-main -f

-- 查看哪些參數已變更且尚未生效
SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

# 列出所有已安裝的叢集
pg_lsclusters
# Ver Cluster Port Status Owner    Data directory
# 15  main    5433 online postgres /var/lib/postgresql/15/main
# 17  main    5432 online postgres /var/lib/postgresql/17/main

# 建立新叢集
sudo pg_createcluster 17 replica --port 5434

# 啟動/停止特定叢集
sudo pg_ctlcluster 17 main start
sudo pg_ctlcluster 17 main stop

# 刪除叢集（警告：資料遺失）
sudo pg_dropcluster 15 main --stop

# 使用 --cluster 指定版本
psql --cluster 17/main -U postgres

# 使用環境變數切換
export PGPORT=5433   # 切換至 15 版叢集
psql -U postgres

# 1. 設定 postgres superuser 密碼
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'strong_password_here';"

# 2. 移除預設的 public schema 寫入權限
sudo -u postgres psql -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;"

# 3. 移除 template1 的 PUBLIC CONNECT 權限
sudo -u postgres psql -c "REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;"

# 4. 建立應用專用使用者（最小權限原則）
sudo -u postgres psql <<EOF
CREATE USER appuser WITH PASSWORD 'app_password';
CREATE DATABASE appdb OWNER appuser;
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO appuser;
EOF

# 5. 確認 pg_hba.conf 無 trust 規則
grep -v '^#' /etc/postgresql/17/main/pg_hba.conf | grep trust

# 6. 確認 SSL 已啟用
sudo -u postgres psql -c "SHOW ssl;"

- name: 安裝 PostgreSQL 17
  hosts: db_servers
  roles:
    - role: geerlingguy.postgresql
      vars:
        postgresql_version: "17"
        postgresql_config_options:
          - option: shared_buffers
            value: "{{ (ansible_memtotal_mb * 0.25) | int }}MB"
          - option: max_connections
            value: "200"
        postgresql_hba_entries:
          - type: local
            database: all
            user: postgres
            auth_method: peer
          - type: host
            database: all
            user: all
            address: "10.10.0.0/16"
            auth_method: scram-sha-256

resource "aws_db_instance" "postgres" {
  identifier        = "prod-postgres"
  engine            = "postgres"
  engine_version    = "17.0"
  instance_class    = "db.r6g.xlarge"
  allocated_storage = 100
  storage_type      = "gp3"
  storage_encrypted = true

  db_name  = "appdb"
  username = "admin"
  password = var.db_password

  backup_retention_period = 7
  multi_az               = true
  deletion_protection    = true
}