實戰 PostgreSQL 雲端遷移與高用性架構

身為一個在雲端環境中摸爬滾打多年的技術老貓，我深刻體會到資料函式庫效能、可擴充套件性和高用性對於應用程式的重要性。這篇文章將分享我在 AWS 上使用 PostgreSQL 的一些經驗，包含 RDS 執行個體設定、EC2 連線管理、原生備份與還原策略、RDS 讀取副本設定、以及 pglogical 雙向複製的實戰技巧，希望能幫助大家少走一些彎路。

PostgreSQL 雲端佈署：RDS 執行個體設定與連線管理

首先，我們來看看如何在 AWS 上建立 PostgreSQL 執行個體，並透過 EC2 執行個體進行連線管理。

使用 RDS 快速建立 PostgreSQL 執行個體

  graph LR
    B[B]
    D[D]
    F[F]
    A[登入 AWS 管理主控台] --> B{選擇 RDS 服務};
    B --> C[建立資料函式庫];
    C --> D{選擇 PostgreSQL 引擎};
    D --> E[設定執行個體引數];
    E --> F{設定網路和安全};
    F --> G[啟動資料函式庫];

登入 AWS 管理主控台，選擇 RDS 服務，然後點選「建立資料函式庫」。
選擇 PostgreSQL 作為資料函式庫引擎，並選擇您需要的版本。
設定執行個體識別碼、主要使用者名稱和密碼等引數。
選擇執行個體類別、儲存型別和大小等。
設定 VPC、子網路群組和安全群組等網路和安全設定。
最後，點選「建立資料函式庫」啟動您的 PostgreSQL 執行個體。

RDS 讓建立和管理 PostgreSQL 執行個體變得非常簡單，它自動處理許多底層的基礎架構設定，讓您可以專注於應用程式開發。選擇正確的執行個體類別和儲存型別對於效能至關重要，建議根據您的應用程式需求進行調整。

透過 EC2 執行個體連線到 RDS

  graph LR
    B[B]
    A[啟動 EC2 執行個體] --> B{安裝 PostgreSQL 使用者端};
    B --> C[設定安全群組];
    C --> D[使用 psql 連線];

啟動一個 EC2 執行個體，並確保它與 RDS 執行個體位於同一個 VPC 中。
在 EC2 執行個體上安裝 PostgreSQL 使用者端工具：

sudo yum update -y
sudo yum install -y postgresql

設定 EC2 執行個體的安全群組，允許從 EC2 執行個體連線到 RDS 執行個體的 5432 埠。
使用 psql 指令從 EC2 執行個體連線到 RDS 執行個體：

psql --host=<RDS_ENDPOINT> --port=5432 --username=<USERNAME> --password=<PASSWORD> --dbname=<DATABASE_NAME>

使用 EC2 執行個體作為跳板連線到 RDS 執行個體是一種常見的做法，它可以提供更高的安全性和控制性。確保 EC2 執行個體的安全群組設定正確，以防止未經授權的存取。

PostgreSQL 資料函式庫備份與還原策略

資料函式庫備份是至關重要的，它可以保護您的資料免受意外損失。以下是在 AWS 上進行 PostgreSQL 備份和還原的幾種方法。

使用 pg_dump 進行原生備份和還原

  graph LR
    B[B]
    A[SSH 登入 EC2] --> B{建立備份目錄};
    B --> C[使用 pg_dump 備份];
    C --> D[上傳備份到 S3];
    D --> E[使用 pg_restore 還原];

SSH 登入您的 EC2 執行個體。
建立一個目錄來儲存備份：mkdir ~/backups
使用 pg_dump 建立資料函式庫的備份：

pg_dump -h <RDS_ENDPOINT> -p 5432 -U <USERNAME> -Fc -f ~/backups/backup.dump <DATABASE_NAME>

將備份檔案上傳到 S3 儲存貯體，以便長期儲存。
使用 pg_restore 從備份檔案還原資料函式庫：

