“custom archiver out of memory” error when restoring large DB using pg_restore

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: Raw Message | Whole Thread | 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?

Browse pgsql-general by date

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