PostgreSQL 備份與恢復實戰(zhàn)操作pg_dump / pg_restore 全方位指南
更新時間：2025年10月21日 10:55:31 作者：csdn_tom_168
本文將帶你深入掌握 PostgreSQL 最核心的備份恢復工具 —— pg_dump 和 pg_restore,涵蓋邏輯備份、物理備份、增量備份、自動化腳本等企業(yè)級實踐,感興趣的朋友跟隨小編一起看看吧
? PostgreSQL 備份與恢復詳解 —— pg_dump / pg_restore 全方位指南
數(shù)據(jù)安全是數(shù)據(jù)庫管理的核心。本篇將帶你深入掌握 PostgreSQL 最核心的備份恢復工具 —— pg_dump 和 pg_restore，涵蓋邏輯備份、物理備份、增量備份、自動化腳本等企業(yè)級實踐。
?? 一、備份類型總覽
| 類型 | 工具/方法 | 特點 | 適用場景 |
|---|---|---|---|
| 邏輯備份 | pg_dump / pg_dumpall | 導出 SQL 語句或自定義格式，跨版本兼容，靈活恢復 | 日常備份、遷移、開發(fā)環(huán)境 |
| 物理備份 | pg_basebackup / 文件系統(tǒng)快照 | 復制數(shù)據(jù)文件，速度快，需相同版本，支持 PITR（時間點恢復） | 生產(chǎn)環(huán)境全量備份 + WAL 歸檔 |
| 增量備份 | WAL 歸檔 + pg_receivewal | 基于 WAL 日志，實現(xiàn)秒級恢復 | 高可用、金融級數(shù)據(jù)安全 |
?? 本篇重點：邏輯備份（pg_dump / pg_restore） —— 最常用、最靈活！
一、pg_dump —— 邏輯備份工具
? 1. 基本語法
pg_dump [選項] [數(shù)據(jù)庫名] > 備份文件
常用選項：
| 選項 | 說明 |
|---|---|
-U username | 指定用戶名 |
-h host | 指定主機 |
-p port | 指定端口 |
-F format | 輸出格式：p(plain), c(custom), d(dir), t(tar) |
-f file | 指定輸出文件 |
-v | 詳細模式 |
-j n | 并行備份（僅 -Fd 格式） |
-t table | 只備份指定表 |
-n schema | 只備份指定模式 |
--data-only | 只備份數(shù)據(jù)（無結構） |
--schema-only | 只備份結構（無數(shù)據(jù)） |
--inserts | 使用 INSERT 語句（默認是 COPY） |
--column-inserts | INSERT 包含列名（便于部分恢復） |
--exclude-table | 排除指定表 |
? 2. 四大備份格式詳解
? 1. 純文本格式（-Fp，默認）
pg_dump -U postgres mydb > mydb.sql
- 優(yōu)點：可讀性強，可用 psql 恢復，支持跨版本
- 缺點：恢復慢，不支持選擇性恢復
? 2. 自定義格式（-Fc）
pg_dump -U postgres -Fc mydb > mydb.dump
- 優(yōu)點：壓縮率高，支持并行恢復，支持選擇性恢復（pg_restore）
- 缺點：只能用 pg_restore 恢復
? 3. 目錄格式（-Fd）
pg_dump -U postgres -Fd -f mydb_backup_dir mydb
- 優(yōu)點：支持并行備份/恢復，每個表獨立文件，便于管理
- 缺點：占用目錄空間
? 4. tar 格式（-Ft）
pg_dump -U postgres -Ft mydb > mydb.tar
- 優(yōu)點：兼容 tar 工具
- 缺點：不支持壓縮，不支持并行
?? 生產(chǎn)環(huán)境推薦：
-Fc或-Fd（支持并行和選擇性恢復）
? 3. 實用備份示例
示例1：完整備份（推薦格式）
# 自定義格式（壓縮+并行恢復） pg_dump -U postgres -h localhost -p 5432 -Fc -f /backup/mydb_$(date +%Y%m%d).dump mydb # 目錄格式（并行備份） pg_dump -U postgres -Fd -j 4 -f /backup/mydb_dir mydb
示例2：僅備份結構
pg_dump -U postgres --schema-only -f mydb_schema.sql mydb
示例3：僅備份數(shù)據(jù)
pg_dump -U postgres --data-only -f mydb_data.sql mydb
示例4：備份特定表
pg_dump -U postgres -t users -t orders -f mydb_tables.dump mydb
示例5：排除某些表
pg_dump -U postgres --exclude-table=log_* -f mydb_no_logs.dump mydb
示例6：使用 INSERT 語句（便于部分編輯）
pg_dump -U postgres --inserts --column-inserts -f mydb_inserts.sql mydb
? 4. pg_dumpall —— 備份整個集群
# 備份所有數(shù)據(jù)庫 + 全局對象（角色、表空間等） pg_dumpall -U postgres -f cluster_backup.sql # 只備份全局對象 pg_dumpall -U postgres --globals-only -f globals.sql # 只備份某個數(shù)據(jù)庫（等同 pg_dump） pg_dumpall -U postgres -l mydb -f mydb.sql
?? pg_dumpall 只能輸出純文本格式（-Fp）
二、pg_restore —— 邏輯恢復工具
? 1. 基本語法
pg_restore [選項] [備份文件]
常用選項：
| 選項 | 說明 |
|---|---|
-U username | 指定用戶名 |
-h host | 指定主機 |
-p port | 指定端口 |
-d dbname | 指定目標數(shù)據(jù)庫 |
-C | 創(chuàng)建數(shù)據(jù)庫（需連接到 postgres 數(shù)據(jù)庫） |
-c | 恢復前清理（DROP 對象） |
-j n | 并行恢復 |
-t table | 只恢復指定表 |
-n schema | 只恢復指定模式 |
-l | 列出備份內(nèi)容（生成列表文件） |
-L list-file | 按列表文件選擇性恢復 |
--data-only | 只恢復數(shù)據(jù) |
--schema-only | 只恢復結構 |
--verbose | 詳細輸出 |
? 2. 恢復實戰(zhàn)示例
示例1：完整恢復（自定義格式）
# 方法1：直接恢復到現(xiàn)有數(shù)據(jù)庫 pg_restore -U postgres -d mydb -j 4 mydb.dump # 方法2：創(chuàng)建新數(shù)據(jù)庫并恢復 pg_restore -U postgres -C -d postgres -j 4 mydb.dump # -C 會創(chuàng)建數(shù)據(jù)庫，-d postgres 表示連接到 postgres 數(shù)據(jù)庫執(zhí)行創(chuàng)建
示例2：選擇性恢復（單表）
pg_restore -U postgres -d mydb -t users mydb.dump
示例3：僅恢復數(shù)據(jù)（跳過結構）
pg_restore -U postgres -d mydb --data-only mydb.dump
示例4：清理后恢復（先 DROP 再 CREATE）
pg_restore -U postgres -d mydb -c mydb.dump
示例5：列出備份內(nèi)容并選擇性恢復
# 生成內(nèi)容列表 pg_restore -l mydb.dump > mydb.list # 編輯 mydb.list，刪除不需要的行（如注釋掉某些表） # 只恢復 users 表： # ;2345; 0 12345 TABLE users postgres # 按列表恢復 pg_restore -L mydb.list -d mydb mydb.dump
示例6：并行恢復（大幅提升速度）
pg_restore -U postgres -d mydb -j 8 -Fd mydb_backup_dir
?? 并行恢復要求：
- 備份格式為
-Fc或-Fd - PostgreSQL 9.3+
三、備份恢復最佳實踐
? 1. 自動化備份腳本（Linux 示例）
#!/bin/bash
# backup_postgres.sh
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"
# 創(chuàng)建備份目錄
mkdir -p $BACKUP_DIR
# 執(zhí)行備份
pg_dump -U $USER -Fc -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump $DB_NAME
# 刪除7天前的備份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete
# 記錄日志
echo "[$(date)] 備份完成: ${DB_NAME}_${DATE}.dump" >> /var/log/pg_backup.log添加到 crontab（每天凌晨2點）：
0 2 * * * /path/to/backup_postgres.sh
? 2. 增量備份策略（WAL + 基礎備份）
# 1. 啟用 WAL 歸檔（postgresql.conf） wal_level = replica archive_mode = on archive_command = 'cp %p /wal_archive/%f' # 2. 定期基礎備份 pg_basebackup -D /backup/base -Ft -z -P # 3. 恢復時先恢復基礎備份，再應用 WAL 日志
本篇重點邏輯備份，物理備份另文詳解。
? 3. 備份驗證與監(jiān)控
驗證備份完整性：
# 檢查自定義格式備份 pg_restore -l mydb.dump > /dev/null && echo "備份有效" # 恢復到測試數(shù)據(jù)庫驗證 createdb test_restore pg_restore -d test_restore mydb.dump psql -d test_restore -c "SELECT COUNT(*) FROM users;" dropdb test_restore
監(jiān)控備份：
# 檢查備份文件大小 ls -lh /backup/*.dump # 檢查備份日志 tail -f /var/log/pg_backup.log
? 4. 常見問題與解決
? 1. 權限不足
# 確保用戶有 CONNECT 和 SELECT 權限 GRANT CONNECT ON DATABASE mydb TO backup_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
? 2. 恢復時對象已存在
# 使用 -c 選項先清理 pg_restore -c -d mydb mydb.dump # 或手動 DROP DATABASE dropdb mydb createdb mydb pg_restore -d mydb mydb.dump
? 3. 編碼問題
# 指定客戶端編碼 pg_dump -U postgres --encoding=UTF8 -f backup.sql mydb
? 4. 大表備份中斷
# 使用目錄格式 + 并行，支持斷點續(xù)備 pg_dump -Fd -j 4 -f backup_dir mydb
四、高級技巧
? 1. 壓縮備份
# pg_dump + gzip pg_dump -U postgres mydb | gzip > mydb.sql.gz # 恢復 gunzip -c mydb.sql.gz | psql -U postgres mydb # 或使用 -Fc 格式（內(nèi)置壓縮） pg_dump -Fc -f mydb.dump mydb # 壓縮率通常 70-90%
? 2. 遠程備份
# 從遠程服務器備份 pg_dump -h remote_host -U postgres -Fc mydb > mydb.dump # 通過 SSH 備份 ssh user@remote "pg_dump -U postgres mydb" > mydb.sql
? 3. 備份到云存儲（AWS S3 示例）
# 備份并上傳到 S3 pg_dump -U postgres -Fc mydb | aws s3 cp - s3://mybucket/mydb.dump # 從 S3 恢復 aws s3 cp s3://mybucket/mydb.dump - | pg_restore -U postgres -d mydb
? 4. 使用 .pgpass 文件免密
創(chuàng)建 ~/.pgpass 文件：
hostname:port:database:username:password localhost:5432:mydb:postgres:mypassword
設置權限：
chmod 600 ~/.pgpass
?? 五、實踐任務
請完成以下操作：
- 使用
pg_dump備份你的數(shù)據(jù)庫（自定義格式） - 創(chuàng)建一個新數(shù)據(jù)庫
mydb_restore - 使用
pg_restore恢復備份到新數(shù)據(jù)庫 - 嘗試只恢復某一張表
- 編寫一個自動化備份腳本，每天執(zhí)行并保留最近7天備份
- 驗證備份文件的完整性
?? 備份策略推薦表
| 場景 | 推薦工具 | 格式 | 頻率 | 保留策略 |
|---|---|---|---|---|
| 開發(fā)/測試環(huán)境 | pg_dump | -Fc | 每日 | 7天 |
| 生產(chǎn)環(huán)境（中小型） | pg_dump | -Fd -j4 | 每日 | 30天 |
| 生產(chǎn)環(huán)境（大型） | pg_basebackup + WAL | 物理 | 每周全量 + 每日增量 | 60天 |
| 數(shù)據(jù)遷移 | pg_dump | -Fp | 一次性 | - |
| 災難恢復 | 物理備份 + WAL 歸檔 | - | 實時 | 180天 |
到此這篇關于PostgreSQL 備份與恢復實戰(zhàn)操作pg_dump / pg_restore 全方位指南的文章就介紹到這了,更多相關PostgreSQL 備份與恢復內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
PostgreSQL查詢和處理JSON數(shù)據(jù)
這篇文章主要給大家介紹了關于PostgreSQL查詢和處理JSON數(shù)據(jù)的相關資料,需要的朋友可以參考下2023-11-11
PostgreSQL?16?新特性之正態(tài)分布隨機數(shù)函數(shù)的示例
這篇文章主要介紹了PostgreSQL?16?新特性之正態(tài)分布隨機數(shù)函數(shù),PostgreSQL 16 新增了一個內(nèi)置的 random_normal() 函數(shù)，用于生成這種隨機數(shù)，通過示例代碼給大家介紹的非常詳細，需要的朋友可以參考下2023-01-01
這篇文章主要介紹了PostgreSQL 序列增刪改案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 實現(xiàn)數(shù)據(jù)的導入導出
這篇文章主要介紹了postgresql 實現(xiàn)數(shù)據(jù)的導入導出，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
在PostgreSQL中,數(shù)據(jù)的導入和導出是數(shù)據(jù)庫管理中不可或缺的操作,通過使用COPYCOPYpg_dump和pg_dumpall等工具,您可以高效地管理您的數(shù)據(jù),感興趣的可以了解一下2026-05-05
這篇文章主要介紹了phpPgAdmin 常見錯誤和問題的解決辦法,如安裝錯誤、登陸錯誤、轉(zhuǎn)儲功能、其它錯誤和問題等,需要的朋友可以參考下2014-03-03
從原理到實戰(zhàn)詳解PostgreSQL數(shù)據(jù)恢復的完整指南
本文將從原理到實戰(zhàn),萬字詳解?PostgreSQL?如何實現(xiàn)數(shù)據(jù)恢復,覆蓋邏輯恢復、物理恢復PITR、誤操作回滾、從庫重建、工具選型及最佳實踐,希望對大家有所幫助2026-02-02
postgresql開啟pg_log日志詳細步驟及參數(shù)說明
pg_log日志要啟動保存的話需要去設置一下相關的配置文件參數(shù)就好了,下面這篇文章主要給大家介紹了關于postgresql開啟pg_log日志詳細步驟及參數(shù)說明的相關資料,需要的朋友可以參考下2024-02-02
PostgreSQL auto_explain插件自動記錄慢SQL執(zhí)行計劃,支持全局、會話及用戶級別加載,具有一定的參考價值,感興趣的可以了解一下2025-06-06
PostgreSQL數(shù)據(jù)庫升級的完整流程與注意事項
在現(xiàn)代軟件開發(fā)和運維實踐中,數(shù)據(jù)庫作為核心基礎設施,其穩(wěn)定性和性能至關重要,PostgreSQL 作為一款功能強大、開源且高度可靠的數(shù)據(jù)庫管理系統(tǒng),持續(xù)推出新版本以增強性能、安全性和功能特性,所以本文將深入探討 PostgreSQL 數(shù)據(jù)庫升級的完整流程,需要的朋友可以參考下2026-03-03