PostgreSQL - 如何恢复非常大的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15765606/
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
PostgreSQL - how to restore very large database
提问by Labynocle
I got a little problem when I try to restore a large database (almost 32Go in custom format) on my devel database node (this node has less RAM, CPU... than my production server).
当我尝试在我的开发数据库节点上恢复一个大型数据库（几乎是 32Go 的自定义格式）时，我遇到了一个小问题（这个节点的 RAM、CPU……比我的生产服务器少）。
My database dumps are generated with a command similar to:
我的数据库转储是使用类似于以下的命令生成的：
pg_dump -F custom -b myDB -Z 9 > /backup/myDB-`date +%y%m%d`.pg91
And when I restore it, I used the following command:
当我恢复它时，我使用了以下命令：
pg_restore -F custom -j 5 -d myDB /backup/myDB-20130331.pg91
But here, each time the restore command failed with an error like:
但是在这里，每次恢复命令失败并出现如下错误时：
pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_restore: [archiver] worker process failed: exit code 1
pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
And when I check my postgresql logs, I can read this:
当我检查我的 postgresql 日志时，我可以读到：
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2013-04-02 11:41:48 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 86/26F302B0
LOG: unexpected pageaddr 85/E3F52000 in log file 134, segment 38, offset 16064512
LOG: redo done at 86/26F51FC0
LOG: last completed transaction was at log time 2013-04-02 11:50:47.663599+00
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
It's quite strange, my postgresql server "restarts" alone just because of my restore.
I try to minimize the number of jobs (-j 5option) but still got the same problem.
However on a node with better specs, I have no problem to restore this database.
I'm not sure but maybe the updates of my indexes (one of them is really too large) could be a clue to understand this issue?
很奇怪，我的 postgresql 服务器仅仅因为我的恢复而“重新启动”。我尝试尽量减少工作数量（-j 5选项），但仍然遇到同样的问题。但是，在具有更好规格的节点上，恢复此数据库没有问题。我不确定，但也许我的索引的更新（其中一个真的太大了）可能是理解这个问题的线索？
So I have some questions: is there a better way to restore really large database? Do I miss something in my pg_restore command? May be the settings of my devel server are too low?
所以我有一些问题：有没有更好的方法来恢复非常大的数据库？我是否遗漏了 pg_restore 命令中的某些内容？可能是我开发服务器的设置太低了？
Any clue will be greatly appreciated. Thank in advance.
任何线索将不胜感激。预先感谢。
env: PostgreSQL 9.1 (installed via Debian packages)
环境：PostgreSQL 9.1（通过 Debian 软件包安装）
回答by Labynocle
For this kind of big work, it is recommended to disable the autovacuum(by set it to offin your postgresql.conf) during the restoration process.
对于这种大型工作，建议在恢复过程中禁用autovacuum（通过off在您的postgresql.conf 中设置）。
It seems it finally works for me.
看来它终于对我有用了。
