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@dunslane.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?
No we have not so far, was soliciting feedback first from the hackers and possibly implement as a contrib module. Also i did misread the earlier post on the subject.

>Also, do you really need parts of a cluster or would a base backup of
>the whole cluster do the trick?
We were looking at parts of cluster as an faster alternative to pg_dump and restore

--
 Andres Freund                     http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services