印刻数据库

外观

PostgreSQL 初始数据导入 ​

数据导入工具选择 ​

pg_dump/pg_restore ​

pg_dump 和 pg_restore 是 PostgreSQL 内置的逻辑备份和恢复工具，支持多种备份格式。

主要特点 ​

  • 支持多种备份格式：自定义格式、目录格式、tar 格式、SQL 格式
  • 支持并行恢复
  • 支持选择性恢复（表、模式等）
  • 支持压缩备份

使用示例 ​

bash
# 使用自定义格式备份数据库
pg_dump -h source_host -U postgres -d source_db -F c -j 4 -Z 5 -f /backup/source_db.dump

# 使用并行恢复导入数据
pg_restore -h target_host -U postgres -d target_db -j 4 /backup/source_db.dump

# 选择性恢复特定表
pg_restore -h target_host -U postgres -d target_db -t table_name /backup/source_db.dump

pg_basebackup ​

pg_basebackup 是 PostgreSQL 内置的物理备份工具，基于 WAL 日志复制实现。

主要特点 ​

  • 支持全量备份和增量备份
  • 支持压缩备份
  • 支持并行备份
  • 恢复速度快

使用示例 ​

bash
# 使用 pg_basebackup 备份数据库
pg_basebackup -h source_host -U replicator -D /backup/base_backup -X stream -F t -Z 5 -j 4

# 恢复物理备份
# 1. 停止目标数据库
pg_ctl -D /var/lib/postgresql/14/main stop

