| From: | Jordan Glassman <jordanglassman(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | “custom archiver out of memory” error when restoring large DB using pg_restore | 
| Date: | 2013-03-01 19:13:24 | 
| Message-ID: | CA+JPJoe4ReNs_8PscLVWY2euAE-nP3PEdohsJnrmqc7FQGrsdg@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Misa Simic | 2013-03-01 22:07:54 | Re: Poor performance when using a window function in a view | 
| Previous Message | JD Wong | 2013-03-01 16:50:26 | Re: broke postgres, how to fix?? |