PostgreSQL: 提升 pg_dump、pg_restore 性能

Question

刚开始的时候，我使用了默认的纯文本格式pg_dump。可我还不够明智。

通过研究，我发现使用pg_dump -Fc | gzip -9 -c > dumpfile.gz可以节省时间和文件大小。我恍然大悟。

当需要重新创建数据库时，

# create tablespace dbname location '/SAN/dbname';
# create database dbname tablespace dbname;
# alter database dbname set temp_tablespaces = dbname;

% gunzip dumpfile.gz              # to evaluate restore time without a piped uncompression
% pg_restore -d dbname dumpfile   # into a new, empty database defined above

我感觉不得要领：恢复过程花了12个小时来创建数据库，而这只是它将来的一小部分。

# select pg_size_pretty(pg_database_size('dbname'));
47 GB

因为预计这个数据库将会有几个 TB 的数据量，所以我现在需要考虑提高性能。

请给我一点启示。

- Joe Creighton

Answer 1

首先检查您的磁盘设置是否具有合理的IO性能。然后检查您的PostgreSQL安装是否适当调整。特别是shared_buffers应该正确设置，maintenance_work_mem在恢复期间应该增加，full_page_writes在恢复期间应该关闭，wal_buffers在恢复期间应该增加到16MB，checkpoint_segments在恢复期间应该增加到类似于16的值，您不应该记录任何不合理的日志（例如记录每个执行的语句），auto_vacuum在恢复期间应该禁用。

如果您使用的是8.4版本，还可以尝试并行恢复，使用pg_restore的--jobs选项。

- Ants Aasma

---

如果您已连接了从节点，并且主节点的负载已经相当大，那么您可能希望仅在从节点上执行备份。特别是因为从节点是只读的，我想这也可能会在某种程度上有所帮助。在一个大型集群中，如果备份需要很长时间，那么专门为交错备份分配一个或多个从节点可能会有所帮助。为了不漏掉任何东西，您需要通过流复制使这些待机节点连接，以便它们从主节点的WAL中写入。 - Michael M

"shared_buffers should be set correctly" 意思是需要正确设置共享缓冲区。 - Juan Carlos Oropeza

@JuanCarlosOropeza — 我发现了关于shared_buffers的文档，可能会有所帮助。 - Darragh Enright

Answer 2

改进pg dump&restore

PG_DUMP | 总是使用format-directory和-j选项

time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | 始终在 postgres.conf 和 format-directory 中使用调整和 -j 选项

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/

- Yanar Assaf

---

这里使用的配置参数显著提高了性能。 - ramnar

链接失效。 - Hamed

哇！这帮了我很多忙！谢谢！ - stasdeep

同时，将这些参数进行更改对我帮助很大，如将 maintenance_work_mem 设置为 4GB，max_wal_size 设置为 4GB。pg_restore 的时间从90分钟减少到20分钟。 - undefined

Answer 3

两个问题/想法:

1. 通过指定 -Fc，pg_dump输出已经被压缩。虽然这种压缩不是最大的，所以您可能会发现使用 "gzip -9" 可以节省一些空间，但我敢打赌它不足以证明额外的时间（和I/O）用于压缩和解压缩备份的 -Fc 版本。

2. 如果您正在使用PostgreSQL 8.4.x，则可以使用新的pg_restore命令行选项 "-j n" 来加速从 -Fc 备份恢复，其中 n=用于还原的并行连接数。这将允许pg_restore同时加载多个表的数据或生成多个索引。

- Matthew Wood

---

我们目前的版本是8.3；有新的升级理由。 - Joe Creighton

您可以使用8.4版本的pg_restore与8.3版本的服务器配合使用。只需确保您使用8.3的pg_dump即可。 - Magnus Hagander

我们被困在8.3版本，因为我们使用Solaris10包安装的Postgres，"目前没有计划将PG8.4集成到S10中。" [参考：http://www.mail-archive.com/[e-mail]/msg136829.html]我必须承担安装和维护开源postgres的任务。不确定我们是否能在这里做到这一点...唉。 - Joe Creighton

Answer 4

我猜你需要备份，而不是对数据库进行重大升级。

对于大型数据库的备份，你应该设置持续归档，而不是使用pg_dump命令。

1. 设置WAL归档。

2. 例如，使用以下命令每天进行基本备份：

   psql template1 -c "select pg_start_backup('`\`date +%F-%T\``')"
   rsync -a --delete /var/lib/pgsql/data/ /var/backups/pgsql/base/
   psql template1 -c "select pg_stop_backup()"
   

恢复过程非常简单，只需从备份位置恢复数据库和不早于pg_start_backup时间的WAL日志，然后启动Postgres即可。这样会更快。

- Tometzky

---

我们没有考虑PITR（WAL归档），因为系统的事务量不是很大，但会保留许多历史记录。然而，现在我想起来了，“增量”备份可能会有所帮助。我会进行调查。谢谢。 - Joe Creighton

Answer 5

zcat dumpfile.gz | pg_restore -d db_name

优化后可以避免将未压缩的数据完整写入磁盘，这是目前的瓶颈。

- richo

Answer 6

通过压缩备份可以加速性能，这表明您的备份受到 I/O 限制。备份通常都会受到 I/O 限制，这并不奇怪。压缩数据会将 I/O 负载转换为 CPU 负载，由于大多数 CPU 在大量数据传输期间处于空闲状态，因此压缩后总体上是一个优势。

因此，要加快备份/恢复时间，您需要更快的 I/O。除了重新组织数据库以避免成为一个巨型单一实例，这基本上就是您能做的全部了。

- Will Hartung

---

如果只优化pg_dump时间，使用v9.3的并行转储，压缩>0可能会造成很大的影响！这是因为pg_dump和postmaster进程已经占用了足够多的CPU资源，加上压缩>=1会使整个任务明显变成CPU密集型而不是I/O密集型。基本上，旧的假设认为在没有压缩的情况下CPU是空闲的，在并行转储中是无效的。 - Asclepius

Answer 7

如果您在使用pg_restore时遇到速度问题，请检查您是否使用了INSERT或COPY语句来转储数据。

如果您使用INSERT（调用pg_dump时使用--column-inserts参数），则数据恢复将明显变慢。

使用INSERT适用于创建加载到非Postgres数据库中的转储。但是，如果要还原到Postgres，请在使用pg_dump时省略使用--column-inserts参数。

- michal.jakubeczy