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

On Fri, Apr 12, 2013 at 10:22:38PM +0530, Pavan Deolasee wrote:
>
>
>
> 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.

Uh, now that you mention it, pg_upgrade in non-link mode does
something similer, in that it copies the data files and clog. You could
use pg_upgrade in non-link mode, run VACUUM FREEZE on the upgraded
cluster, and then copy the data files.

The only problem is that pg_upgrade can't upgrade tablespaces with the
same system catalog version because the tablespace directory names would
conflict.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +