Re: Detach/attach table and index data files from one cluster to another

On 2013-04-12 12:14:24 -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > On 04/12/2013 10:15 AM, Tom Lane wrote:
> >> There's 0 chance of making that work, because the two databases wouldn't
> >> have the same notions of committed XIDs.
>
> > Yeah. Trying to think way outside the box, could we invent some sort of
> > fixup mechanism that could be applied to adopted files?
>
> Well, it wouldn't be that hard to replace XIDs with FrozenXID or
> InvalidXID as appropriate, if you had access to the source database's
> clog while you did the copying. It just wouldn't be very fast.

I think if one goes over the heap and hint bits everything (so the item
pointers don't have to be immediately rewritten), freeze everything and
such it should be doable at about disk speed unless you have a really
fast disk subsystem.
But it still is fairly complicated and I doubt its really necessary.

> I suppose it would still be faster than a COPY transfer, but I'm not
> sure it'd be enough faster to justify the work and the additional
> portability hits you'd be taking.

Using binary copy might already give quite a speedup, Sameer, did you
try that?

Also, do you really need parts of a cluster or would a base backup of
the whole cluster do the trick?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services