pg_restore -h <RDS_ENDPOINT> -p 5432 -U <USERNAME> -d <DATABASE_NAME> -v ~/backups/backup.dump

pg_dump 是一個功能強大的工具，可以建立 PostgreSQL 資料函式庫的邏輯備份。-Fc 引數指定使用自定義格式，這是一種壓縮的二進位格式，可以提高備份和還原的速度。

利用 RDS 快照進行備份和還原

RDS 快照是資料函式庫執行個體在特定時間點的完整副本。您可以使用快照來建立新的 RDS 執行個體，或者將現有執行個體還原到先前的狀態。

  graph LR
    B[B]
    A[在 RDS 控制檯中選擇執行個體] --> B{建立快照};
    B --> C[從快照還原];

在 RDS 控制檯中選擇您的 PostgreSQL 執行個體。
點選「動作」>「建立快照」。
為快照提供一個名稱，然後點選「建立快照」。
要從快照還原，請選擇快照，然後點選「動作」>「還原快照」。

RDS 快照提供了一種簡單與可靠的備份和還原方法。自動備份功能可以自動建立快照，並根據您設定的保留期進行管理。

(以下內容待續)

PostgreSQL 資料函式庫複製實戰：提升效能與高用性

在建構高用性和高效能的應用程式時，資料函式庫複製是不可或缺的技術。我將分享使用 AWS RDS 和 pglogical 擴充套件來實作 PostgreSQL 資料函式庫複製的實戰經驗。

首先，我們需要在 AWS RDS 上設定 PostgreSQL 執行個體，並安裝 pglogical 擴充套件。pglogical 是一個根據邏輯複製的 PostgreSQL 擴充套件，提供更靈活的複製設定。

設定 pglogical 涉及幾個關鍵步驟：

建立 pglogical 擴充套件： 在主資料函式庫和所有副本上建立 pglogical 擴充套件。
建立複製集合： 在主資料函式庫上建立一個複製集合，用於定義要複製的資料。
建立訂閱： 在每個副本上建立一個訂閱，訂閱主資料函式庫的複製集合。
插入測試資料： 在主資料函式庫中插入一些測試資料，並觀察資料是否同步到副本。
驗證複製： 在一個執行個體上修改 AdventureWorks 資料函式庫中的資料，確認變更是否同步到其他執行個體。
監控複製狀態： 使用以下命令檢查訂閱狀態：

SELECT * FROM pglogical.show_subscription_status();

  graph LR
    subgraph 讀取副本架構
        A[主資料函式庫] -->|讀取副本| B(讀取副本 1)
        A -->|讀取副本| C(讀取副本 2)
    end
    subgraph 雙向複製架構
        D[主資料函式庫 1] <-->|雙向複製| E[主資料函式庫 2)
    end

圖表說明瞭讀取副本和雙向複製的架構。讀取副本架構中，主資料函式庫的資料單向複製到讀取副本，適用於讀取負載大的應用。雙向複製架構允許兩個主資料函式庫之間雙向同步資料，適用於需要資料雙向同步的場景。

透過 pglogical，我們可以輕鬆設定 PostgreSQL 的讀取副本和雙向複製，提升應用程式效能和高用性。

資料函式庫遷移策略：從其他資料函式庫系統轉移到 PostgreSQL

資料函式庫遷移是複雜的過程，需要仔細規劃和執行。我將分享四種將資料從其他資料函式庫系統遷移到 PostgreSQL 的策略。

方案一：本地 PostgreSQL 伺服器遷移到 AWS EC2/RDS

此方案著重於將本地 PostgreSQL 伺服器遷移到 AWS 雲端環境。

