---

PostgreSQL 18 於 2025 年 9 月 25 日正式發布，這是 PostgreSQL 史上最重要的版本之一。有介紹 非同步I/O子系統，從儲存讀取的效能提高了 3次，以及許多新功能，例如 UUIDv7, 虛擬產生的列, 和 OAuth 2.0 身份驗證。

本文將詳細介紹如何依照生產標準從 PostgreSQL 17.6 升級到 18.1，確保 最短停機時間 和 安全復原。

---

1. 為什麼要升級到 PostgreSQL 18？

1.1.突出特點

特點	描述	好處
異步I/O	新的非同步I/O系統	順序掃描、點陣圖堆掃描速度提升至 3 倍
統計資料保存	升級時保留規劃器統計信息	升級後無需運行 ANALYZE
跳過掃描	多列 B 樹索引的查找	省略條件時查詢速度較快 = 在前綴列上
UUIDv7	本地人 uuidv7() 功能	UUID有時間戳，更適合索引
虛擬產生的列	查詢時的計算列	架構設計更加靈活
OAuth 2.0	與身分提供者進行身分驗證	輕鬆的 SSO 集成
pg_upgrade--交換	新的交換模式	升級更快，無需複製文件

1.2.需要注意的重大變更

⚠️ QUAN TRỌNG - Các thay đổi không tương thích ngược:


Data Checksums mặc định BẬT (initdb)

Cần matching checksum settings khi pg_upgrade



MD5 authentication DEPRECATED

Nên migrate sang SCRAM-SHA-256



Time zone abbreviation handling thay đổi

Session timezone được ưu tiên trước timezone_abbreviations

---

2. 升級前的準備

2.1.升級前清單

#!/bin/bash



pre-upgrade-checklist.sh
echo "=== PostgreSQL Upgrade Checklist ==="
1. Kiểm tra version hiện tại
echo "1. Current PostgreSQL Version:"
psql -c "SELECT version();"
2. Kiểm tra disk space
echo "2. Disk Space (cần ít nhất 2x data directory size):"
df -h /var/lib/postgresql
3. Kiểm tra data directory size
echo "3. Data Directory Size:"
du -sh /var/lib/postgresql/17/main
4. Kiểm tra checksum status
echo "4. Data Checksum Status:"
pg_controldata /var/lib/postgresql/17/main | grep "Data page checksum"
5. Kiểm tra extensions
echo "5. Installed Extensions:"
psql -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
6. Kiểm tra replication slots
echo "6. Replication Slots:"
psql -c "SELECT slot_name, slot_type, active FROM pg_replication_slots;"
7. Kiểm tra prepared transactions
echo "7. Prepared Transactions (should be empty):"
psql -c "SELECT * FROM pg_prepared_xacts;"

2.2.備份策略

重要： 升級前務必備份！

# 1. Full backup với pg_dumpall (logical backup)
pg_dumpall -U postgres -h localhost -f /backup/full_backup_$(date +%Y%m%d).sql

2. Base backup (physical backup) - khuyến nghị
pg_basebackup -D /backup/basebackup_$(date +%Y%m%d) 

-Ft -z -P 

-U replication 

-h localhost
3. Backup configuration files
cp /etc/postgresql/17/main/postgresql.conf /backup/
cp /etc/postgresql/17/main/pg_hba.conf /backup/
cp /etc/postgresql/17/main/pg_ident.conf /backup/

2.3.安裝 PostgreSQL 18.1

# Ubuntu/Debian
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-18

RHEL/Rocky Linux
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql18-server postgresql18-contrib

---

3. 升級方法

3.1.方法比較

方法	停機時間	使用案例	複雜性
pg_升級	分分鐘	同一主持人，短暫停頓	低
pg_upgrade--鏈接	秒 - 分鐘	相同的檔案系統，最快	低
pg_upgrade--交換 （新PG18）	秒 - 分鐘	交換目錄	低
邏輯複製	秒數	24x7 應用程序，零停機時間	高
pg_dump/恢復	小時 - 日期	小資料庫，跨平台	低

3.2.生產建議

