pg_restore out of memory

From: Francisco Reyes <lists(at)stringsutils(dot)com>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore out of memory
Date: 2007-06-14 20:17:51
Message-ID: cone.1181852271.597717.38963.5001@35st.simplicato.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am trying to restore a file that was done with pg_dump -Fc
pg_dump on a postgreql 8.1.4 machine
pg_restore on a postgresql 8.2.4 machine.

The restore machine has the following settings.
/etc/sysctl.conf (FreeBSD machine)
kern.ipc.shmall=262144
kern.ipc.shmmax=534773760 #510MB
kern.ipc.semmap=256

/boot/loader.conf
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
kern.maxdsiz="1600MB" #1.6GB
kern.dfldsiz="1600MB" #1.6GB
kern.maxssiz="128M" # 128MB

shared_buffers = 450MB
temp_buffers = 8MB
work_mem = 8MB # min 64kB
maintenance_work_mem = 64M
max_fsm_pages = 5000000 #Had error with 100,000 and increased
to 5Million while trying the pg_restore

OS can see 3.5GB of RAM.
Swap is 20GB.

The error that I got was:
pg_restore: [archiver (db)] Error from TOC entry 2146; 0 988154 TABLE DATA
message_attachments pgsql
pg_restore: [archiver (db)] COPY failed: ERROR: out of memory
DETAIL: Failed on request of size 134217728 (128MB)

Syslog was:
Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory
Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of size 134217728.

I see a simmilar thread back in December 2006.
http://tinyurl.com/3aa29g

However i don't see a final resolution.

Which parameter do I need to increase?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-06-14 20:43:10 Re: pg_restore out of memory
Previous Message Mason Hale 2007-06-14 20:15:03 Hash Aggregate plan picked for very large table == out of memory