PostgreSQL - how to restore very large database

Question

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).

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:

   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 5 option) 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?

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?

Any clue will be greatly appreciated. Thank in advance.

env: PostgreSQL 9.1 (installed via Debian packages)

Solution

For this kind of big work, it is recommended to disable the autovacuum (by set it to off in your postgresql.conf) during the restoration process.

It seems it finally works for me.

Comments

[christianbundy]

This worked for me – can you provide a source (or more information) on why it's recommended to disable autovacuum, or what it's specifically doing that would crash the database? Thanks!

[Labynocle]

vaccum process could degrade the performance of your system (generate statistic, increase the I/O traffic...) so it's interesting to disable it to avoid this kind of problem. However, make vaccum is important to keep statistic uptodate but not necessary during the dump. Don't forget to manually launch it after or to enable the autovaccum daemon.

[andilabs]

@Labynocle could you clarify it is crucial during dump or restore because in answer you are talking about restore and in comment about dump, or you simply mean both? ;)

[Labynocle]

@andilabs in my case it was during the restore!

[Abhishek Gautam]

How to do this in PGadmin?