┌─────────────────────────────────────────────────────────────┐
│                    DECISION FLOWCHART                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Database size < 100GB?                                      │
│     YES → pg_upgrade --link hoặc --swap                     │
│     NO  ↓                                                   │
│                                                              │
│  Zero-downtime required?                                     │
│     YES → Logical Replication                                │
│     NO  → pg_upgrade --link với maintenance window           │
│                                                              │
│  Cross-platform migration?                                   │
│     YES → pg_dump/restore                                    │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

4. 使用 pg_upgrade 升級（建議）

4.1.初始化新集群

# Tạo data directory cho PostgreSQL 18
sudo mkdir -p /var/lib/postgresql/18/main
sudo chown postgres:postgres /var/lib/postgresql/18/main
Chuyển sang user postgres
sudo -i -u postgres
Kiểm tra checksum của cluster cũ
pg_controldata /var/lib/postgresql/17/main | grep "Data page checksum"
Output: Data page checksum version: 0 (disabled) hoặc 1 (enabled)
Init cluster mới với MATCHING checksum setting
Nếu cluster cũ KHÔNG có checksum:
/usr/lib/postgresql/18/bin/initdb 

-D /var/lib/postgresql/18/main 

--no-data-checksums 

--encoding=UTF8 

--locale=en_US.UTF-8
Nếu cluster cũ CÓ checksum (hoặc muốn enable):
/usr/lib/postgresql/18/bin/initdb 

-D /var/lib/postgresql/18/main 

--data-checksums 

--encoding=UTF8 

--locale=en_US.UTF-8

4.2.升級前檢查

# Set environment variables
export PGBINOLD=/usr/lib/postgresql/17/bin
export PGBINNEW=/usr/lib/postgresql/18/bin
export PGDATAOLD=/var/lib/postgresql/17/main
export PGDATANEW=/var/lib/postgresql/18/main

Chạy check mode TRƯỚC
/usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=$PGDATAOLD 

--new-datadir=$PGDATANEW 

--old-bindir=$PGBINOLD 

--new-bindir=$PGBINNEW 

--check
Expected output:
Performing Consistency Checks
-----------------------------
Checking cluster versions                         ok
Checking database connection settings             ok
Checking database user is the install user        ok
Checking for prepared transactions                ok
...
Clusters are compatible

4.3.停止服務和升級

# 1. Stop ứng dụng kết nối đến database

(tùy thuộc vào setup của bạn)
2. Stop PostgreSQL 17
sudo systemctl stop postgresql@17-main
3. Verify PostgreSQL 17 đã stop
pg_isready -p 5432
Output: no response
4. Thực hiện upgrade với --link mode (nhanh nhất)
cd /var/lib/postgresql
/usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=$PGDATAOLD 

--new-datadir=$PGDATANEW 

--old-bindir=$PGBINOLD 

--new-bindir=$PGBINNEW 

--link 

--jobs=$(nproc)
Hoặc với --swap mode (PostgreSQL 18 mới)
/usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=$PGDATAOLD 

--new-datadir=$PGDATANEW 

--old-bindir=$PGBINOLD 

--new-bindir=$PGBINNEW 

--swap 

--jobs=$(nproc)

4.4.複製設定檔

# Copy các config files từ cluster cũ
cp /etc/postgresql/17/main/postgresql.conf /etc/postgresql/18/main/
cp /etc/postgresql/17/main/pg_hba.conf /etc/postgresql/18/main/
cp /etc/postgresql/17/main/pg_ident.conf /etc/postgresql/18/main/

Hoặc nếu dùng data directory cho config
cp $PGDATAOLD/postgresql.conf $PGDATANEW/
cp $PGDATAOLD/pg_hba.conf $PGDATANEW/
Cập nhật port trong postgresql.conf nếu cần
(nếu muốn chạy song song cả 2 version)
sed -i 's/port = 5432/port = 5433/' /etc/postgresql/18/main/postgresql.conf

4.5.啟動 PostgreSQL 18 並驗證

# Start PostgreSQL 18
sudo systemctl start postgresql@18-main