# 2. 清理目标数据库数据目录
rm -rf /var/lib/postgresql/14/main/*

# 3. 解压备份文件
tar -xzf /backup/base_backup.tar.gz -C /var/lib/postgresql/14/main/

# 4. 创建恢复配置文件
touch /var/lib/postgresql/14/main/recovery.signal

# 5. 启动目标数据库
pg_ctl -D /var/lib/postgresql/14/main start

COPY 命令 ​

COPY 命令是 PostgreSQL 内置的高速数据导入导出命令，适合大量数据的导入。

主要特点 ​

  • 支持从文件导入数据到表
  • 支持从查询结果导出数据到文件
  • 支持多种文件格式：文本格式、CSV 格式
  • 导入速度快

使用示例 ​

bash
# 使用 COPY 命令导入 CSV 数据
psql -h target_host -U postgres -d target_db -c "COPY table_name FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');"

# 使用 COPY 命令导入文本数据
psql -h target_host -U postgres -d target_db -c "COPY table_name FROM '/path/to/data.txt' WITH (FORMAT text, DELIMITER '\t', NULL '\N');"

# 从标准输入导入数据
cat /path/to/data.csv | psql -h target_host -U postgres -d target_db -c "COPY table_name FROM STDIN WITH (FORMAT csv, HEADER true);"

第三方工具 ​

除了 PostgreSQL 内置工具外，还有一些第三方工具可以用于初始数据导入。

pg_bulkload ​

pg_bulkload 是一个高速数据导入工具，适合超大数据量的导入。

bash
# 安装 pg_bulkload
sudo apt-get install postgresql-14-pg-bulkload

# 使用 pg_bulkload 导入数据
pg_bulkload -d target_db -U postgres -W -i /path/to/data.csv -o /path/to/output.log -l /path/to/log.csv -O table_name

pgloader ​

pgloader 是一个用于从其他数据库迁移数据到 PostgreSQL 的工具，支持 MySQL、SQLite、CSV 等数据源。

bash
# 安装 pgloader
sudo apt-get install pgloader

# 从 CSV 文件导入数据
pgloader csv:///path/to/data.csv postgresql://postgres:password@target_host/target_db

# 从 MySQL 迁移数据
pgloader mysql://mysql_user:mysql_password@mysql_host/mysql_db postgresql://postgres:password@target_host/target_db

数据导入优化策略 ​

1. 调整 PostgreSQL 配置 ​

在初始数据导入期间，可以临时调整 PostgreSQL 配置，提高导入速度。

推荐配置 ​

bash
# 在 postgresql.conf 中临时调整
shared_buffers = 1GB               # 增加共享缓冲区
double_write = off                 # 关闭双写
checkpoint_timeout = 30min         # 延长检查点超时时间
max_wal_size = 4GB                 # 增加最大 WAL 大小
maintenance_work_mem = 512MB       # 增加维护工作内存
synchronous_commit = off           # 关闭同步提交
wal_level = minimal                # 使用最小 WAL 级别
full_page_writes = off             # 关闭全页写入

# 导入完成后恢复默认配置
double_write = on
checkpoint_timeout = 5min
max_wal_size = 1GB
synchronous_commit = on
wal_level = replica
full_page_writes = on

2. 使用并行导入 ​

利用多核 CPU 进行并行导入，提高导入速度。

bash
# 使用 pg_restore 并行恢复
pg_restore -h target_host -U postgres -d target_db -j 4 /backup/source_db.dump

# 使用 COPY 命令并行导入多个表
# 创建多个导入脚本，并行执行
psql -h target_host -U postgres -d target_db -c "COPY table1 FROM '/path/to/data1.csv' WITH (FORMAT csv);" &
psql -h target_host -U postgres -d target_db -c "COPY table2 FROM '/path/to/data2.csv' WITH (FORMAT csv);" &
psql -h target_host -U postgres -d target_db -c "COPY table3 FROM '/path/to/data3.csv' WITH (FORMAT csv);" &
wait

3. 禁用索引和约束 ​

在数据导入前禁用索引和约束，导入完成后重新启用，可以提高导入速度。

sql
-- 禁用索引
ALTER INDEX index_name DISABLE;

-- 禁用外键约束
ALTER TABLE table_name DISABLE TRIGGER ALL;

-- 导入数据后重新启用索引
ALTER INDEX index_name ENABLE;

-- 导入数据后重新启用外键约束
ALTER TABLE table_name ENABLE TRIGGER ALL;

4. 调整数据格式 ​

选择合适的数据格式可以提高导入速度。

推荐数据格式 ​

  • 自定义格式：pg_dump 自定义格式，支持压缩和并行恢复
  • CSV 格式：适合大量数据的导入，支持 COPY 命令
  • 目录格式：支持并行备份和恢复

5. 优化存储配置 ​

优化存储配置，提高磁盘 I/O 性能。

推荐配置 ​

  • 使用 SSD 存储，提高磁盘 I/O 速度
  • 调整 RAID 配置，提高存储性能
  • 优化文件系统配置，如禁用访问时间记录

不同数据量的导入策略 ​

小数据量（< 1GB） ​

对于小数据量的导入，可以使用简单的导入方法。

推荐策略 ​

  • 使用 pg_dump/pg_restore 导入
  • 使用 SQL 脚本导入
  • 不需要特别调整 PostgreSQL 配置

中数据量（1GB-100GB） ​

对于中数据量的导入，需要优化导入配置。

推荐策略 ​

  • 使用并行导入
  • 调整 PostgreSQL 配置
  • 禁用索引和约束
  • 使用 SSD 存储

大数据量（> 100GB） ​

对于大数据量的导入，需要更复杂的优化策略。

推荐策略 ​

  • 使用物理备份恢复
  • 调整 PostgreSQL 配置
  • 禁用索引和约束
  • 使用 SSD 存储
  • 考虑使用分区表
  • 并行导入多个表

数据导入监控和验证 ​

导入进度监控 ​

监控数据导入进度，确保导入过程正常进行。

监控方法 ​

  • 使用 pg_stat_progress_restore 视图监控 pg_restore 进度
  • 使用 pg_stat_activity 视图监控导入进程
  • 监控系统资源使用情况
sql
-- 监控 pg_restore 进度
SELECT * FROM pg_stat_progress_restore;

-- 监控导入进程
SELECT pid, usename, datname, query, wait_event_type, wait_event 
FROM pg_stat_activity 
WHERE query LIKE '%COPY%' OR query LIKE '%restore%';

数据完整性验证 ​

导入完成后，验证数据完整性，确保数据正确导入。

验证方法 ​

  • 检查表行数
  • 检查关键数据字段
  • 运行数据完整性约束检查
  • 比较源数据库和目标数据库的统计信息
sql
-- 检查表行数
SELECT COUNT(*) FROM table_name;

-- 检查关键数据字段
SELECT COUNT(*) FROM table_name WHERE key_column IS NULL;

-- 运行数据完整性约束检查
ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;

-- 收集统计信息
ANALYZE table_name;

常见问题（FAQ） ​

Q1: 导入速度慢怎么办？ ​

A1: 可以通过以下方法提高导入速度：

  1. 调整 PostgreSQL 配置，如增加 shared_buffers、关闭双写等
  2. 使用并行导入
  3. 禁用索引和约束
  4. 使用 SSD 存储
  5. 优化数据格式

Q2: 导入过程中出现内存不足错误怎么办？ ​

A2: 可以通过以下方法解决：

  1. 减少并行度
  2. 增加系统内存
  3. 调整 work_mem 和 maintenance_work_mem 参数
  4. 分批导入数据

Q3: 导入过程中 WAL 日志满导致导入失败怎么办？ ​

A3: 可以通过以下方法解决：

  1. 增加 max_wal_size 参数
  2. 延长 checkpoint_timeout 参数
  3. 启用 WAL 归档
  4. 使用最小 WAL 级别

Q4: 导入过程中出现约束冲突错误怎么办？ ​

A4: 可以通过以下方法解决：

  1. 检查源数据完整性
  2. 禁用约束后导入，导入完成后重新启用
  3. 调整导入顺序，确保外键约束满足

Q5: 导入过程中由于各种原因中断怎么办？ ​

A5: 可以通过以下方法解决：

  1. 使用支持断点续传的导入工具
  2. 分批导入数据，便于恢复
  3. 定期备份导入进度
  4. 使用事务，确保导入的原子性

不同版本的导入特性 ​

PostgreSQL 12 及以上版本 ​

  • 改进了并行恢复性能
  • 支持 pg_stat_progress_restore 视图，监控恢复进度
  • 支持 --jobs 参数用于并行恢复

PostgreSQL 13 及以上版本 ​

  • 改进了 COPY 命令的性能
  • 支持 wal_compression 参数，减少 WAL 日志大小
  • 改进了并行查询的性能

PostgreSQL 14 及以上版本 ​

  • 改进了 pg_restore 的并行恢复性能
  • 支持 --verbose 参数，显示更详细的恢复信息
  • 支持 --single-transaction 参数，使用单个事务恢复

PostgreSQL 15 及以上版本 ​

  • 改进了逻辑复制的性能
  • 支持 pg_stat_wal 视图，提供更详细的 WAL 统计信息
  • 支持 --zstd 参数，使用 Zstandard 压缩算法

数据导入最佳实践 ​

1. 提前规划 ​

  • 评估数据量和导入时间
  • 选择合适的导入工具
  • 准备目标数据库环境
  • 制定导入计划和回滚策略

2. 测试导入 ​

  • 在测试环境中进行导入测试
  • 测量导入时间和资源使用情况
  • 验证数据完整性
  • 调整导入策略

3. 优化配置 ​

  • 调整 PostgreSQL 配置，提高导入速度
  • 优化存储配置
  • 调整系统资源限制

4. 监控导入过程 ​

  • 监控导入进度
  • 监控系统资源使用情况
  • 记录导入日志
  • 及时处理导入过程中的问题

5. 验证数据完整性 ​

  • 导入完成后验证数据完整性
  • 运行数据一致性检查
  • 收集统计信息
  • 测试应用功能

配置验证和测试 ​

1. 验证 PostgreSQL 配置 ​

sql
-- 检查导入相关配置
SELECT name, setting FROM pg_settings WHERE name IN (
  'shared_buffers', 'double_write', 'checkpoint_timeout', 'max_wal_size',
  'maintenance_work_mem', 'synchronous_commit', 'wal_level', 'full_page_writes'
);

2. 测试导入速度 ​

bash
# 使用 pgbench 生成测试数据
pgbench -i -s 100 postgres

# 测试不同导入方法的速度
time pg_dump -U postgres -d pgbench -F c -f /tmp/pgbench.dump
time pg_restore -U postgres -d pgbench_test -j 4 /tmp/pgbench.dump

# 测试 COPY 命令导入速度
time psql -U postgres -d pgbench -c "COPY pgbench_accounts TO '/tmp/accounts.csv' WITH (FORMAT csv);"
time psql -U postgres -d pgbench_test -c "COPY pgbench_accounts FROM '/tmp/accounts.csv' WITH (FORMAT csv);"

3. 验证数据完整性 ​

sql
-- 比较源数据库和目标数据库的表行数
SELECT 
  (SELECT COUNT(*) FROM source_db.public.table_name) AS source_count,
  (SELECT COUNT(*) FROM target_db.public.table_name) AS target_count;

-- 检查数据一致性
SELECT * FROM source_db.public.table_name 
EXCEPT 
SELECT * FROM target_db.public.table_name;

通过遵循本指南，可以高效地完成 PostgreSQL 初始数据导入，确保数据完整性和导入性能。