Thread: Out of memory error on pg_restore

Out of memory error on pg_restore

From
"Nik"
Date:
I am running PostgreSQL 8.1.3 on Windows 2003 Server.

I am trying to transfer the data from a table in db1on one machine to a
table in db2 on a different machine. The table size is about 22Gb
(about 280 million rows).

I was trying to do it by generating a backup file of the table in db1
and restoring it to db2. First, I created a plain 22Gb SQL dump and
tried inserting it via psql. After some time it failed with the
following error:

psql: ERROR: out of memory
DETAIL: Failed on request of size 32.

I tried creating a compressed backup file (about 800Mb) and restoring
it using pg_restore but I got the following error:

pg_restore: ERROR:  out of memory
DETAIL:  Failed on request of size 32.
CONTEXT:  COPY lane_data, line 17345022: "<line of data goes here>"
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error

So it seems like I am getting the same error in both cases.

What is the cause? How could I adjust the restoring process or database
parameters to handle this transfer? Is there a better way to transfer
this data?

Thanks.


Re: Out of memory error on pg_restore

From
Richard Huxton
Date:
Nik wrote:
> I am running PostgreSQL 8.1.3 on Windows 2003 Server.
>
> I am trying to transfer the data from a table in db1on one machine to a
> table in db2 on a different machine. The table size is about 22Gb
> (about 280 million rows).
>
> I was trying to do it by generating a backup file of the table in db1
> and restoring it to db2. First, I created a plain 22Gb SQL dump and
> tried inserting it via psql. After some time it failed with the
> following error:
>
> psql: ERROR: out of memory
> DETAIL: Failed on request of size 32.

And were you out of memory or not?
If nothing else, task-manager should show how much memory everything is
using.

--
   Richard Huxton
   Archonet Ltd

Re: Out of memory error on pg_restore

From
Tom Lane
Date:
"Nik" <XLPizza@gmail.com> writes:
> pg_restore: ERROR:  out of memory
> DETAIL:  Failed on request of size 32.
> CONTEXT:  COPY lane_data, line 17345022: "<line of data goes here>"

A COPY command by itself shouldn't eat memory.  I'm wondering if the
table being copied into has any AFTER triggers on it (eg for foreign key
checks), as each pending trigger event uses memory and so a copy of a
lot of rows could run out.

pg_dump scripts ordinarily load data before creating triggers or foreign
keys in order to avoid this problem.  Perhaps you were trying a
data-only restore?  If so, best answer is "don't do that".  A plain
combined schema+data dump should work.

            regards, tom lane

Re: Out of memory error on pg_restore

From
"Moises Alberto Lindo Gutarra"
Date:
other way is to set
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session
Manager\Memory Management
bigger values

but to restore a lot of data on windows take so many time

2006/3/8, Tom Lane <tgl@sss.pgh.pa.us>:
> "Nik" <XLPizza@gmail.com> writes:
> > pg_restore: ERROR:  out of memory
> > DETAIL:  Failed on request of size 32.
> > CONTEXT:  COPY lane_data, line 17345022: "<line of data goes here>"
>
> A COPY command by itself shouldn't eat memory.  I'm wondering if the
> table being copied into has any AFTER triggers on it (eg for foreign key
> checks), as each pending trigger event uses memory and so a copy of a
> lot of rows could run out.
>
> pg_dump scripts ordinarily load data before creating triggers or foreign
> keys in order to avoid this problem.  Perhaps you were trying a
> data-only restore?  If so, best answer is "don't do that".  A plain
> combined schema+data dump should work.
>
>                        regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Re: Out of memory error on pg_restore

From
"Nik"
Date:
Yes, I was indeed out of memory. That is the problem: the postgres.exe
process corresponding to the pg_restore continuously consumes more and
more memory until it runs out and fails with the mentioned error. Since
I already have 4Gb of RAM, throwing more hardware at it is not a
feasible solution, so I was wondering what else can I do to complete
this process without running out of memory.


Re: Out of memory error on pg_restore

From
"Nik"
Date:
Tom was exactly right.

I was trying to restore the dump file into an already created table
structure that did have three foreign key constraints. I removed the
primary key constraint to speed up the load, but was not aware of the
memory usage of the foreign keys.

I dropped the table and ran the pg_restore again, and it is running
currently and behaving nicely at constant 13Mb of memory. It will
probably take some time, but I think it is working fine.

Thanks for the help.


Re: Out of memory error on pg_restore

From
"Guillaume Lelarge"
Date:
8 Mar 2006 07:31:19 -0800, Nik <XLPizza@gmail.com>:
> [...]
> psql: ERROR: out of memory
> DETAIL: Failed on request of size 32.
>

I also have this kind of error (out of memory) during the restoration
of objects on my database. I use a 8.1.2 pg_dump on a 7.1.1 PostgreSQL
server. Size of the dump is approx 20GB. I restore it using the 8.1.2
pg_restore on a 8.1.2 PostgreSQL server. And I finaly receive a "out
of memory" error. I don't think there is trigger on pg_largeobject.

What can be the cause of this one ? I will try with the 8.1.3 release
but I don't see a related fix on the release notes.

Thanks.


--
Guillaume.