Verify connection
psql -p 5432 -c "SELECT version();"
Output: PostgreSQL 18.1 on x86_64-pc-linux-gnu...
Verify databases
psql -c "\l"
Verify table counts (sample)
psql -d your_database -c "SELECT schemaname, COUNT(*) FROM pg_tables GROUP BY schemaname;"

---

5. 升級後任務

5.1.統計處理（PostgreSQL 18 自動保留）

# PostgreSQL 18 tự động preserve statistics!

Chỉ cần chạy cho extended statistics nếu có:
/usr/lib/postgresql/18/bin/vacuumdb 

--all 

--analyze-in-stages 

--missing-stats-only
Hoặc chạy đầy đủ nếu muốn
vacuumdb --all --analyze

5.2.擴充更新

# Kiểm tra extensions cần update
psql -c "SELECT * FROM pg_extension WHERE extversion != (SELECT default_version FROM pg_available_extensions WHERE name = extname);"

Update tất cả extensions
psql -c "SELECT format('ALTER EXTENSION %I UPDATE;', extname) FROM pg_extension;" | psql
Hoặc update từng extension
psql -c "ALTER EXTENSION pg_stat_statements UPDATE;"
psql -c "ALTER EXTENSION postgis UPDATE;"

5.3.清理

# Xóa cluster cũ (CHỈ SAU KHI VERIFY HOÀN TẤT!)

pg_upgrade tạo script delete_old_cluster.sh
./delete_old_cluster.sh
Hoặc thủ công
rm -rf /var/lib/postgresql/17/main
Disable PostgreSQL 17 service
sudo systemctl disable postgresql@17-main

5.4.性能驗證

-- Kiểm tra query plan của các query quan trọng
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM your_critical_table WHERE your_condition;

-- So sánh với baseline trước upgrade
-- Lưu ý: PostgreSQL 18 EXPLAIN ANALYZE tự động include BUFFERS
-- Kiểm tra AIO settings (PostgreSQL 18)
SHOW io_method;
-- Output: io_uring (Linux) hoặc worker
-- Monitor với pg_stat_io (mới trong PG16, cải thiện PG18)
SELECT * FROM pg_stat_io WHERE reads > 0;

---

6. 透過邏輯複製進行升級（零停機）

6.1.架構概述

┌──────────────────────┐         ┌──────────────────────┐
│   PostgreSQL 17.6    │         │   PostgreSQL 18.1    │
│     (Publisher)      │  ─────► │    (Subscriber)      │
│   Primary/Source     │   WAL   │   Target/Replica     │
│     Port: 5432       │         │     Port: 5433       │
└──────────────────────┘         └──────────────────────┘
│
▼
Cutover (DNS/HAProxy)

6.2.安裝發布者 (PostgreSQL 17)

-- 1. Enable logical replication trong postgresql.conf
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- 2. Tạo publication
CREATE PUBLICATION pg18_migration FOR ALL TABLES;
-- 3. Tạo replication user
CREATE USER repl_user WITH REPLICATION PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;
-- 4. Cập nhật pg_hba.conf
-- host    all    repl_user    subscriber_ip/32    scram-sha-256
-- host    replication    repl_user    subscriber_ip/32    scram-sha-256

6.3.設定訂閱者 (PostgreSQL 18)

# 1. Dump schema only (không data)
pg_dump -h old_server -U postgres -s -d your_db > schema.sql

2. Restore schema vào PostgreSQL 18
psql -d your_db -f schema.sql

-- 3. Tạo subscription
CREATE SUBSCRIPTION pg18_sub
CONNECTION 'host=old_server port=5432 dbname=your_db user=repl_user password=secure_password'
PUBLICATION pg18_migration;

-- 4. Monitor sync progress
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel;
-- 5. Kiểm tra lag
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as lag
FROM pg_replication_slots;

6.4.割接流程

#!/bin/bash