建立資料函式庫備份： 使用 pg_dump 命令建立本地 PostgreSQL 資料函式庫的備份。
```
pg_dump -U <your_username> -W -F t -f adventureworks_backup.tar adventureworks
```
傳輸備份到 AWS： 將備份檔案傳輸到 AWS EC2 或 RDS 執行個體。EC2 可使用 SCP 命令，RDS 則可先上傳到 S3 儲存貯體。
驗證遷移： 在 EC2 或 RDS 執行個體上還原備份，並驗證資料完整性。EC2 上使用 pg_restore 命令，RDS 則透過 PostgreSQL 使用者端連線執行還原操作。

  graph LR
    C[C]
    EC2[EC2]
    RDS[RDS]
    A[本地伺服器] -->|備份| B(備份檔案)
    B -->|傳輸| C{EC2/RDS?}
    C -- EC2 --> D[EC2 執行個體]
    C -- RDS --> E[RDS 執行個體]
    D -->|還原| F[驗證]
    E -->|還原| F

此流程圖展示了本地 PostgreSQL 伺服器遷移到 AWS 的步驟。首先備份資料函式庫，然後根據目標環境 (EC2 或 RDS) 選擇傳輸方式，最後在目標環境還原並驗證。

這個方案讓企業可以利用 AWS 的雲端優勢，提升資料函式庫的可擴充套件性和可用性。

Pgloader：簡化 PostgreSQL 資料遷移

pgloader 是一款功能強大的工具，可以簡化資料遷移到 PostgreSQL 的過程。

安裝 pgloader： 使用系統套件管理器安裝 pgloader。
```
sudo apt-get update
sudo apt-get install -y pgloader
```
建立 pgloader 命令檔： 建立一個 .load 檔案，設定來源和目標資料函式庫連線資訊，以及遷移選項。
執行遷移： 使用 pgloader 命令執行遷移。

從 MySQL 遷移到 PostgreSQL

使用 pgloader 將資料從 MySQL 遷移到 PostgreSQL 十分便捷。步驟與上述類別似，只需調整命令檔中的連線字串和資料型別轉換設定。

設定 Foreign Data Wrapper (FDW)

FDW 允許 PostgreSQL 存取外部資料函式庫的資料。以下是如何設定 FDW 連線到 MySQL：

安裝 FDW： 安裝 postgresql-13-mysql-fdw 套件。
定義伺服器物件： 使用 CREATE SERVER 命令定義 MySQL 伺服器。
定義使用者對映： 使用 CREATE USER MAPPING 命令建立 PostgreSQL 使用者與 MySQL 使用者的對映關係。
建立 FDW 表格： 使用 CREATE FOREIGN TABLE 命令建立指向 MySQL 表格的 FDW 表格。
遷移資料： 使用 INSERT INTO ... SELECT 語法將資料從 FDW 表格複製到 PostgreSQL 表格。

  graph LR
    A[PostgreSQL] --> B(FDW)
    B --> C[MySQL]

此圖展示了 PostgreSQL 透過 FDW 存取 MySQL 資料的架構。FDW 作為橋樑，讓 PostgreSQL 可以直接查詢和操作 MySQL 資料。

透過 FDW，我們可以整合不同資料函式庫系統的資料，實作更靈活的資料管理。

PostgreSQL 的 Write-Ahead Logging (WAL) 與 Vacuum 程式對於資料函式庫效能和穩定性至關重要。WAL 機制確保資料函式庫的可靠性，而 Vacuum 則負責回收儲存空間並提升查詢效能。本文中，玄貓將分享多年 PostgreSQL 調校經驗，教你如何有效管理 WAL 檔案、設定歸檔策略、最佳化 Vacuum 程式，開發高效能與安全的 PostgreSQL 資料函式庫。

WAL 檔案管理與效能調校

WAL 檔案的持續增長會佔用大量儲存空間，影響效能。啟用 WAL 壓縮能有效減少 WAL 檔案大小，特別適用於大型資料函式庫。

啟用 WAL 壓縮

在 postgresql.conf 檔案中，找到 wal_compression 引數，設定為 on：

wal_compression = on

重新啟動 PostgreSQL 服務以套用變更：

sudo systemctl restart postgresql

調整 WAL 相關引數

除了壓縮，還可以調整其他 WAL 引數來最佳化效能：

