PostgreSQL数据库之pg_dump使用

目录

前言

1. 基础用法

1.1 备份整个数据库到 SQL 文件

2. 备份选项

2.1 仅备份结构（不包含数据）

2.2 仅备份数据（不包含表结构）

2.3 备份特定表

2.4 排除特定表

2.5 备份为自定义格式（支持压缩和快速恢复）

2.6 备份为目录格式（支持并行）

3. 高级选项

3.1 并行备份（加快大数据库备份速度）

3.2 压缩备份

3.3 备份时忽略某些数据（如排除大表）

4. 远程数据库备份

5. 恢复备份

5.1 恢复 SQL 文件

5.2 恢复自定义格式备份

5.3 恢复时创建新数据库

6. 注意事项

7. 示例场景

7.1 场景 1: 备份并压缩

7.2 场景 2: 仅备份特定表结构

7.3 场景 3: 并行备份大数据库

前言

pg_dump是PostgreSQL的一个工具，用来备份数据库。需要备份整个数据库，或者特定的表，或者结构，或者数据。然后，先介绍基本的用法，比如备份整个数据库到文件。

先分情况讨论，比如只备份结构或者数据，这时候需要用到--schema-only或者--data-only选项。还有备份特定表的情况，用-t参数加上表名。然后不同的格式，比如自定义格式、目录格式，这些有什么优势，比如压缩或者并行备份。

还有连接到远程数据库的情况，需要指定主机、端口、用户名等参数。恢复数据库的时候，可能需要使用pg_restore，特别是如果备份是自定义或目录格式的话。如果是纯SQL格式，可能用psql来恢复。

另外，我们可能不知道如何排除某些表，这时候可以用--exclude-table选项。还有并行备份的选项，比如-j参数，用于加快速度。注意事项方面，备份时确保没有长时间运行的事务，避免锁的问题。权限问题也很重要，备份文件的安全存放。

举几个例子，更直观地理解，比如备份整个数据库、备份特定表、只备份结构等等。最后定期测试备份的有效性，确保在需要时能够恢复。这样就能全面了解pg_dump的各种用法和注意事项了。

pg_dump 是 PostgreSQL 数据库自带的命令行工具，用于备份数据库或部分对象。它可以将数据库导出为 SQL 脚本或自定义格式的归档文件，常用于数据迁移、备份和恢复。以下是详细的使用说明：

1. 基础用法

1.1 备份整个数据库到 SQL 文件

pg_dump -U 用户名 -h 主机名 -p 端口 数据库名 > 备份文件.sql

• -U: 指定数据库用户名（默认使用当前系统用户）
• -h: 数据库服务器地址（默认 localhost）
• -p: 数据库端口（默认 5432）
• > 备份文件.sql: 将输出重定向到 SQL 文件

示例:

pg_dump -U postgres -h 127.0.0.1 -p 5432 mydb > mydb_backup.sql

2. 备份选项

2.1 仅备份结构（不包含数据）

pg_dump --schema-only 数据库名 > 结构备份.sql

2.2 仅备份数据（不包含表结构）

pg_dump --data-only 数据库名 > 数据备份.sql

2.3 备份特定表

pg_dump -t 表名1 -t 表名2 数据库名 > 部分表备份.sql

2.4 排除特定表

pg_dump --exclude-table=表名 数据库名 > 排除表后的备份.sql

2.5 备份为自定义格式（支持压缩和快速恢复）

pg_dump -Fc 数据库名 > 备份文件.dump

• -Fc: 输出为自定义压缩格式（需用 pg_restore 恢复）

2.6 备份为目录格式（支持并行）

pg_dump -Fd 数据库名 -f 备份目录

• -Fd: 输出为目录格式，每个表一个文件

• 支持并行备份（-j N 指定线程数）

3. 高级选项

3.1 并行备份（加快大数据库备份速度）

pg_dump -Fd -j 4 数据库名 -f 备份目录

• -j 4: 使用 4 个并行任务（仅适用于目录格式 -Fd）

3.2 压缩备份

pg_dump 数据库名 | gzip > 备份文件.sql.gz

或使用内置压缩（自定义格式默认压缩）:

pg_dump -Fc -Z 9 数据库名 > 备份文件.dump

• -Z 9: 最高压缩级别（0-9）

3.3 备份时忽略某些数据（如排除大表）

pg_dump --exclude-table-data=大表名 数据库名 > 备份.sql

4. 远程数据库备份

pg_dump -U 用户名 -h 远程IP -p 端口 数据库名 > 备份文件.sql

5. 恢复备份

5.1 恢复 SQL 文件

psql -U 用户名 -d 目标数据库名 -f 备份文件.sql

5.2 恢复自定义格式备份

pg_restore -U 用户名 -d 目标数据库名 备份文件.dump

5.3 恢复时创建新数据库

createdb 新数据库名
pg_restore -U 用户名 -d 新数据库名 备份文件.dump

6. 注意事项

1. 权限问题: 确保执行命令的用户有对应数据库的读取权限。
2. 避免锁冲突: 备份期间可能对表加锁，建议在低峰期操作。
3. 版本兼容性: pg_dump 版本通常需与 PostgreSQL 服务器版本一致。
4. 备份验证: 定期测试备份文件是否可恢复。
5. 环境变量: 可通过 PGPASSWORD=密码 传递密码（不推荐在脚本中明文使用，建议使用 .pgpass 文件）。

7. 示例场景

7.1 场景 1: 备份并压缩

pg_dump -U postgres mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

7.2 场景 2: 仅备份特定表结构

pg_dump -U postgres --schema-only -t users -t orders mydb > tables_schema.sql

7.3 场景 3: 并行备份大数据库

pg_dump -Fd -j 8 -U postgres -f /backup/mydb mydb

通过合理使用 pg_dump，可以实现灵活的数据库备份策略。建议结合定时任务（如 cron）实现自动化备份。