Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
"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 Mar 1 '13 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 Mar 1 '13 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 Mar 3 '13 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 Mar 4 '13 at 18:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.