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

On Fri, Apr 12, 2013 at 9:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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.
>
>
Would it be possible to fix the XIDs *after* copying the data files,
potentially on a different server so as to avoid any additional overhead on
the main server ? I guess so, though we will probably need some mechanism
to lock out access to the table (which seems easy), flush all its data
pages to the disk and some way to reliably flush all clog pages as well so
that they can be copied along with the data files. The page LSNs seem to be
easy to handle and can be easily zeroed out outside the server.

I wonder though if this all look like a material for something like
pg_reorg(pack) though some kind of support from the core may be required.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee