阿时 發表於 2025-10-21 10:55:31

PostgreSQL 备份与恢复实战操作pg_dump / pg_restore 全方位指南

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、pg_dump &mdash;&mdash; 逻辑备份工具</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_1">✅ 1. 基本语法</a></li><ul class="third_class_ul"><li><a href="#_label3_0_1_0">常用选项：</a></li></ul><li><a href="#_lab2_0_2">✅ 2. 四大备份格式详解</a></li><ul class="third_class_ul"><li><a href="#_label3_0_2_1">▶ 1. 纯文本格式（-Fp，默认）</a></li><li><a href="#_label3_0_2_2">▶ 2. 自定义格式（-Fc）</a></li><li><a href="#_label3_0_2_3">▶ 3. 目录格式（-Fd）</a></li><li><a href="#_label3_0_2_4">▶ 4. tar 格式（-Ft）</a></li></ul><li><a href="#_lab2_0_3">✅ 3. 实用备份示例</a></li><ul class="third_class_ul"><li><a href="#_label3_0_3_5">示例1：完整备份（推荐格式）</a></li><li><a href="#_label3_0_3_6">示例2：仅备份结构</a></li><li><a href="#_label3_0_3_7">示例3：仅备份数据</a></li><li><a href="#_label3_0_3_8">示例4：备份特定表</a></li><li><a href="#_label3_0_3_9">示例5：排除某些表</a></li><li><a href="#_label3_0_3_10">示例6：使用 INSERT 语句（便于部分编辑）</a></li></ul><li><a href="#_lab2_0_4">✅ 4. pg_dumpall &mdash;&mdash; 备份整个集群</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label1">二、pg_restore &mdash;&mdash; 逻辑恢复工具</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_5">✅ 1. 基本语法</a></li><ul class="third_class_ul"><li><a href="#_label3_1_5_11">常用选项：</a></li></ul><li><a href="#_lab2_1_6">✅ 2. 恢复实战示例</a></li><ul class="third_class_ul"><li><a href="#_label3_1_6_12">示例1：完整恢复（自定义格式）</a></li><li><a href="#_label3_1_6_13">示例2：选择性恢复（单表）</a></li><li><a href="#_label3_1_6_14">示例3：仅恢复数据（跳过结构）</a></li><li><a href="#_label3_1_6_15">示例4：清理后恢复（先 DROP 再 CREATE）</a></li><li><a href="#_label3_1_6_16">示例5：列出备份内容并选择性恢复</a></li><li><a href="#_label3_1_6_17">示例6：并行恢复（大幅提升速度）</a></li></ul></ul><li><a href="#_label2">三、备份恢复最佳实践</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_7">✅ 1. 自动化备份脚本（Linux 示例）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_8">✅ 2. 增量备份策略（WAL + 基础备份）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_9">✅ 3. 备份验证与监控</a></li><ul class="third_class_ul"><li><a href="#_label3_2_9_18">验证备份完整性：</a></li><li><a href="#_label3_2_9_19">监控备份：</a></li></ul><li><a href="#_lab2_2_10">✅ 4. 常见问题与解决</a></li><ul class="third_class_ul"><li><a href="#_label3_2_10_20">❓ 1. 权限不足</a></li><li><a href="#_label3_2_10_21">❓ 2. 恢复时对象已存在</a></li><li><a href="#_label3_2_10_22">❓ 3. 编码问题</a></li><li><a href="#_label3_2_10_23">❓ 4. 大表备份中断</a></li></ul></ul><li><a href="#_label3">四、高级技巧</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_11">✅ 1. 压缩备份</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_12">✅ 2. 远程备份</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_13">✅ 3. 备份到云存储（AWS S3 示例）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_14">✅ 4. 使用 .pgpass 文件免密</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">🎯 五、实践任务</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_15">📊 备份策略推荐表</a></li><ul class="third_class_ul"></ul></ul></ul></div><p>✅ <strong>PostgreSQL 备份与恢复详解 &mdash;&mdash; pg_dump / pg_restore 全方位指南</strong></p>
<p>数据安全是数据库管理的核心。本篇将带你深入掌握 PostgreSQL 最核心的备份恢复工具 &mdash;&mdash; <code>pg_dump</code> 和 <code>pg_restore</code>，涵盖逻辑备份、物理备份、增量备份、自动化脚本等企业级实践。</p>
<h3>🧭 一、备份类型总览</h3>
<table><thead><tr><th>类型</th><th>工具/方法</th><th>特点</th><th>适用场景</th></tr></thead><tbody><tr><td><strong>逻辑备份</strong></td><td><code>pg_dump</code> / <code>pg_dumpall</code></td><td>导出 SQL 语句或自定义格式，跨版本兼容，灵活恢复</td><td>日常备份、迁移、开发环境</td></tr><tr><td><strong>物理备份</strong></td><td><code>pg_basebackup</code> / 文件系统快照</td><td>复制数据文件，速度快，需相同版本，支持 PITR（时间点恢复）</td><td>生产环境全量备份 + WAL 归档</td></tr><tr><td><strong>增量备份</strong></td><td>WAL 归档 + <code>pg_receivewal</code></td><td>基于 WAL 日志，实现秒级恢复</td><td>高可用、金融级数据安全</td></tr></tbody></table>
<p>💡 <strong>本篇重点：逻辑备份（pg_dump / pg_restore）</strong> &mdash;&mdash; 最常用、最灵活！</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、pg_dump &mdash;&mdash; 逻辑备份工具</h2>
<p class="maodian"><a name="_lab2_0_1"></a></p><p class="maodian"><a name="_lab2_1_5"></a></p><h3>✅ 1. 基本语法</h3>
<div class="jb51code"><pre class="brush:sql;">pg_dump [选项] [数据库名] &gt; 备份文件
</pre></div>
<p class="maodian"><a name="_label3_0_1_0"></a></p><p class="maodian"><a name="_label3_1_5_11"></a></p><h4>常用选项：</h4>
<table><thead><tr><th>选项</th><th>说明</th></tr></thead><tbody><tr><td><code>-U username</code></td><td>指定用户名</td></tr><tr><td><code>-h host</code></td><td>指定主机</td></tr><tr><td><code>-p port</code></td><td>指定端口</td></tr><tr><td><code>-F format</code></td><td>输出格式：<code>p</code>(plain), <code>c</code>(custom), <code>d</code>(dir), <code>t</code>(tar)</td></tr><tr><td><code>-f file</code></td><td>指定输出文件</td></tr><tr><td><code>-v</code></td><td>详细模式</td></tr><tr><td><code>-j n</code></td><td>并行备份（仅 <code>-Fd</code> 格式）</td></tr><tr><td><code>-t table</code></td><td>只备份指定表</td></tr><tr><td><code>-n schema</code></td><td>只备份指定模式</td></tr><tr><td><code>--data-only</code></td><td>只备份数据（无结构）</td></tr><tr><td><code>--schema-only</code></td><td>只备份结构（无数据）</td></tr><tr><td><code>--inserts</code></td><td>使用 INSERT 语句（默认是 COPY）</td></tr><tr><td><code>--column-inserts</code></td><td>INSERT 包含列名（便于部分恢复）</td></tr><tr><td><code>--exclude-table</code></td><td>排除指定表</td></tr></tbody></table>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>✅ 2. 四大备份格式详解</h3>
<p class="maodian"><a name="_label3_0_2_1"></a></p><h4>▶ 1. 纯文本格式（-Fp，默认）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres mydb &gt; mydb.sql
</pre></div>
<ul><li><strong>优点</strong>：可读性强，可用 psql 恢复，支持跨版本</li><li><strong>缺点</strong>：恢复慢，不支持选择性恢复</li></ul>
<p class="maodian"><a name="_label3_0_2_2"></a></p><h4>▶ 2. 自定义格式（-Fc）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres -Fc mydb &gt; mydb.dump
</pre></div>
<ul><li><strong>优点</strong>：压缩率高，支持并行恢复，支持选择性恢复（pg_restore）</li><li><strong>缺点</strong>：只能用 pg_restore 恢复</li></ul>
<p class="maodian"><a name="_label3_0_2_3"></a></p><h4>▶ 3. 目录格式（-Fd）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres -Fd -f mydb_backup_dir mydb
</pre></div>
<ul><li><strong>优点</strong>：支持并行备份/恢复，每个表独立文件，便于管理</li><li><strong>缺点</strong>：占用目录空间</li></ul>
<p class="maodian"><a name="_label3_0_2_4"></a></p><h4>▶ 4. tar 格式（-Ft）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres -Ft mydb &gt; mydb.tar
</pre></div>
<ul><li><strong>优点</strong>：兼容 tar 工具</li><li><strong>缺点</strong>：不支持压缩，不支持并行</li></ul>
<blockquote><p>💡 生产环境推荐：<code>-Fc</code> 或 <code>-Fd</code>（支持并行和选择性恢复）</p></blockquote>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>✅ 3. 实用备份示例</h3>
<p class="maodian"><a name="_label3_0_3_5"></a></p><h4>示例1：完整备份（推荐格式）</h4>
<div class="jb51code"><pre class="brush:sql;"># 自定义格式（压缩+并行恢复）
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</pre></div>
<p class="maodian"><a name="_label3_0_3_6"></a></p><h4>示例2：仅备份结构</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres --schema-only -f mydb_schema.sql mydb
</pre></div>
<p class="maodian"><a name="_label3_0_3_7"></a></p><h4>示例3：仅备份数据</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres --data-only -f mydb_data.sql mydb
</pre></div>
<p class="maodian"><a name="_label3_0_3_8"></a></p><h4>示例4：备份特定表</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres -t users -t orders -f mydb_tables.dump mydb
</pre></div>
<p class="maodian"><a name="_label3_0_3_9"></a></p><h4>示例5：排除某些表</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres --exclude-table=log_* -f mydb_no_logs.dump mydb
</pre></div>
<p class="maodian"><a name="_label3_0_3_10"></a></p><h4>示例6：使用 INSERT 语句（便于部分编辑）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_dump -U postgres --inserts --column-inserts -f mydb_inserts.sql mydb
</pre></div>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>✅ 4. pg_dumpall &mdash;&mdash; 备份整个集群</h3>
<div class="jb51code"><pre class="brush:sql;"># 备份所有数据库 + 全局对象（角色、表空间等）
pg_dumpall -U postgres -f cluster_backup.sql
# 只备份全局对象
pg_dumpall -U postgres --globals-only -f globals.sql
# 只备份某个数据库（等同 pg_dump）
pg_dumpall -U postgres -l mydb -f mydb.sql</pre></div>
<p>⚠️ <code>pg_dumpall</code> 只能输出纯文本格式（-Fp）</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、pg_restore &mdash;&mdash; 逻辑恢复工具</h2>
<h3>✅ 1. 基本语法</h3>
<div class="jb51code"><pre class="brush:sql;">pg_restore [选项] [备份文件]
</pre></div>
<h4>常用选项：</h4>
<table><thead><tr><th>选项</th><th>说明</th></tr></thead><tbody><tr><td><code>-U username</code></td><td>指定用户名</td></tr><tr><td><code>-h host</code></td><td>指定主机</td></tr><tr><td><code>-p port</code></td><td>指定端口</td></tr><tr><td><code>-d dbname</code></td><td>指定目标数据库</td></tr><tr><td><code>-C</code></td><td>创建数据库（需连接到 postgres 数据库）</td></tr><tr><td><code>-c</code></td><td>恢复前清理（DROP 对象）</td></tr><tr><td><code>-j n</code></td><td>并行恢复</td></tr><tr><td><code>-t table</code></td><td>只恢复指定表</td></tr><tr><td><code>-n schema</code></td><td>只恢复指定模式</td></tr><tr><td><code>-l</code></td><td>列出备份内容（生成列表文件）</td></tr><tr><td><code>-L list-file</code></td><td>按列表文件选择性恢复</td></tr><tr><td><code>--data-only</code></td><td>只恢复数据</td></tr><tr><td><code>--schema-only</code></td><td>只恢复结构</td></tr><tr><td><code>--verbose</code></td><td>详细输出</td></tr></tbody></table>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>✅ 2. 恢复实战示例</h3>
<p class="maodian"><a name="_label3_1_6_12"></a></p><h4>示例1：完整恢复（自定义格式）</h4>
<div class="jb51code"><pre class="brush:sql;"># 方法1：直接恢复到现有数据库
pg_restore -U postgres -d mydb -j 4 mydb.dump
# 方法2：创建新数据库并恢复
pg_restore -U postgres -C -d postgres -j 4 mydb.dump
# -C 会创建数据库，-d postgres 表示连接到 postgres 数据库执行创建</pre></div>
<p class="maodian"><a name="_label3_1_6_13"></a></p><h4>示例2：选择性恢复（单表）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_restore -U postgres -d mydb -t users mydb.dump
</pre></div>
<p class="maodian"><a name="_label3_1_6_14"></a></p><h4>示例3：仅恢复数据（跳过结构）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_restore -U postgres -d mydb --data-only mydb.dump
</pre></div>
<p class="maodian"><a name="_label3_1_6_15"></a></p><h4>示例4：清理后恢复（先 DROP 再 CREATE）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_restore -U postgres -d mydb -c mydb.dump
</pre></div>
<p class="maodian"><a name="_label3_1_6_16"></a></p><h4>示例5：列出备份内容并选择性恢复</h4>
<div class="jb51code"><pre class="brush:sql;"># 生成内容列表
pg_restore -l mydb.dump &gt; mydb.list
# 编辑 mydb.list，删除不需要的行（如注释掉某些表）
# 只恢复 users 表：
# ;2345; 0 12345 TABLE users postgres
# 按列表恢复
pg_restore -L mydb.list -d mydb mydb.dump</pre></div>
<p class="maodian"><a name="_label3_1_6_17"></a></p><h4>示例6：并行恢复（大幅提升速度）</h4>
<div class="jb51code"><pre class="brush:sql;">pg_restore -U postgres -d mydb -j 8 -Fd mydb_backup_dir
</pre></div>
<p>💡 <strong>并行恢复要求：</strong></p>
<ul><li>备份格式为 <code>-Fc</code> 或 <code>-Fd</code></li><li>PostgreSQL 9.3+</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>三、备份恢复最佳实践</h2>
<p class="maodian"><a name="_lab2_2_7"></a></p><h3>✅ 1. 自动化备份脚本（Linux 示例）</h3>
<div class="jb51code"><pre class="brush:bash;">#!/bin/bash
# backup_postgres.sh
BACKUP_DIR="/backup/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="postgres"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份
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" &gt;&gt; /var/log/pg_backup.log</pre></div>
<p>添加到 crontab（每天凌晨2点）：</p>
<div class="jb51code"><pre class="brush:sql;">0 2 * * * /path/to/backup_postgres.sh
</pre></div>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>✅ 2. 增量备份策略（WAL + 基础备份）</h3>
<div class="jb51code"><pre class="brush:bash;"># 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 日志</pre></div>
<blockquote><p>本篇重点逻辑备份，物理备份另文详解。</p></blockquote>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>✅ 3. 备份验证与监控</h3>
<p class="maodian"><a name="_label3_2_9_18"></a></p><h4>验证备份完整性：</h4>
<div class="jb51code"><pre class="brush:sql;"># 检查自定义格式备份
pg_restore -l mydb.dump &gt; /dev/null &amp;&amp; echo "备份有效"
# 恢复到测试数据库验证
createdb test_restore
pg_restore -d test_restore mydb.dump
psql -d test_restore -c "SELECT COUNT(*) FROM users;"
dropdb test_restore</pre></div>
<p class="maodian"><a name="_label3_2_9_19"></a></p><h4>监控备份：</h4>
<div class="jb51code"><pre class="brush:bash;"># 检查备份文件大小
ls -lh /backup/*.dump
# 检查备份日志
tail -f /var/log/pg_backup.log</pre></div>
<p class="maodian"><a name="_lab2_2_10"></a></p><h3>✅ 4. 常见问题与解决</h3>
<p class="maodian"><a name="_label3_2_10_20"></a></p><h4>❓ 1. 权限不足</h4>
<div class="jb51code"><pre class="brush:sql;"># 确保用户有 CONNECT 和 SELECT 权限
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
</pre></div>
<p class="maodian"><a name="_label3_2_10_21"></a></p><h4>❓ 2. 恢复时对象已存在</h4>
<div class="jb51code"><pre class="brush:sql;"># 使用 -c 选项先清理
pg_restore -c -d mydb mydb.dump
# 或手动 DROP DATABASE
dropdb mydb
createdb mydb
pg_restore -d mydb mydb.dump</pre></div>
<p class="maodian"><a name="_label3_2_10_22"></a></p><h4>❓ 3. 编码问题</h4>
<div class="jb51code"><pre class="brush:sql;"># 指定客户端编码
pg_dump -U postgres --encoding=UTF8 -f backup.sql mydb
</pre></div>
<p class="maodian"><a name="_label3_2_10_23"></a></p><h4>❓ 4. 大表备份中断</h4>
<div class="jb51code"><pre class="brush:sql;"># 使用目录格式 + 并行，支持断点续备
pg_dump -Fd -j 4 -f backup_dir mydb
</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>四、高级技巧</h2>
<p class="maodian"><a name="_lab2_3_11"></a></p><h3>✅ 1. 压缩备份</h3>
<div class="jb51code"><pre class="brush:sql;"># pg_dump + gzip
pg_dump -U postgres mydb | gzip &gt; mydb.sql.gz
# 恢复
gunzip -c mydb.sql.gz | psql -U postgres mydb
# 或使用 -Fc 格式（内置压缩）
pg_dump -Fc -f mydb.dump mydb# 压缩率通常 70-90%</pre></div>
<p class="maodian"><a name="_lab2_3_12"></a></p><h3>✅ 2. 远程备份</h3>
<div class="jb51code"><pre class="brush:bash;"># 从远程服务器备份
pg_dump -h remote_host -U postgres -Fc mydb &gt; mydb.dump
# 通过 SSH 备份
ssh user@remote "pg_dump -U postgres mydb" &gt; mydb.sql</pre></div>
<p class="maodian"><a name="_lab2_3_13"></a></p><h3>✅ 3. 备份到云存储（AWS S3 示例）</h3>
<div class="jb51code"><pre class="brush:bash;"># 备份并上传到 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</pre></div>
<p class="maodian"><a name="_lab2_3_14"></a></p><h3>✅ 4. 使用 .pgpass 文件免密</h3>
<p>创建 <code>~/.pgpass</code> 文件：</p>
<div class="jb51code"><pre class="brush:sql;">hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword
</pre></div>
<p>设置权限：</p>
<div class="jb51code"><pre class="brush:sql;">chmod 600 ~/.pgpass
</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>🎯 五、实践任务</h2>
<p>请完成以下操作：</p>
<ol><li>使用 <code>pg_dump</code> 备份你的数据库（自定义格式）</li><li>创建一个新数据库 <code>mydb_restore</code></li><li>使用 <code>pg_restore</code> 恢复备份到新数据库</li><li>尝试只恢复某一张表</li><li>编写一个自动化备份脚本，每天执行并保留最近7天备份</li><li>验证备份文件的完整性</li></ol>
<p class="maodian"><a name="_lab2_4_15"></a></p><h3>📊 备份策略推荐表</h3>
<table><thead><tr><th>场景</th><th>推荐工具</th><th>格式</th><th>频率</th><th>保留策略</th></tr></thead><tbody><tr><td>开发/测试环境</td><td>pg_dump</td><td>-Fc</td><td>每日</td><td>7天</td></tr><tr><td>生产环境（中小型）</td><td>pg_dump</td><td>-Fd -j4</td><td>每日</td><td>30天</td></tr><tr><td>生产环境（大型）</td><td>pg_basebackup + WAL</td><td>物理</td><td>每周全量 + 每日增量</td><td>60天</td></tr><tr><td>数据迁移</td><td>pg_dump</td><td>-Fp</td><td>一次性</td><td>-</td></tr><tr><td>灾难恢复</td><td>物理备份 + WAL 归档</td><td>-</td><td>实时</td><td>180天</td></tr></tbody></table>
頁: [1]
查看完整版本: PostgreSQL 备份与恢复实战操作pg_dump / pg_restore 全方位指南