wal_buffers = 16MB  # WAL 資料的分享記憶體大小
checkpoint_timeout = 10min # 自動檢查點之間的最大時間間隔
max_wal_size = 2GB # 檢查點之間 WAL 資料的最大量
min_wal_size = 128MB # 保留的最小 WAL 空間量

wal_buffers 越大，WAL 資料在記憶體中停留的時間越長，減少 I/O 操作；checkpoint_timeout 越長，寫入效能越高，但還原時間也越長；max_wal_size 和 min_wal_size 控制 WAL 檔案的大小，需要根據實際情況調整。

  graph LR
    B[B]
    off[off]
    on[on]
A[WAL 檔案] --> B{wal_compression}
B -- on --> C[壓縮]
B -- off --> D[不壓縮]

WAL 歸檔策略

WAL 歸檔將 WAL 檔案備份到單獨的儲存位置，實作時間點還原 (PITR) 並支援複製場景。

設定歸檔命令

在 postgresql.conf 檔案中，設定 archive_command 引數，指定如何歸檔 WAL 檔案：

archive_command = 'rsync %p user@remotehost:/path/to/archive/%f'

將 user@remotehost:/path/to/archive/ 替換為你的遠端伺服器資訊和歸檔目錄路徑。

這個命令使用 rsync 將 WAL 檔案複製到遠端伺服器。%p 代表 WAL 檔案的完整路徑，%f 代表 WAL 檔案名稱。

啟用歸檔模式

設定 archive_mode 引數為 on 以啟動 WAL 檔案歸檔：

archive_mode = on

多目標歸檔

PostgreSQL 也支援多個歸檔目標，可以使用 archive_destinations 引數：

archive_destinations = '/local/archive/path, user@remotehost:/remote/archive/path'

多目標歸檔可以提高資料安全性和容錯能力。

  sequenceDiagram
    participant PostgreSQL
    participant Local Storage
    participant Remote Server

    PostgreSQL->>Local Storage: 歸檔 WAL 檔案
    PostgreSQL->>Remote Server: 歸檔 WAL 檔案

Vacuum 程式最佳化

Vacuum 程式回收 dead tuples 佔用的空間，提升查詢效能。

Vacuum 命令

VACUUM sales.orders; -- 基本 Vacuum 操作
VACUUM FULL sales.orders; -- 更徹底的清理，但會鎖定資料表
VACUUM (PARALLEL 2) sales.orders; -- 平行 Vacuum，加快處理速度

VACUUM 命令回收 dead tuples 的空間；VACUUM FULL 更徹底，但會鎖定資料表；VACUUM (PARALLEL) 利用多核心 CPU 加快處理速度。

自動 Vacuum

PostgreSQL 的自動 Vacuum 程式會根據資料表活動自動觸發 Vacuum。

SHOW autovacuum; -- 檢視自動 Vacuum 設定

在 postgresql.conf 中調整自動 Vacuum 引數：

autovacuum_vacuum_scale_factor = 0.1  -- 當 10% 的資料表被修改時執行 Vacuum
autovacuum_analyze_scale_factor = 0.05 -- 當 5% 的資料表被修改時執行 Analyze

autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor 控制自動 Vacuum 的觸發頻率。

監控 Vacuum 活動

SELECT relname, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000; -- 查詢 dead tuples 數量超過 1000 的資料表

監控 Vacuum 活動可以幫助你瞭解資料函式庫的健康狀況，並及時調整 Vacuum 設定。

透過以上技巧，你可以有效管理 PostgreSQL WAL 和 Vacuum 程式，在效能和資料安全之間取得平衡。記住，根據你的實際環境和需求調整引數，才能達到最佳效果。

  graph LR
    A[WAL 設定] --> B(效能提升)
    C[WAL 歸檔] --> D(資料安全)
    E[Vacuum 最佳化] --> F(空間回收)
    F --> G(效能提升)
    B & G --> H(最佳效能)

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。