cutover.sh - Zero-downtime cutover script
echo "=== Starting Cutover Process ==="
1. Stop writes to old server (application level)
echo "1. Stopping application writes..."
kubectl scale deployment/app --replicas=0
hoặc update HAProxy/PgBouncer
2. Wait for replication to catch up
echo "2. Waiting for replication lag to be 0..."
while true; do
LAG=$(psql -h new_server -p 5433 -t -c 

"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_name='pg18_sub'")
if [ "$LAG" -eq 0 ]; then
break
fi
sleep 1
done
3. Disable subscription
echo "3. Disabling subscription..."
psql -h new_server -p 5433 -c "ALTER SUBSCRIPTION pg18_sub DISABLE;"
psql -h new_server -p 5433 -c "DROP SUBSCRIPTION pg18_sub;"
4. Reset sequences (quan trọng!)
echo "4. Resetting sequences..."
psql -h new_server -p 5433 -c "SELECT setval(c.oid, s.last_value) FROM pg_class c JOIN pg_sequences s ON c.relname = s.sequencename WHERE s.last_value IS NOT NULL;"
5. Switch traffic to new server
echo "5. Switching traffic..."
Update DNS, HAProxy, PgBouncer, etc.
echo "=== Cutover Complete ==="

---

7. 回滾計劃

7.1.從 pg_upgrade 回滾 --link

# QUAN TRỌNG: Với --link mode, bạn KHÔNG THỂ rollback sau khi start cluster mới!

Do đó, LUÔN test kỹ trước khi start
Nếu chưa start PostgreSQL 18:
Chỉ cần start lại PostgreSQL 17
sudo systemctl start postgresql@17-main

7.2.從 pg_upgrade 回滾 --copy

# Nếu dùng --copy mode, cluster cũ vẫn còn nguyên

Chỉ cần switch về cluster cũ
sudo systemctl stop postgresql@18-main
sudo systemctl start postgresql@17-main

7.3.從邏輯複製回滾

-- Trên subscriber (PostgreSQL 18)
ALTER SUBSCRIPTION pg18_sub DISABLE;
DROP SUBSCRIPTION pg18_sub;

-- Switch traffic về PostgreSQL 17
-- Update DNS/HAProxy/PgBouncer

---

8. 監控和故障排除

8.1.監控腳本

#!/bin/bash

monitor-upgrade.sh
echo "=== PostgreSQL 18 Health Check ==="
1. Version
psql -c "SELECT version();"
2. Uptime
psql -c "SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() as uptime;"
3. Active connections
psql -c "SELECT count(*) as active_connections FROM pg_stat_activity WHERE state = 'active';"
4. Database sizes
psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"
5. Long running queries
psql -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';"
6. Replication status (if applicable)
psql -c "SELECT * FROM pg_stat_replication;"
7. I/O statistics (PostgreSQL 18)
psql -c "SELECT backend_type, reads, writes, extends FROM pg_stat_io WHERE reads > 0 OR writes > 0;"

9. 最佳實務總結

9.1.預升級

• [ ] 仔細閱讀 發行說明 PostgreSQL 18
• [ ] 在暫存/開發環境上測試升級
• [ ] 完整備份（邏輯+物理）
• [ ] 文檔回溯計劃
• [ ] 通知利害關係人有關維護窗口的信息
• [ ] 檢查所有擴充功能的兼容性

9.2.升級期間

• [ ] 使用 pg_upgrade --檢查 在實際升級之前
• [ ] 升級期間監控磁碟空間
• [ ] 保持終端會話 螢幕 或 多路復用器
• [ ] 記錄所有輸出以進行故障排除

9.3.升級後

• [ ] 驗證資料完整性
• [ ] 測試關鍵查詢並與基準進行比較
• [ ] 更新擴展
• [ ] 24-48 小時監控效能
• [ ] 驗證完成後清理舊集群
• [ ] 更新文件和操作手冊

---

10. 結論

PostgreSQL 18 帶來了許多有價值的改進，特別是 異步I/O 和 統計資料保存 幫助升級比以往更順利。透過仔細規劃並遵循正確的流程，您可以升級生產資料庫 最短停機時間 和 低風險。

參考文獻：

• PostgreSQL 18 發行說明
• pg_upgrade 文檔
• 升級 PostgreSQL 叢集

---

如果文章有用，請分享並在下面留言！ 👇