postgresql 使用索引转储 postgres 数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7610550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dump postgres data with indexes
提问by AndreDurao
I've got a Postgres 9.0 database which frequently I took data dumps of it.
我有一个 Postgres 9.0 数据库，我经常对它进行数据转储。
This database has a lot of indexes and everytime I restore a dump postgres starts background task vacuum cleaner(is that right?). That task consumes much processing time and memory to recreate indexes of the restored dump.
这个数据库有很多索引，每次我恢复转储 postgres 都会启动后台任务真空吸尘器（对吗？）。该任务会消耗大量处理时间和内存来重新创建已恢复转储的索引。
My question is:
我的问题是：
- Is there a way to dump the database data and the indexes of that database?
- If there is a way, will worth the effort (I meant dumping the data with the indexes will perform better than vacuum cleaner)?
- Oracle has some the "data pump" command a faster way to imp and exp. Does postgres have something similar?
- 有没有办法转储数据库数据和该数据库的索引？
- 如果有办法，是否值得付出努力（我的意思是用索引转储数据会比吸尘器表现得更好）？
- Oracle 有一些“数据泵”命令可以更快地进行imp 和exp。postgres 有类似的东西吗？
Thanks in advance, Andre
提前致谢，安德烈
回答by wildplasser
If you use pg_dump twice, once with --schema-only, and once with --data-only, you can cut the schema-only output in two parts: the first with the bare table definitions and the final part with the constraints and indexes. Something similar can probably be done with pg_restore.
如果您使用 pg_dump 两次，一次使用 --schema-only，一次使用 --data-only，您可以将仅模式输出分为两部分：第一部分带有裸表定义，最后一部分带有约束和索引。类似的事情可能可以用 pg_restore 来完成。
回答by Mike Sherrill 'Cat Recall'
Best Practice is probably to
最佳实践可能是
- restore the schema without indexes
- and possibly without constraints,
- load the data,
- then create the constraints,
- and create the indexes.
- 恢复没有索引的模式
- 并且可能没有限制，
- 加载数据，
- 然后创建约束，
- 并创建索引。
If an index exists, a bulk load will make PostgreSQL write to the database and to the index. And a bulk load will make your table statistics useless. But if you load data first, then create the index, the stats are automatically up to date.
如果索引存在，批量加载将使 PostgreSQL 写入数据库和索引。批量加载将使您的表统计信息无用。但是如果你先加载数据，然后创建索引，统计数据会自动更新。
We store scripts that create indexes and scripts that create tables in different files under version control. This is why.
我们存储创建索引的脚本和在版本控制下在不同文件中创建表的脚本。这就是为什么。
In your case, changing autovacuum settingsmight help you. You might also consider disabling autovacuum for some tables or for all tables, but that might be a little extreme.
在您的情况下，更改 autovacuum 设置可能会对您有所帮助。您也可以考虑为某些表或所有表禁用自动清理，但这可能有点极端。
