PostgreSQL – 如何恢复非常大的数据库
发布时间:2020-12-13 16:18:57 所属栏目:百科 来源:网络整理
导读:当我尝试在我的devel数据库节点上恢复大型数据库(几乎32Go自定义格式)时,我遇到了一个小问题(这个节点的RAM,CPU比我的生产服务器少). 我的数据库转储是使用类似于以下命令生成的: pg_dump -F custom -b myDB -Z 9 /backup/myDB-`date +%y%m%d`.pg91 当我恢
当我尝试在我的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服务器因为我的恢复而单独“重新启动”. 所以我有一些问题:有没有更好的方法来恢复真正的大型数据库?我的pg_restore命令中是否遗漏了一些内容?可能是我的devel服务器的设置太低了? 任何线索将不胜感激. 环境:PostgreSQL 9.1(通过Debian软件包安装)
对于这种大型工作,建议在恢复过程中禁用autovacuum(通过在postgresql.conf中将其设置为off).
它似乎终于适合我了. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |