Debian下PostgreSQL數據恢復方法

一、恢復前的準備工作

1. 備份當前數據：無論采用哪種恢復方式，都應先備份現有數據庫集群，防止恢復過程中數據覆蓋或丟失。
   執行命令：sudo pg_dumpall -U postgres > /path/to/backup/all-databases.sql
   該命令會生成包含所有數據庫結構和數據的SQL備份文件。

2. 停止PostgreSQL服務：恢復操作前需停止服務，確保數據一致性。
   執行命令：sudo systemctl stop postgresql

二、常見恢復方法

1. 邏輯備份與恢復（適用于常規場景）

邏輯備份通過pg_dump生成可讀的SQL或自定義格式文件，恢復時通過psql或pg_restore還原數據。

• 備份數據庫：
  使用pg_dump生成自定義格式（支持壓縮和并行恢復）：
  pg_dump -Fc laozheng > laozheng.dump
  或生成SQL格式（兼容性強，但恢復速度較慢）：
  pg_dump -Fp laozheng > laozheng.sql
• 恢復數據庫：
  • 自定義格式（推薦）：sudo -u postgres pg_restore -d laozheng /path/to/laozheng.dump
  • SQL格式：sudo -u postgres psql -d laozheng < /path/to/laozheng.sql
  • tar格式（需指定目錄）：sudo -u postgres pg_restore -d laozheng -C /path/to/laozheng.tar
    恢復后可通過psql -U postgres -d laozheng -c "\dt"驗證表結構及數據。

2. 物理備份與恢復（適用于大型數據庫或物理文件損壞）

物理備份通過pg_basebackup復制數據庫集群的物理文件（包括數據目錄、WAL日志），恢復速度快，但需停機。

• 創建物理備份：
  執行命令：pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/full_backup
  參數說明：-Ft表示tar格式，-Pv顯示進度，-Xf包含WAL日志，-z壓縮，-Z5壓縮級別5。
• 恢復物理備份：
  1. 停止PostgreSQL服務：sudo systemctl stop postgresql
  2. 清空原數據目錄（如/var/lib/postgresql/15/main/）：sudo rm -rf /var/lib/postgresql/15/main/*
  3. 解壓備份文件到數據目錄：sudo tar -xzf /backup/full_backup/base.tar.gz -C /var/lib/postgresql/15/main/
  4. 修改文件權限：sudo chown -R postgres:postgres /var/lib/postgresql/15/main/
  5. 啟動PostgreSQL服務：sudo systemctl start postgresql
     恢復后需驗證數據完整性（如SELECT * FROM key_table LIMIT 10;）。

3. 基于時間點恢復（PITR，適用于誤操作或數據損壞）

PITR通過WAL（Write-Ahead Logging）日志將數據庫恢復到指定時間點（如某時刻、某事務ID），需提前開啟WAL歸檔。

• 開啟WAL歸檔：
  編輯PostgreSQL配置文件（/etc/postgresql/<版本>/main/postgresql.conf），修改以下參數：

  wal_level = replica
  archive_mode = on
  archive_command = 'cp %p /usr/local/pgsql/arch/%f'
  max_wal_senders = 2
  wal_keep_segments = 100
  

  重啟服務使配置生效：sudo systemctl restart postgresql。
• 創建基礎備份：
  使用pg_basebackup創建全量備份（如備份到/backup/pitr_base）：
  pg_basebackup -D /backup/pitr_base -Ft -Pv -Xf -z -Z5 -p 5432。
• 執行PITR：
  1. 停止PostgreSQL服務：sudo systemctl stop postgresql
  2. 清空原數據目錄：sudo rm -rf /var/lib/postgresql/15/main/*
  3. 復制基礎備份到數據目錄：sudo cp -r /backup/pitr_base/* /var/lib/postgresql/15/main/
  4. 創建postgresql.auto.conf文件（用于恢復配置）：
     echo "restore_command = 'cp /usr/local/pgsql/arch/%f %p'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "recovery_target_time = '2025-10-15 14:30:00+08'" >> /var/lib/postgresql/15/main/postgresql.auto.conf
     （recovery_target_time可根據需求改為事務ID，如recovery_target_xid = '12345'）
  5. 啟動PostgreSQL服務：sudo systemctl start postgresql
     服務啟動后會自動應用WAL日志至指定時間點，完成后會自動重啟為正常模式?？赏ㄟ^日志確認恢復狀態：journalctl -u postgresql -f。

4. 使用pg_resetwal修復WAL日志損壞（高級場景）

若WAL日志損壞導致數據庫無法啟動，可使用pg_resetwal工具重置WAL日志（需謹慎操作，可能導致數據丟失）。

• 操作步驟：
  1. 停止PostgreSQL服務：sudo systemctl stop postgresql
  2. 備份原數據目錄：sudo cp -r /var/lib/postgresql/15/main /var/lib/postgresql/15/main_backup
  3. 運行pg_resetwal：sudo pg_resetwal -D /var/lib/postgresql/15/main -f
     （-f參數強制重置，無備份時不建議使用）
  4. 啟動PostgreSQL服務：sudo systemctl start postgresql
  5. 登錄數據庫驗證數據：sudo -u postgres psql -c "\l"。

三、注意事項

1. 恢復前驗證備份：恢復前需檢查備份文件的完整性和可恢復性（如嘗試用pg_restore -l列出備份內容）。
2. 選擇合適恢復方法：邏輯備份適合常規恢復，物理備份適合大型數據庫，PITR適合時間點恢復，pg_resetwal僅用于WAL日志損壞。
3. 測試恢復流程：建議在測試環境演練恢復步驟，避免生產環境誤操作。
4. 監控恢復過程：恢復時可通過journalctl -u postgresql -f實時查看日志，及時處理錯誤。