I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result using 9.1.5 and 9.1.7 under OS X 10.8.

Using the command:

pg_restore --clean --no-acl --no-owner -d db_name -U username -h localhost -v db.dump

I get the following output on the command line ("cleaning" commands omitted) after 20 minutes and seeing the DB size grow to about 30 GB:

pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating TABLE video_data
pg_restore: creating SEQUENCE video_data_id_seq
pg_restore: creating SEQUENCE OWNED BY video_data_id_seq
pg_restore: executing SEQUENCE SET video_data_id_seq
pg_restore: creating DEFAULT id
pg_restore: restoring data for table "video_data"
pg_restore(11938) malloc: *** mmap(size=18446744071605936128) failed (error code=12)
*** error: can't allocate region
*** set a breakpoint in malloc_error_break to debug
pg_restore: [custom archiver] out of memory
pg_restore: *** aborted because of error

Turning up log level to DEBUG1 reveals the error:

...(many of these checkpoints hints)...
LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".
ERROR:  extra data after last expected column
CONTEXT:  COPY video_data, line 463110542: "3599794500:59:01497 797942  2012    23422-08-96 2400892 08.794219   08 0617 2342118769-08 72    115 2046225 1..."
STATEMENT:  COPY video_data (id, video_id, views, favorites, comments, created_at, likes, dislikes) FROM stdin;

I have tried increasing SHMMAX and SHMALL to 2GB, checkpoint_segments to 30, as well as upgrading PG to match patchlevels. A search reveals a small handful of people with this problem several years back, and no resolution.

Any recommendations?

  • "extra data after..." - this means malformed line in the dump. is it really your data? can you locate this offending row in the source database?
    – filiprem
    Commented Mar 1, 2013 at 12:58
  • There is nothing wrong with the record with id = 463110542, but all of those numbers after line 463110542: above don't look like my data. Do you know how to interpret that line number?
    – jordanpg
    Commented Mar 1, 2013 at 17:53
  • This error says "463110542nd line after COPY video_data ... statement inside the dump is broken". You can try to convert the dump to a text file with pg_restore --clean --no-acl --no-owner -v db.dump -f db.txt. BTW - did you notice this big number is exactly 16 exabytes? This might be related to a bug in operating system / libs (google for similar bug reports).
    – filiprem
    Commented Mar 3, 2013 at 17:41
  • This problem was fixed with another dump, which, coupled with the "extra data" messages, suggests that the original one was corrupt. The "out of memory" message was a red herring. Thanks for your help, @filiprem
    – jordanpg
    Commented Mar 4, 2013 at 18:35


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.