PostgreSQL 備份與恢復(fù)實(shí)戰(zhàn)操作pg_dump / pg_restore 全方位指南
更新時(shí)間：2025年10月21日 10:55:31 作者：csdn_tom_168
本文將帶你深入掌握 PostgreSQL 最核心的備份恢復(fù)工具 —— pg_dump 和 pg_restore,涵蓋邏輯備份、物理備份、增量備份、自動(dòng)化腳本等企業(yè)級(jí)實(shí)踐,感興趣的朋友跟隨小編一起看看吧
? PostgreSQL 備份與恢復(fù)詳解 —— pg_dump / pg_restore 全方位指南
數(shù)據(jù)安全是數(shù)據(jù)庫(kù)管理的核心。本篇將帶你深入掌握 PostgreSQL 最核心的備份恢復(fù)工具 —— pg_dump 和 pg_restore，涵蓋邏輯備份、物理備份、增量備份、自動(dòng)化腳本等企業(yè)級(jí)實(shí)踐。
?? 一、備份類型總覽
| 類型 | 工具/方法 | 特點(diǎn) | 適用場(chǎng)景 |
|---|---|---|---|
| 邏輯備份 | pg_dump / pg_dumpall | 導(dǎo)出 SQL 語(yǔ)句或自定義格式，跨版本兼容，靈活恢復(fù) | 日常備份、遷移、開(kāi)發(fā)環(huán)境 |
| 物理備份 | pg_basebackup / 文件系統(tǒng)快照 | 復(fù)制數(shù)據(jù)文件，速度快，需相同版本，支持 PITR（時(shí)間點(diǎn)恢復(fù)） | 生產(chǎn)環(huán)境全量備份 + WAL 歸檔 |
| 增量備份 | WAL 歸檔 + pg_receivewal | 基于 WAL 日志，實(shí)現(xiàn)秒級(jí)恢復(fù) | 高可用、金融級(jí)數(shù)據(jù)安全 |
?? 本篇重點(diǎn)：邏輯備份（pg_dump / pg_restore） —— 最常用、最靈活！
一、pg_dump —— 邏輯備份工具
? 1. 基本語(yǔ)法
pg_dump [選項(xiàng)] [數(shù)據(jù)庫(kù)名] > 備份文件
常用選項(xiàng)：
| 選項(xiàng) | 說(shuō)明 |
|---|---|
-U username | 指定用戶名 |
-h host | 指定主機(jī) |
-p port | 指定端口 |
-F format | 輸出格式：p(plain), c(custom), d(dir), t(tar) |
-f file | 指定輸出文件 |
-v | 詳細(xì)模式 |
-j n | 并行備份（僅 -Fd 格式） |
-t table | 只備份指定表 |
-n schema | 只備份指定模式 |
--data-only | 只備份數(shù)據(jù)（無(wú)結(jié)構(gòu)） |
--schema-only | 只備份結(jié)構(gòu)（無(wú)數(shù)據(jù)） |
--inserts | 使用 INSERT 語(yǔ)句（默認(rèn)是 COPY） |
--column-inserts | INSERT 包含列名（便于部分恢復(fù)） |
--exclude-table | 排除指定表 |
? 2. 四大備份格式詳解
? 1. 純文本格式（-Fp，默認(rèn)）
pg_dump -U postgres mydb > mydb.sql
- 優(yōu)點(diǎn)：可讀性強(qiáng)，可用 psql 恢復(fù)，支持跨版本
- 缺點(diǎn)：恢復(fù)慢，不支持選擇性恢復(fù)
? 2. 自定義格式（-Fc）
pg_dump -U postgres -Fc mydb > mydb.dump
- 優(yōu)點(diǎn)：壓縮率高，支持并行恢復(fù)，支持選擇性恢復(fù)（pg_restore）
- 缺點(diǎn)：只能用 pg_restore 恢復(fù)
? 3. 目錄格式（-Fd）
pg_dump -U postgres -Fd -f mydb_backup_dir mydb
- 優(yōu)點(diǎn)：支持并行備份/恢復(fù)，每個(gè)表獨(dú)立文件，便于管理
- 缺點(diǎn)：占用目錄空間
? 4. tar 格式（-Ft）
pg_dump -U postgres -Ft mydb > mydb.tar
- 優(yōu)點(diǎn)：兼容 tar 工具
- 缺點(diǎn)：不支持壓縮，不支持并行
?? 生產(chǎn)環(huán)境推薦：
-Fc或-Fd（支持并行和選擇性恢復(fù)）
? 3. 實(shí)用備份示例
示例1：完整備份（推薦格式）
# 自定義格式（壓縮+并行恢復(fù)） 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：僅備份結(jié)構(gòu)
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 語(yǔ)句（便于部分編輯）
pg_dump -U postgres --inserts --column-inserts -f mydb_inserts.sql mydb
? 4. pg_dumpall —— 備份整個(gè)集群
# 備份所有數(shù)據(jù)庫(kù) + 全局對(duì)象（角色、表空間等） pg_dumpall -U postgres -f cluster_backup.sql # 只備份全局對(duì)象 pg_dumpall -U postgres --globals-only -f globals.sql # 只備份某個(gè)數(shù)據(jù)庫(kù)（等同 pg_dump） pg_dumpall -U postgres -l mydb -f mydb.sql
?? pg_dumpall 只能輸出純文本格式（-Fp）
二、pg_restore —— 邏輯恢復(fù)工具
? 1. 基本語(yǔ)法
pg_restore [選項(xiàng)] [備份文件]
常用選項(xiàng)：
| 選項(xiàng) | 說(shuō)明 |
|---|---|
-U username | 指定用戶名 |
-h host | 指定主機(jī) |
-p port | 指定端口 |
-d dbname | 指定目標(biāo)數(shù)據(jù)庫(kù) |
-C | 創(chuàng)建數(shù)據(jù)庫(kù)（需連接到 postgres 數(shù)據(jù)庫(kù)） |
-c | 恢復(fù)前清理（DROP 對(duì)象） |
-j n | 并行恢復(fù) |
-t table | 只恢復(fù)指定表 |
-n schema | 只恢復(fù)指定模式 |
-l | 列出備份內(nèi)容（生成列表文件） |
-L list-file | 按列表文件選擇性恢復(fù) |
--data-only | 只恢復(fù)數(shù)據(jù) |
--schema-only | 只恢復(fù)結(jié)構(gòu) |
--verbose | 詳細(xì)輸出 |
? 2. 恢復(fù)實(shí)戰(zhàn)示例
示例1：完整恢復(fù)（自定義格式）
# 方法1：直接恢復(fù)到現(xiàn)有數(shù)據(jù)庫(kù) pg_restore -U postgres -d mydb -j 4 mydb.dump # 方法2：創(chuàng)建新數(shù)據(jù)庫(kù)并恢復(fù) pg_restore -U postgres -C -d postgres -j 4 mydb.dump # -C 會(huì)創(chuàng)建數(shù)據(jù)庫(kù)，-d postgres 表示連接到 postgres 數(shù)據(jù)庫(kù)執(zhí)行創(chuàng)建
示例2：選擇性恢復(fù)（單表）
pg_restore -U postgres -d mydb -t users mydb.dump
示例3：僅恢復(fù)數(shù)據(jù)（跳過(guò)結(jié)構(gòu)）
pg_restore -U postgres -d mydb --data-only mydb.dump
示例4：清理后恢復(fù)（先 DROP 再 CREATE）
pg_restore -U postgres -d mydb -c mydb.dump
示例5：列出備份內(nèi)容并選擇性恢復(fù)
# 生成內(nèi)容列表 pg_restore -l mydb.dump > mydb.list # 編輯 mydb.list，刪除不需要的行（如注釋掉某些表） # 只恢復(fù) users 表： # ;2345; 0 12345 TABLE users postgres # 按列表恢復(fù) pg_restore -L mydb.list -d mydb mydb.dump
示例6：并行恢復(fù)（大幅提升速度）
pg_restore -U postgres -d mydb -j 8 -Fd mydb_backup_dir
?? 并行恢復(fù)要求：
- 備份格式為
-Fc或-Fd - PostgreSQL 9.3+
三、備份恢復(fù)最佳實(shí)踐
? 1. 自動(dòng)化備份腳本（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點(diǎn)）：
0 2 * * * /path/to/backup_postgres.sh
? 2. 增量備份策略（WAL + 基礎(chǔ)備份）
# 1. 啟用 WAL 歸檔（postgresql.conf） wal_level = replica archive_mode = on archive_command = 'cp %p /wal_archive/%f' # 2. 定期基礎(chǔ)備份 pg_basebackup -D /backup/base -Ft -z -P # 3. 恢復(fù)時(shí)先恢復(fù)基礎(chǔ)備份，再應(yīng)用 WAL 日志
本篇重點(diǎn)邏輯備份，物理備份另文詳解。
? 3. 備份驗(yàn)證與監(jiān)控
驗(yàn)證備份完整性：
# 檢查自定義格式備份 pg_restore -l mydb.dump > /dev/null && echo "備份有效" # 恢復(fù)到測(cè)試數(shù)據(jù)庫(kù)驗(yàn)證 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. 常見(jiàn)問(wèn)題與解決
? 1. 權(quán)限不足
# 確保用戶有 CONNECT 和 SELECT 權(quán)限 GRANT CONNECT ON DATABASE mydb TO backup_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
? 2. 恢復(fù)時(shí)對(duì)象已存在
# 使用 -c 選項(xiàng)先清理 pg_restore -c -d mydb mydb.dump # 或手動(dòng) DROP DATABASE dropdb mydb createdb mydb pg_restore -d mydb mydb.dump
? 3. 編碼問(wèn)題
# 指定客戶端編碼 pg_dump -U postgres --encoding=UTF8 -f backup.sql mydb
? 4. 大表備份中斷
# 使用目錄格式 + 并行，支持?jǐn)帱c(diǎn)續(xù)備 pg_dump -Fd -j 4 -f backup_dir mydb
四、高級(jí)技巧
? 1. 壓縮備份
# pg_dump + gzip pg_dump -U postgres mydb | gzip > mydb.sql.gz # 恢復(fù) gunzip -c mydb.sql.gz | psql -U postgres mydb # 或使用 -Fc 格式（內(nèi)置壓縮） pg_dump -Fc -f mydb.dump mydb # 壓縮率通常 70-90%
? 2. 遠(yuǎn)程備份
# 從遠(yuǎn)程服務(wù)器備份 pg_dump -h remote_host -U postgres -Fc mydb > mydb.dump # 通過(guò) SSH 備份 ssh user@remote "pg_dump -U postgres mydb" > mydb.sql
? 3. 備份到云存儲(chǔ)（AWS S3 示例）
# 備份并上傳到 S3 pg_dump -U postgres -Fc mydb | aws s3 cp - s3://mybucket/mydb.dump # 從 S3 恢復(fù) 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
設(shè)置權(quán)限：
chmod 600 ~/.pgpass
?? 五、實(shí)踐任務(wù)
請(qǐng)完成以下操作：
- 使用
pg_dump備份你的數(shù)據(jù)庫(kù)（自定義格式） - 創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)
mydb_restore - 使用
pg_restore恢復(fù)備份到新數(shù)據(jù)庫(kù) - 嘗試只恢復(fù)某一張表
- 編寫一個(gè)自動(dòng)化備份腳本，每天執(zhí)行并保留最近7天備份
- 驗(yàn)證備份文件的完整性
?? 備份策略推薦表
| 場(chǎng)景 | 推薦工具 | 格式 | 頻率 | 保留策略 |
|---|---|---|---|---|
| 開(kāi)發(fā)/測(cè)試環(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 | 一次性 | - |
| 災(zāi)難恢復(fù) | 物理備份 + WAL 歸檔 | - | 實(shí)時(shí) | 180天 |
到此這篇關(guān)于PostgreSQL 備份與恢復(fù)實(shí)戰(zhàn)操作pg_dump / pg_restore 全方位指南的文章就介紹到這了,更多相關(guān)PostgreSQL 備份與恢復(fù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Ubuntu中安裝PostgreSQL步驟及常規(guī)操作指南
PostgreSQL是一款很受歡迎的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS),擴(kuò)展性強(qiáng),處理大量數(shù)據(jù)時(shí)效率很高,這篇文章主要介紹了Ubuntu中安裝PostgreSQL步驟及常規(guī)操作的相關(guān)資料,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-09-09
PostgreSQL數(shù)據(jù)目錄遷移的全過(guò)程
生產(chǎn)環(huán)境中隨著PostgreSQL數(shù)據(jù)庫(kù)表數(shù)據(jù)的不斷產(chǎn)生,數(shù)據(jù)庫(kù)目錄會(huì)不斷增長(zhǎng),當(dāng)磁盤空間不足時(shí)會(huì)有將PostgreSQL數(shù)據(jù)庫(kù)數(shù)據(jù)目錄遷移到其他目錄的需求,下面詳細(xì)介紹目錄遷移過(guò)程,需要的朋友可以參考下2024-04-04
PostgreSQL擴(kuò)展UUID-OSSP的使用方法
UUID-OSSP是PostgreSQL提供的一個(gè)擴(kuò)展模塊,用于生成符合標(biāo)準(zhǔn)的 UUID,下面就來(lái)介紹一下PostgreSQL擴(kuò)展UUID-OSSP的使用方法,具有一定的參考價(jià)值,感興趣的可以了解一下2025-07-07
PostgreSQL教程(十二)：角色和權(quán)限管理介紹
這篇文章主要介紹了PostgreSQL教程(十二)：角色和權(quán)限管理介紹,本文講解了數(shù)據(jù)庫(kù)角色、角色屬性、權(quán)限、角色成員,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL 啟動(dòng)失敗的解決方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
這篇文章主要介紹了postgresql json取值為何這么慢,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-06-06
PostgreSQL物理備份恢復(fù)之 pg_rman的用法說(shuō)明
這篇文章主要介紹了PostgreSQL物理備份恢復(fù)之 pg_rman的用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
postgresql高級(jí)應(yīng)用之合并單元格的思路詳解
這篇文章主要介紹了postgresql高級(jí)應(yīng)用之合并單元格,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-05-05
postgresql 如何關(guān)閉自動(dòng)提交
這篇文章主要介紹了postgresql 如何關(guān)閉自動(dòng)提交的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Postgresql?REGEXP開(kāi)頭的正則函數(shù)用法圖文詳解
正則表達(dá)式是指一個(gè)用來(lái)描述或者匹配一系列符合某個(gè)句法規(guī)則的字符串的單個(gè)字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql?REGEXP開(kāi)頭的正則函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-02-02