PostgreSQL 大对象备份

大对象管理

1. 创建和使用大对象

sql

-- 创建大对象
SELECT lo_create(0); -- 返回OID

-- 使用lo_import导入文件
SELECT lo_import('/path/to/file.pdf'); -- 返回OID

-- 使用lo_export导出文件
SELECT lo_export(12345, '/path/to/output.pdf');

-- 在表中使用大对象
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  content_oid OID
);

-- 插入大对象引用
INSERT INTO documents (name, content_oid) VALUES ('Report.pdf', 12345);

2. 查看大对象

sql

-- 查看所有大对象
SELECT 
  oid,
  lomowner AS owner,
  lomacl AS access_control,
  pg_size_pretty(pg_relation_size('pg_largeobject')) AS total_size
FROM pg_largeobject_metadata;

-- 查看大对象大小
SELECT 
  oid,
  pg_size_pretty(pg_largeobject_size(oid)) AS size
FROM pg_largeobject_metadata;

-- 查看表中引用的大对象
SELECT 
  d.id,
  d.name,
  d.content_oid,
  pg_size_pretty(pg_largeobject_size(d.content_oid)) AS size
FROM documents d;

大对象备份方法

1. 使用pg_dump备份大对象

pg_dump默认会备份大对象，可以通过以下选项控制：

bash

# 备份数据库，包括大对象
pg_dump -h localhost -U postgres -d mydb -f mydb_backup.sql

# 仅备份大对象
pg_dump -h localhost -U postgres -d mydb -b -f large_objects_backup.sql

# 使用自定义格式备份，支持压缩
pg_dump -h localhost -U postgres -d mydb -Fc -f mydb_backup.dump

# 使用目录格式备份，适合大型数据库
pg_dump -h localhost -U postgres -d mydb -Fd -f mydb_backup_dir

2. 使用pg_dumpall备份大对象

bash

# 备份所有数据库，包括大对象
pg_dumpall -h localhost -U postgres -f all_databases_backup.sql

3. 选择性备份大对象

bash

# 备份特定表及其引用的大对象
pg_dump -h localhost -U postgres -d mydb -t documents -b -f documents_backup.sql

4. 使用COPY命令备份大对象引用

sql

-- 备份表数据（包括大对象OID）
COPY documents TO '/path/to/documents_backup.csv' CSV HEADER;

-- 恢复表数据
COPY documents FROM '/path/to/documents_backup.csv' CSV HEADER;

大对象恢复方法

1. 使用psql恢复SQL格式备份

bash

# 恢复SQL格式备份
psql -h localhost -U postgres -d mydb -f mydb_backup.sql

# 恢复仅大对象备份
psql -h localhost -U postgres -d mydb -f large_objects_backup.sql

2. 使用pg_restore恢复自定义格式备份

bash

# 恢复自定义格式备份
pg_restore -h localhost -U postgres -d mydb mydb_backup.dump

# 仅恢复大对象
pg_restore -h localhost -U postgres -d mydb -L large_objects_list.txt mydb_backup.dump

# 从目录格式备份恢复
pg_restore -h localhost -U postgres -d mydb mydb_backup_dir

3. 选择性恢复大对象

bash

# 列出备份内容，查找大对象条目
pg_restore -l mydb_backup.dump > backup_list.txt

# 编辑backup_list.txt，保留需要恢复的大对象条目
# 然后恢复指定条目
pg_restore -h localhost -U postgres -d mydb -L backup_list.txt mydb_backup.dump

4. 手动恢复大对象

sql

-- 手动创建大对象并导入数据
SELECT lo_import('/path/to/file.pdf'); -- 返回新OID

-- 更新表中的大对象引用
UPDATE documents SET content_oid = 67890 WHERE id = 1;

大对象备份策略

1. 完整备份策略

场景：需要完整备份所有大对象

策略：

每周执行一次完整备份（包括大对象）
每日执行增量备份
使用压缩格式减少存储空间

bash

# 完整备份脚本示例
#!/bin/bash
date=$(date +%Y%m%d_%H%M%S)
backup_dir="/backup/postgresql"

# 确保备份目录存在
mkdir -p $backup_dir

# 执行完整备份
pg_dump -h localhost -U postgres -d mydb -Fc -b -f "$backup_dir/mydb_full_$date.dump"

# 保留最近30天的备份
find $backup_dir -name "mydb_full_*.dump" -mtime +30 -delete

