I have some historic data that I want to analyze. To do this I set up postgres on a spare box I picked up for cheap, which just lucked into having tons of RAM (1.5G). I set up postgres to use 10000 buffers, and recompiled the kernel to allow 2Gb data size limit per process. Since this is historical data, I'm actually merging a couple of dumps that span the time range. I've dealt with eliminating any conflicting data (ie, clashing unique keys) but I'm not 100% sure that the foreign key constraints are all met. Thus, when loading the data from the second dump, I am leaving the FK triggers on. Now, this is where my trouble has begun... On importing row 29,796,801 for the first big table, I get this (after 27 hours!): pg_restore: ERROR: out of memory DETAIL: Failed on request of size 44. CONTEXT: COPY msg_recipients, line 29796801: "75312 3434358 sent" pg_restore: [archiver (db)] error returned by PQendcopy (I had originally tried this with a 1Gb data size limit on the process, and it died at around row 15 million.) The curious thing is that watching the process size grow on another window, is that it shrunk considerably a few minutes before it croaked: % ps axuw | grep postg pgsql 26032 6.0 7.9 2186888 123440 ?? D Thu09AM 122:26.10 postmaster: khera vkmlm [local] COPY (postgres) Note here that we are using nearly 2gb virtual memory (I'm sure a bunch of that is stack and the shared memory segment). a few minutes later I saw this: % ps axuw | grep postg pgsql 26032 10.4 5.5 91840 85624 ?? S Thu09AM 123:17.24 postmaster: khera vkmlm [local] idle (postgres) note the process size is down to 91M. A few minutes later I got the out of memory error. This is very curious because I don't expect the process to release the memory back to the OS like that. There are about 157 million records in this table, three columns wide. FreeBSD 4.10-PRERELEASE, PostgreSQL 7.4.2 So, is there any way to load this in or do I need to either break it into chunks (no small feat) or disable triggers during load and hope and pray there are no FK violations? Is there some leak or does it just take that much RAM to load in data from a table? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
