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

On Fri, Apr 12, 2013 at 9:52 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> 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?
> 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
>