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

<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Fri, Apr 12, 2013 at 9:44 PM, Tom
Lane<span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div
class="im">AndrewDunstan <<a href="mailto:andrew@dunslane.net">andrew@dunslane.net</a>> writes:<br /> > On
04/12/201310:15 AM, Tom Lane wrote:<br /></div><div class="im">>> There's 0 chance of making that work, because
thetwo databases wouldn't<br /> >> have the same notions of committed XIDs.<br /><br /> > Yeah. Trying to
thinkway outside the box, could we invent some sort of<br /> > fixup mechanism that could be applied to adopted
files?<br/><br /></div>Well, it wouldn't be that hard to replace XIDs with FrozenXID or<br /> InvalidXID as
appropriate,if you had access to the source database's<br /> clog while you did the copying.  It just wouldn't be very
fast.<br/><div class="HOEnZb"><div class="h5"><br /></div></div></blockquote></div><div class="gmail_extra"><br
/></div>Wouldit be possible to fix the XIDs *after* copying the data files, potentially on a different server so as to
avoidany additional overhead on the main server ? I guess so, though we will probably need some mechanism to lock out
accessto the table (which seems easy), flush all its data pages to the disk and some way to reliably flush all clog
pagesas well so that they can be copied along with the data files. The page LSNs seem to be easy to handle and can be
easilyzeroed out outside the server.<br /><br clear="all" /><div style="style">I wonder though if this all look like a
materialfor something like pg_reorg(pack) though some kind of support from the core may be required.</div><div
style="style"><br/></div><div style="style">Thanks,</div><div style="style"> Pavan</div><div style="style"><br
/></div>--<br />Pavan Deolasee<br /><a href="http://www.linkedin.com/in/pavandeolasee"
target="_blank">http://www.linkedin.com/in/pavandeolasee</a></div></div>