2. 选择性备份策略

场景：只需要备份特定表引用的大对象

策略：

定期备份包含大对象引用的表
监控大对象大小变化
仅备份新增或修改的大对象

bash

# 选择性备份脚本示例
#!/bin/bash
date=$(date +%Y%m%d_%H%M%S)
backup_dir="/backup/postgresql"

# 备份包含大对象的表
pg_dump -h localhost -U postgres -d mydb -t documents -t images -b -f "$backup_dir/mydb_selective_$date.sql"

3. 热备份策略

场景：需要在线备份大对象，不影响生产系统

策略：

使用pg_basebackup进行基础备份
结合WAL归档实现Point-In-Time Recovery (PITR)

bash

# 基础备份
pg_basebackup -h localhost -U replicator -D /backup/base_backup -Ft -z -Xs

# 配置WAL归档
echo "wal_level = replica" >> postgresql.conf
echo "archive_mode = on" >> postgresql.conf
echo "archive_command = 'cp %p /backup/wal_archive/%f'" >> postgresql.conf

大对象备份最佳实践

1. 备份前检查

sql

-- 检查大对象完整性
SELECT lo_manage(oid) FROM pg_largeobject_metadata;

-- 检查孤立的大对象（没有被任何表引用的大对象）
SELECT 
  lom.oid
FROM pg_largeobject_metadata lom
LEFT JOIN documents d ON lom.oid = d.content_oid
WHERE d.id IS NULL;

2. 优化备份性能

使用压缩：减少备份大小和传输时间
使用并行备份：在PostgreSQL 12+中支持并行pg_dump
避免在高峰时段备份：减少对生产系统的影响
使用增量备份：减少备份时间和存储空间

bash

# 并行备份（PostgreSQL 12+）
pg_dump -h localhost -U postgres -d mydb -j 4 -Fc -b -f mydb_parallel_backup.dump

3. 验证备份完整性

bash

# 验证自定义格式备份
pg_restore -l mydb_backup.dump > /dev/null

# 恢复到测试数据库验证
createdb -h localhost -U postgres test_db
pg_restore -h localhost -U postgres -d test_db mydb_backup.dump
# 验证数据完整性
psql -h localhost -U postgres -d test_db -c "SELECT count(*) FROM documents;"

4. 监控大对象增长

sql

-- 监控大对象大小变化
CREATE TABLE large_object_size_history (
  id SERIAL PRIMARY KEY,
  check_time TIMESTAMP DEFAULT NOW(),
  total_size BIGINT,
  object_count INT
);

-- 插入大小数据
INSERT INTO large_object_size_history (total_size, object_count)
SELECT 
  sum(pg_largeobject_size(oid)),
  count(*)
FROM pg_largeobject_metadata;

-- 查看增长趋势
SELECT 
  check_time,
  pg_size_pretty(total_size) AS total_size,
  object_count
FROM large_object_size_history
ORDER BY check_time;

大对象恢复最佳实践

1. 恢复前准备

确保目标数据库已创建
确保有足够的磁盘空间
暂停应用程序写入（如果恢复到生产数据库）

2. 恢复后验证

sql

-- 验证大对象存在
SELECT 
  d.id,
  d.name,
  pg_largeobject_exists(d.content_oid) AS object_exists,
  pg_size_pretty(pg_largeobject_size(d.content_oid)) AS size
FROM documents d;

-- 验证大对象内容
SELECT lo_export(content_oid, '/tmp/test_output.pdf') FROM documents WHERE id = 1;

3. 处理恢复失败

场景：大对象恢复失败

解决方法：

检查备份文件完整性
检查磁盘空间
查看PostgreSQL日志获取详细错误信息
尝试使用不同的恢复方法

常见问题（FAQ）

Q1：如何查看大对象的大小？

A1：使用pg_largeobject_size()函数：

sql

SELECT 
  oid,
  pg_size_pretty(pg_largeobject_size(oid)) AS size
FROM pg_largeobject_metadata;

Q2：如何备份仅大对象，不备份表结构？

A2：使用-b选项：

bash

pg_dump -h localhost -U postgres -d mydb -b -s -f large_objects_only.sql

Q3：如何恢复单个大对象？

A3：使用lo_import和lo_export命令：

sql

-- 导入单个大对象
SELECT lo_import('/path/to/file.pdf'); -- 返回新OID

