pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCopying large tables with DBLink
4+ messages / 4 participants
[nested] [flat]
* Copying large tables with DBLink
@ 2005-03-24 18:59 Chris Hoover <revoohc@sermonaudio.com>
2005-03-24 19:21 ` Re: Copying large tables with DBLink Joe Conway <mail@joeconway.com>
2005-03-24 19:40 ` Re: Copying large tables with DBLink Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 19:50 ` Re: Copying large tables with DBLink Michael Fuhr <mike@fuhr.org>
0 siblings, 3 replies; 4+ messages in thread
From: Chris Hoover @ 2005-03-24 18:59 UTC (permalink / raw)
To: pgsql-admin
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 <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? 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying large tables with DBLink
2005-03-24 18:59 Copying large tables with DBLink Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-24 19:21 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2005-03-24 19:21 UTC (permalink / raw)
To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin
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.
> 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?
dblink just uses libpq, and libpq reads the entire result into memory.
There is no direct way around that that I'm aware of. You could,
however, use a cursor, and fetch/manipulate rows in more reasonably
sized groups.
HTH,
Joe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying large tables with DBLink
2005-03-24 18:59 Copying large tables with DBLink Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-24 19:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-03-24 19:40 UTC (permalink / raw)
To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin
"Chris Hoover" <revoohc@sermonaudio.com> writes:
> 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.
You're not going to be able to do that with dblink, nor any other
set-returning function, because the current implementation of SRFs
always materializes the entire function result in temporary memory/swap.
Consider something like
pg_dump -t srctab srcdb | psql destdb
instead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Copying large tables with DBLink
2005-03-24 18:59 Copying large tables with DBLink Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-24 19:50 ` Michael Fuhr <mike@fuhr.org>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-03-24 19:50 UTC (permalink / raw)
To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-24 19:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-24 18:59 Copying large tables with DBLink Chris Hoover <revoohc@sermonaudio.com>
2005-03-24 19:21 ` Joe Conway <mail@joeconway.com>
2005-03-24 19:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-24 19:50 ` Michael Fuhr <mike@fuhr.org>
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