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 7EE845343F for ; Thu, 24 Mar 2005 18:59:08 +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 29873-03 for ; Thu, 24 Mar 2005 18:58:59 +0000 (GMT) Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 46E3953472 for ; Thu, 24 Mar 2005 18:58:57 +0000 (GMT) thread-index: AcUwo93iFUqy/3cMTJinRcLRRfwi0g== Received: from [10.4.13.50] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Thu, 24 Mar 2005 14:01:19 -0500 Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Message-ID: <42430E20.1010600@sermonaudio.com> Date: Thu, 24 Mar 2005 13:59:44 -0500 From: "Chris Hoover" User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "PostgreSQL Admin" Subject: Copying large tables with DBLink Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 24 Mar 2005 19:01:21.0000 (UTC) FILETIME=[DDDD5680:01C530A3] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/401 X-Sequence-Number: 16899 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. 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? The problem we were trying to solve is the fact that due to new column additions that are populated during the conversion, our db's are doubling in space. This is requiring lengthy vacuum fulls to reclaim the space and making for a very long conversion time. We are very concerned that this system lockup could happen on our production boxes since we have several db's that are very large and probably have tables larger than our memory on the systems. Anyway, any ideas on how to get around this, or how we might speed it up and not use so much space would be appreciated. Chris PG 7.3.4