X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 60D1D534AB for ; Thu, 24 Mar 2005 19:51:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46865-02 for ; Thu, 24 Mar 2005 19:51:03 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 810EB53457 for ; Thu, 24 Mar 2005 19:51:02 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j2OJp0Ed034755 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 24 Mar 2005 12:51:03 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j2OJp0nI013293; Thu, 24 Mar 2005 12:51:00 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j2OJp0O8013292; Thu, 24 Mar 2005 12:51:00 -0700 (MST) (envelope-from mfuhr) Date: Thu, 24 Mar 2005 12:50:59 -0700 From: Michael Fuhr To: Chris Hoover Cc: PostgreSQL Admin Subject: Re: Copying large tables with DBLink Message-ID: <20050324195059.GA13146@winnie.fuhr.org> References: <42430E20.1010600@sermonaudio.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42430E20.1010600@sermonaudio.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/404 X-Sequence-Number: 16902 On Thu, Mar 24, 2005 at 01:59:44PM -0500, Chris Hoover wrote: > > Has anyone had problems with memory exhaustion and dblink? We were > trying to use dblink to convert our databases to our new layout, and had > our test server lock up several times when trying to copy a table that > was significantly larger than our memory and swap. Hmmm...doesn't dblink use libpq, and doesn't libpq fetch the entire result set before doing anything with it? If so, then that could explain the memory exhaustion. > Basically where were doing an insert into select * from > dblink('dbname=olddb','select * from large_table) as t_large_table(table > column listing); > > Does anyone know of a way around this? How about using pg_dump to dump the original table and restore it into the new table? If you just want the table's contents without the table definition then you could use the -a (--data-only) option. Another possibility would be to write a function that uses a cursor: dblink_open() and a loop that calls dblink_fetch() until you reach the end of the result set. I think that wouldn't have a memory exhaustion problem (but test it to be sure). -- Michael Fuhr http://www.fuhr.org/~mfuhr/