使用索引导出PostgreSQL数据

Question

我有一个Postgres 9.0数据库，经常需要对其进行数据转储。这个数据库有很多索引，每次恢复转储时，postgres都会启动后台任务“vacuum cleaner”（是这样吗？）。该任务消耗大量处理时间和内存来重新创建恢复的转储的索引。

我的问题是：

1. 是否有一种方法可以同时转储数据库数据和索引？ 2. 如果有一种方法，这样做是否值得（我指的是将数据与索引一起转储比使用vacuum cleaner更好）？ 3. Oracle有一些“数据泵”命令，可以更快地执行imp和exp。Postgres是否有类似的东西？

谢谢！ Andre

- AndreDurao

---

适合在http://dba.stackexchange.com/上提问的好问题。 - Luc M

当使用pg_dump时，无法保留索引数据--导入后必须重新构建索引。您可以简单地打包整个Postgres数据目录而不是执行pg_dump。 - Frank Farmer

谢谢Frank，我会尝试一下，也许这对我来说是最好的选择。我唯一担心的是不同的Postgres版本和操作系统之间的差异。 - AndreDurao

@FrankFarmer，将整个Data目录打包并移动到新服务器上，事情会“顺利进行”吗？如果旧服务器使用较旧的CentOS和PG 8.x，而新数据库则在新硬件堆栈上且带有PG 9.6？ - PKHunter

Answer 1

如果您使用pg_dump两次，一次使用--schema-only参数，一次使用--data-only参数，那么您可以将schema-only输出分为两部分：第一部分是裸表定义，最后一部分是约束和索引。 使用pg_restore可能也可以实现类似功能。

- wildplasser

---

这实际上应该接近被接受的答案，而PG文档本身也应该明确这一点。否则，在许多真实世界的情况下，转储和恢复是相当幼稚的。 - PKHunter

Answer 2

最佳实践可能是：

• 恢复没有索引的模式
• 可能不带约束条件
• 加载数据
• 然后创建约束条件
• 再创建索引

如果存在索引，则批量加载将使PostgreSQL写入数据库和索引。批量加载将使您的表统计信息无用。但是，如果您先加载数据，然后创建索引，则统计信息会自动更新。

我们将创建索引的脚本和创建表的脚本存储在版本控制下的不同文件中。这就是为什么。

在您的情况下，更改自动清理设置 可能会对您有所帮助。您还可以考虑禁用某些表或所有表的自动清理，但这可能有点极端。

- Mike Sherrill 'Cat Recall'

---

了解，这基本上就是pg_dump -> pg_restore所做的。 - Scott Marlowe

@ScottMarlowe：不过，OP只是在转储数据。 pg_dump --data-only 不包括 CREATE INDEX 语句。一个 --rebuild-indexes 选项会很方便。也许我会着手处理这个问题。 - Mike Sherrill 'Cat Recall'

这个 OP 完全没有明确是否正在使用 --data-only 转储数据。我猜这就是他们所说的“进行数据转储”的意思，但谁知道呢？不过，--rebuild-indexes 应该非常方便。 - Scott Marlowe