pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: Chris Hoover <revoohc@sermonaudio.com>
Cc: PostgreSQL Admin <pgsql-admin@postgresql.org>
Subject: Re: Copying large tables with DBLink
Date: Thu, 24 Mar 2005 12:50:59 -0700
Message-ID: <20050324195059.GA13146@winnie.fuhr.org> (raw)
In-Reply-To: <42430E20.1010600@sermonaudio.com>
References: <42430E20.1010600@sermonaudio.com>

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 <table> 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/



view thread (4+ messages)

Message-ID: <20050324195059.GA13146@winnie.fuhr.org>
Permalink:  ../20050324195059.GA13146@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050324195059.GA13146@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, revoohc@sermonaudio.com
  Subject: Re: Copying large tables with DBLink
  In-Reply-To: <20050324195059.GA13146@winnie.fuhr.org>
  References: <42430E20.1010600@sermonaudio.com> <20050324195059.GA13146@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox