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