CentOS環境下PostgreSQL數據庫遷移實用技巧

一、常見遷移方法及步驟

1. 邏輯備份與還原（pg_dump/pg_restore）

邏輯備份是最基礎的遷移方式，適用于跨版本、跨平臺或需要靈活調整數據的場景。

• 單數據庫遷移：
  在源服務器上使用pg_dump導出數據庫（-Fc格式支持壓縮和并行，提升效率）：

  pg_dump -U postgres -Fc db_name -f db_name.dump
  

  將db_name.dump傳輸到目標服務器（如用scp），然后使用pg_restore導入：

  pg_restore -U postgres -d new_db_name db_name.dump
  

• 全集群遷移：
  若需遷移所有數據庫（包括角色、表空間等），使用pg_dumpall：

  pg_dumpall -U postgres -f all.dump
  

  傳輸到目標服務器后，用psql恢復：

  psql -U postgres -f all.dump
  

• 注意事項：
  • 導出前停止應用程序或鎖定數據庫，確保數據一致性；
  • 新版本pg_dump可兼容舊版本數據庫，建議用新版本工具導出。

2. 物理遷移數據目錄

物理遷移直接復制數據庫文件，速度快，適用于同版本、同架構的遷移（如CentOS系統內升級PostgreSQL版本）。

• 步驟：
  ① 停止舊服務：systemctl stop postgresql；
  ② 復制數據目錄（默認路徑/var/lib/pgsql/data，可通過postgresql.conf確認）：

  cp -rp /var/lib/pgsql/data/* /new/data/directory/
  

  ③ 修改權限：確保新目錄屬主為postgres，權限為700：

  chown -R postgres:postgres /new/data/directory/
  chmod -R 700 /new/data/directory/
  

  ④ 修改配置：編輯/etc/postgresql/[版本]/main/postgresql.conf，將data_directory指向新路徑；
  ⑤ 重啟服務：systemctl start postgresql。
• 注意事項：
  • 必須停止舊服務，避免數據損壞；
  • 新舊環境PostgreSQL版本需一致，否則文件格式不兼容。

3. 使用pgloader工具

pgloader支持從MySQL、SQLite、Oracle等多種數據庫遷移到PostgreSQL，也適用于PostgreSQL跨版本遷移，具備自動類型轉換、并行處理等功能。

• 步驟：
  ① 安裝pgloader：yum install -y pgloader；
  ② 創建配置文件（如migrate.load）：

  LOAD DATABASE
      FROM mysql://user:password@source_host/source_db
      INTO postgresql://user:password@target_host/target_db
      WITH include no drop, create tables, create indexes, reset sequences
  

  ③ 執行遷移：pgloader migrate.load。
• 優勢：無需手動編寫SQL，自動化處理數據類型映射，減少人為錯誤。

4. 第三方工具（如CloudCanal）

CloudCanal等可視化工具支持PostgreSQL與其他數據庫（如Doris、MySQL）的雙向同步，適合需要持續數據同步的場景。

• 步驟：
  ① 登錄CloudCanal控制臺，創建PostgreSQL數據源；
  ② 配置源和目標數據庫連接信息；
  ③ 創建遷移鏈路（選擇“全量+增量”模式），啟動同步任務。
• 優勢：圖形化操作，支持實時同步，降低運維成本。

二、遷移注意事項

1. 數據一致性保障

• 遷移前停止應用程序或設置數據庫只讀（修改pg_hba.conf，禁止寫入）；
• 使用事務包裹恢復操作（如psql -f backup.sql時添加-1參數），避免部分失敗導致數據不一致。

2. 權限與路徑檢查

• 確保目標服務器上PostgreSQL用戶（通常為postgres）對數據目錄有讀寫權限；
• 遷移后檢查角色權限（如GRANT語句是否生效）、表空間路徑（pg_tablespace表）是否正確。

3. 版本兼容性處理

• 跨主要版本（如12→13）遷移時，優先使用pg_upgrade（就地升級，停機時間短）或邏輯備份還原（兼容性好）；
• 升級前運行pg_upgrade -c檢查兼容性問題（如不支持的擴展、數據類型）；
• 參考PostgreSQL官方發行說明，處理新增/廢棄的功能（如pg_stat_statements擴展的變更）。

4. 測試與回滾計劃

• 在測試環境模擬遷移流程，驗證數據完整性（如對比源和目標數據庫的記錄數、業務查詢結果）；
• 生產環境遷移前，備份所有數據（包括配置文件、擴展腳本）；
• 準備回滾方案（如保留舊數據目錄、備份文件），若遷移失敗可快速恢復。