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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> 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.

> The big win here over a binary COPY is pulling through the indexes as-is
> as well- without having to rebuild them.

Meh. That raises the ante another substantial multiple with respect to
the amount of portability risk (eg, you're now absolutely dependent on
locale sort orders to be identical in both databases). And I think
you'd have to freeze all updates to the table while you were copying the
table+indexes, if you wanted them to be consistent.

I can't imagine that we'd accept a patch that says to the recipient
database, "here are some large binary blobs, please believe that
they represent a valid table and associated indexes. Oh, and don't you
dare try to actually check them, because that would be slow."

Some other interesting things to think about here would be toast-table
OIDs embedded in toast pointers, data type OIDs embedded in arrays (and
maybe records too, I forget), enum value OIDs, btree vacuum cycle IDs,
GiST NSNs ... not sure what else, but I bet that's not a complete list.

regards, tom lane