PostgreSQL 版本升級：pg_upgrade 與邏輯複寫滾動升級完整攻略 | PostgreSQL

# 停止服務
sudo systemctl stop postgresql@15-main

# 升級套件
sudo apt-get update
sudo apt-get install --only-upgrade postgresql-15

# 重新啟動
sudo systemctl start postgresql@15-main

# 驗證版本
psql -c "SELECT version();"

# 1. 安裝新版本套件（不要移除舊版本）
sudo apt-get install postgresql-17 postgresql-server-dev-17

# 2. 初始化新版本叢集（locale 必須與舊叢集相同）
sudo -u postgres /usr/lib/postgresql/17/bin/initdb \
    -D /var/lib/postgresql/17/main \
    --locale=en_US.UTF-8 \
    --encoding=UTF8

# 3. 確認新舊叢集都已停止
sudo systemctl stop postgresql@17-main
sudo systemctl stop postgresql@15-main

sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
    --old-datadir  /var/lib/postgresql/15/main \
    --new-datadir  /var/lib/postgresql/17/main \
    --old-bindir   /usr/lib/postgresql/15/bin \
    --new-bindir   /usr/lib/postgresql/17/bin \
    --check --verbose

sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
    --old-datadir  /var/lib/postgresql/15/main \
    --new-datadir  /var/lib/postgresql/17/main \
    --old-bindir   /usr/lib/postgresql/15/bin \
    --new-bindir   /usr/lib/postgresql/17/bin \
    --link --verbose \
    2>&1 | tee /var/log/pg_upgrade_$(date +%Y%m%d).log

sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
    --old-datadir  /var/lib/postgresql/15/main \
    --new-datadir  /var/lib/postgresql/17/main \
    --old-bindir   /usr/lib/postgresql/15/bin \
    --new-bindir   /usr/lib/postgresql/17/bin \
    --jobs 4 --verbose
# --jobs：平行複製數，建議為 CPU 核心數的 50-75%

# === 升級前 ===
# 1. 備份（pg_upgrade 不能代替備份！）
pg_basebackup -D /backup/pre-upgrade-$(date +%Y%m%d) -Ft -z -P

# 2. 記錄所有 Extension 清單
psql -c "SELECT name, installed_version FROM pg_available_extensions
         WHERE installed_version IS NOT NULL ORDER BY name;"

# 3. 確認沒有 prepared transactions
psql -c "SELECT * FROM pg_prepared_xacts;"

# 4. 確認 replication slots
psql -c "SELECT slot_name, active FROM pg_replication_slots;"

# 5. 停止應用程式流量

# === 升級執行 ===
# 6. 停止舊叢集
sudo systemctl stop postgresql@15-main

# 7. pg_upgrade --check
# 8. pg_upgrade --link（或 --copy）

# === 升級後 ===
# 9. 啟動新叢集
sudo systemctl start postgresql@17-main

# 10. 驗證
psql -c "SELECT version();"
psql -c "\l"
psql -c "\dx"

# 11. 重建統計資訊
sudo -u postgres ./analyze_new_cluster.sh

# 12. 確認正常後刪除舊叢集
sudo -u postgres ./delete_old_cluster.sh

-- 1. 確認 wal_level = logical
SHOW wal_level;
-- 若為 replica，需修改 postgresql.conf 並重啟

-- 2. 建立複寫使用者
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

-- 3. 建立 Publication
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;

# 4. 匯入 Schema（邏輯複寫不傳輸 DDL）
pg_dump --schema-only --no-owner --no-acl \
  -h pg15-host mydb | psql -h pg17-host mydb

# 5. 建立 Subscription
psql -h pg17-host mydb << 'EOF'
CREATE SUBSCRIPTION upgrade_sub
    CONNECTION 'host=pg15-host port=5432 dbname=mydb user=replicator password=strong_password'
    PUBLICATION upgrade_pub
    WITH (copy_data = true);
EOF

-- 6. 監控複寫落差
SELECT
    slot_name,
    pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_pretty
FROM pg_replication_slots
WHERE slot_name = 'upgrade_sub';

-- === 切換流程（lag 接近 0 時）===

-- 7. 停止寫入 PG15
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

-- 8. 確認 PG17 完全追上（lag = 0）

-- 9. 同步 Sequence（邏輯複寫不同步 sequence）
-- 在 PG15 上產生同步指令
SELECT 'SELECT setval(''' || schemaname || '.' || sequencename || ''', ' || last_value || ');'
FROM pg_sequences;
-- 將結果在 PG17 上執行

-- 10. 更新應用程式連線指向 PG17

-- 11. 清理舊資源
-- PG17: DROP SUBSCRIPTION upgrade_sub;
-- PG15: DROP PUBLICATION upgrade_pub;

-- 查詢所有已安裝 Extension
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL ORDER BY name;

# 在新版本上預先安裝 Extension 套件
sudo apt-get install \
  postgresql-17-postgis-3 \
  postgresql-17-pgvector

# 方法 1：執行 pg_upgrade 自動生成的腳本（推薦）
sudo -u postgres ./analyze_new_cluster.sh
# 等同於 vacuumdb --all --analyze-in-stages

# 方法 2：手動執行
vacuumdb --all --analyze-in-stages --jobs 4 --verbose

# analyze-in-stages 說明：
# Stage 1：最小統計（快速，立即可用）
# Stage 2：預設統計量
# Stage 3：完整統計量（最準確）

-- 列出可升級的 Extension
SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

-- 升級特定 Extension
ALTER EXTENSION pg_stat_statements UPDATE;
ALTER EXTENSION postgis UPDATE TO '3.4.0';

# Physical Replica 必須重新建立
sudo -u postgres pg_basebackup \
    -h primary-host \
    -D /var/lib/postgresql/17/main \
    -P -R --wal-method=stream

# 確認 pgBouncer / 連線池設定已更新

# 查詢資料庫大小（估算 --copy 模式時間）
psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname))
         FROM pg_database ORDER BY pg_database_size(datname) DESC;"
# --copy 模式速率約 1GB/分鐘

# 情境 1：PG17 尚未啟動（最安全）
sudo systemctl start postgresql@15-main

# 情境 2：PG17 已啟動但尚未寫入新資料
sudo systemctl stop postgresql@17-main
sudo systemctl start postgresql@15-main

# 情境 3：PG17 已寫入新資料
# 必須從升級前的備份還原

# 舊目錄完整保留，直接啟動 PG15
sudo systemctl stop postgresql@17-main
sudo systemctl start postgresql@15-main

-- PG17 新功能
-- COPY FROM ... RETURNING
COPY orders(id, amount) FROM stdin RETURNING id;

-- JSON_TABLE（完整 SQL/JSON 標準）
SELECT * FROM JSON_TABLE(
    '{"orders": [{"id": 1}, {"id": 2}]}'::json,
    '$.orders[*]' COLUMNS (id INT PATH '$.id')
);

-- WAL Summary 預設啟用
SHOW summarize_wal;  -- PG17 預設 on