复制PostgreSQL数据库的更快方法（或最佳方法）

Question

我对一个数据库使用了pg_dump命令，现在正试图将生成的.sql文件安装到另一台服务器上。

我正在使用以下命令。

psql -f databasedump.sql

今天早些时候我启动了数据库安装程序，现在已经过去了7个小时，数据库仍在填充中。我不知道这需要多长时间，但我会继续监控它，到目前为止我已经看到了超过1200万条插入记录。我怀疑是否有更快的方法。

- David Bain

---

无论如何，1200万次插入，即使使用psql和纯SQL转储，在像样的硬件上通常只需要大约一分钟的时间。如果花费了7个小时，那么您的设置肯定有问题。 - Erwin Brandstetter

这可能很明显，但请确保您没有在转储中提供跨数据库兼容选项 --inserts 或 --column-inserts。您还可以检查您的 ASCII 脚本是否使用 COPY 来重新加载数据。 - Andrew Lazarus

更多信息，我正在使用一个EC2微实例，因此内存受到一定限制。它是Postgresql 7.4（必须自己编译），因为数据来自旧的7.4设置。 - David Bain

可能在更快的硬件上进行还原，对数据库进行完整的VACUUM操作，然后进行文件复制到EC2，例如使用rsync。 - Jasen

我曾经遇到过同样的问题（使用ASP.Net Core），后来发现我的日志记录严重拖慢了速度。一个美国邮政编码表的导入只需要几秒钟。 - LuvForAirplanes

Answer 1

请问是否需要将 "dumps" 翻译为中文？如果需要，它的上下文是什么？

pg_dump -Fc -Z 9  --file=file.dump myDb

Fc

输出适合输入到 pg_restore 的自定义归档文件。这是最灵活的格式，因为它允许重新排序加载数据以及对象定义。此格式还默认进行压缩。

Z 9: --compress=0..9

指定要使用的压缩级别。零表示不压缩。对于自定义归档格式，这会指定对单个表数据段进行压缩，默认情况下压缩级别适中。对于纯文本输出，将非零压缩级别设置为整个输出文件都被压缩，就像它已经通过 gzip 处理过一样；但默认情况下不进行压缩。tar 归档格式目前不支持压缩。

并使用以下命令进行恢复：

pg_restore -Fc -j 8  file.dump

-j: --jobs=number-of-jobs

使用多个并发作业来运行pg_restore中耗时的部分，例如加载数据、创建索引或创建约束。该选项可以大大减少将大型数据库恢复到多处理器计算机上运行的时间。

每个作业都是一个进程或一个线程，取决于操作系统，并使用与服务器的单独连接。

此选项的最佳值取决于服务器、客户端和网络的硬件设置。因素包括CPU核心数和磁盘设置。一个好的起点是服务器上的CPU核心数，但在许多情况下，比该值更大的值也可能导致更快的恢复时间。当然，过高的值会因为抖动而导致性能下降。

仅自定义和目录归档格式支持此选项。输入必须是常规文件或目录（例如不是管道）。在发出脚本而不是直接连接到数据库服务器时，此选项将被忽略。此外，不能将多个作业与选项--single-transaction一起使用。

链接：

pg_dump

pg_restore

- mullerivan

---

有时，关闭压缩（-Z0）后使用 pg_dump 可以更快地执行。显然，此时转储文件会更大。但如果有足够的空间和快速的磁盘，这可能是正确的权衡选择。 - mivk

如果可能的话，使用外部压缩器性能会更好。pg_dump ... -Fc -Z0 | pigz > file.dump.gz对我而言比内置压缩快大约三倍，可能是因为内置压缩是单线程的原因？ - Fake Name

补充mikv的评论：虽然从45Mo到5.4Go，但在我的SSD上速度快了5倍。现在时间比大小更重要（哈哈）。 - Olivier Pons

问题要求“更快的方式”，因此答案不应使用最大的压缩。 - fjsj

它能与pg_dumpall一起使用吗？ - postgresnewbie

Answer 2

改进pg dump&restore

PG_DUMP | 始终使用带有-j选项的格式目录。

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

PG_RESTORE | 总是使用格式目录的 postgres.conf 调优选项，带有 -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/`

了解更多信息

https://gitlab.com/yanar/Tuning/wikis/improve-pg-dump&restore

- Yanar Assaf

---

有些人可能会觉得在您的代码库中比较“PG_DUMP 9.6 Fc格式与Fd格式”很奇怪，但后者会获得额外的-j 8参数。也许值得添加一条注释，“并行备份仅支持目录格式”。此外，如果您还报告Fc/Fd比较的结果文件大小，那就太好了。 - Anatoly Alekseev

Answer 3

为什么你在生成一个原始的.sql备份? pg_dump 的开头描述建议使用 “自定义” 格式-Fc。

然后您可以使用 pg_restore 来还原数据（或选择其中的部分）。有一个“作业数”选项-j，它可以使用多个核心（假设您的磁盘不是限制因素）。在大多数情况下，在现代机器上，您可以期望至少获得一些性能提升。

现在您说“我不知道这应该花多长时间”。好吧，在进行了几次还原之前，您不会知道。确保监视系统正在执行什么操作以及您是否受到 CPU 或磁盘 I/O 的限制。

最后，您要为恢复数据库设置的配置设置不是您要运行它的设置。以下是一些有用的起点：

1. 增加maintenance_work_mem，以便您可以更大批量地构建索引
2. 在还原期间关闭fsync。如果您的机器崩溃，您将重新开始所有操作。

但请记得在还原之后重置它们。

- Richard Huxton

---

这是非常有用的信息。虽然我以前使用过Postgresql，但显然我对它一无所知。我发现你的反馈非常启发人。 - David Bain

使用psql大约需要9个小时。我想使用pg_restore测试一下，我应该只运行pg_restore还是最好清除我的数据目录并从头开始（这是一个测试盒，所有关键数据都在实际盒上）？ - David Bain

你需要一个新的转储文件（-Fc），然后如果你有足够的磁盘空间，可以选择在不同的名称下恢复数据库。这次计划监控它 - 索引往往比表数据花费更长时间。你可能会发现创建一个较小的测试数据库（相同结构）并多次转储/恢复它，以便更好地了解事情如何交互会很有用。 - Richard Huxton

Answer 4

通常建议使用pg_dump与pg_restore一起使用，而不是psql。可以通过传递--jobs标志将此方法分为多个核心以加速加载过程：

    $ pg_dump -Fc db > db.Fc.dump
    $ pg_restore -d db --jobs=8 db.Fc.dump

Postgres自己有一个关于批量加载数据的指南。

我还建议您对postgresql.conf配置文件进行大量调整，并适当地设置maintenance_work_mem和checkpoint_segments值的高值；这些值越高，可能会显著提高写入性能。

- hoxworth

---

请不要发布明显错误的信息。如果您不知道问题的答案，请不要回答。您的答案不仅不正确，而且会使任何遵循它的人生活更加困难，而不是更容易。 - Richard Huxton

抱歉，我发完回答后才意识到它是错误的。我已经尽力更新了答案，但如果我的对pg_restore的理解和使用不正确，我将简单地删除这个答案。 - hoxworth

您修改后的回答更加实用，我已经取消了踩。我想删除我的评论，但是我认为这个对话链就没什么意义了。 - Richard Huxton

明白了，谢谢 - 我的思维一直被分区和跨多个连接加载数据所占据，以至于我在第一次回答时有点唠叨。今天过得真长... - hoxworth