On T, 2005-03-29 at 11:22 -0500, Christopher Browne wrote:
> Rod Taylor wrote:
> 
> >
> >It takes me about 12 to 24 hours to copy the largest tables (that's
> >each, not collectively); so there are a number of blocks where it
> >rescans from transaction ID X to some larger number many times if I use
> >grouping sets less than 7000.

The real solution would probably be doing the initial copy without
indexes, especially the PK.

It can speed up things tremendously - I had a table, about 5 GB in size
with a 1.5GB index. the initial copy went on fairly well during the
first GB, which was copied in less than 30 min, but then it started to
progressively slow down. up to a point, where the whole copy took more
than 24 hours. The first time I tried to do the same thing between two
database on the same computer and it took over 48 hours.

when transferring the table manually, the dump/load cycle took about an
hour, with ~40 min being load from dump and 15 min building the index.

This was on Postgres 7.4.5, linux 2.4, dual Xeon 2.8GH, 4GB memory,
database on mirrored 72GB/15000rpm SCSI disks and pg_xlog on another
mirrored 36G/15k disk set.

-- 
Hannu Krosing <hannu at skype.net>