PostgreSQL – 如何恢复非常大的数据库

前端之家收集整理的这篇文章主要介绍了PostgreSQL – 如何恢复非常大的数据库前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
当我尝试在我的devel数据库节点上恢复大型数据库(几乎32Go自定义格式)时,我遇到了一个小问题(这个节点的RAM,cpu比我的生产服务器少).

我的数据库转储是使用类似于以下命令生成的:

pg_dump -F custom -b myDB -Z 9 > /backup/myDB-`date +%y%m%d`.pg91

当我恢复它时,我使用以下命令:

pg_restore -F custom -j 5 -d myDB /backup/myDB-20130331.pg91

但是在这里,每次恢复命令失败时都会出现如下错误

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.

当我检查我的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

这很奇怪,我的postgresql服务器因为我的恢复而单独“重新启动”.
我尽量减少作业的数量(-j 5选项),但仍然遇到同样的问题.
但是,在具有更好规格的节点上,恢复此数据库没有问题.
我不确定但是我的索引的更新(其中一个真的太大)可能是理解这个问题的线索?

所以我有一些问题:有没有更好的方法来恢复真正的大型数据库?我的pg_restore命令中是否遗漏了一些内容?可能是我的devel服务器的设置太低了?

任何线索将不胜感激.
预先感谢.

环境:Postgresql 9.1(通过Debian软件包安装)

对于这种大型工作,建议在恢复过程中禁用autovacuum(通过在postgresql.conf中将其设置为off).

它似乎终于适合我了.

猜你在找的Postgre SQL相关文章