-- 更新表中的引用
UPDATE documents SET content_oid = 67890 WHERE id = 1;

Q4：如何删除孤立的大对象？

A4：先查找孤立大对象，然后删除：

sql

-- 查找孤立大对象
SELECT 
  lom.oid
FROM pg_largeobject_metadata lom
LEFT JOIN documents d ON lom.oid = d.content_oid
WHERE d.id IS NULL;

-- 删除孤立大对象
SELECT lo_unlink(oid) FROM pg_largeobject_metadata lom LEFT JOIN documents d ON lom.oid = d.content_oid WHERE d.id IS NULL;

Q5：大对象备份会影响性能吗？

A5：可能会，尤其是备份大型大对象时。建议：

在非高峰时段执行备份
使用压缩减少I/O
考虑使用增量备份
使用并行备份（PostgreSQL 12+）

Q6：如何监控大对象备份进度？

A6：可以通过以下方式监控：

使用pv命令监控备份进度：pg_dump ... | pv > backup.sql
监控PostgreSQL日志
使用系统监控工具（如top、iostat）监控系统资源使用

Q7：大对象备份支持增量备份吗？

A7：PostgreSQL本身不直接支持大对象的增量备份，但可以通过以下方式实现：

使用WAL归档结合基础备份
使用第三方工具（如pg_rman）
实现自定义增量备份逻辑

Q8：如何从损坏的大对象备份中恢复数据？

A8：恢复方法：

尝试使用pg_restore --ignore-version忽略版本差异
使用pg_restore -l列出备份内容，选择性恢复
尝试修复备份文件（如果损坏不严重）
从其他备份源恢复

PostgreSQL 大对象备份 ​

大对象管理 ​

1. 创建和使用大对象 ​

2. 查看大对象 ​

大对象备份方法 ​

1. 使用pg_dump备份大对象 ​

2. 使用pg_dumpall备份大对象 ​

3. 选择性备份大对象 ​

4. 使用COPY命令备份大对象引用 ​

大对象恢复方法 ​

1. 使用psql恢复SQL格式备份 ​

2. 使用pg_restore恢复自定义格式备份 ​

3. 选择性恢复大对象 ​

4. 手动恢复大对象 ​

大对象备份策略 ​

1. 完整备份策略 ​

2. 选择性备份策略 ​

3. 热备份策略 ​

大对象备份最佳实践 ​

1. 备份前检查 ​

2. 优化备份性能 ​

3. 验证备份完整性 ​

4. 监控大对象增长 ​

大对象恢复最佳实践 ​

1. 恢复前准备 ​

2. 恢复后验证 ​

3. 处理恢复失败 ​

常见问题（FAQ） ​

Q1：如何查看大对象的大小？ ​

Q2：如何备份仅大对象，不备份表结构？ ​

Q3：如何恢复单个大对象？ ​

Q4：如何删除孤立的大对象？ ​

Q5：大对象备份会影响性能吗？ ​

Q6：如何监控大对象备份进度？ ​

Q7：大对象备份支持增量备份吗？ ​

Q8：如何从损坏的大对象备份中恢复数据？ ​

PostgreSQL 大对象备份

大对象管理

1. 创建和使用大对象

2. 查看大对象

大对象备份方法

1. 使用pg_dump备份大对象

2. 使用pg_dumpall备份大对象

3. 选择性备份大对象

4. 使用COPY命令备份大对象引用

大对象恢复方法

1. 使用psql恢复SQL格式备份

2. 使用pg_restore恢复自定义格式备份

3. 选择性恢复大对象

4. 手动恢复大对象

大对象备份策略

1. 完整备份策略

2. 选择性备份策略

3. 热备份策略

大对象备份最佳实践

1. 备份前检查

2. 优化备份性能

3. 验证备份完整性

4. 监控大对象增长

大对象恢复最佳实践

1. 恢复前准备

2. 恢复后验证

3. 处理恢复失败

常见问题（FAQ）

Q1：如何查看大对象的大小？

Q2：如何备份仅大对象，不备份表结构？

Q3：如何恢复单个大对象？

Q4：如何删除孤立的大对象？

Q5：大对象备份会影响性能吗？

Q6：如何监控大对象备份进度？

Q7：大对象备份支持增量备份吗？

Q8：如何从损坏的大对象备份中恢复数据？