Re: Copying an index from one copy of a database to another

Martin Weinberg <weinberg(at)osprey(dot)astro(dot)umass(dot)edu> writes:
> The data is static but indices are build and dropped as needed.
> Because the indexing is time consuming, we would like to maintain
> two separate copies of the database: one for indexing and one for
> production.
> Is there a way to copy the index from one copy of the database
> to another copy without copying all of the data?

Not unless you can guarantee that the data files are absolutely
identical in both databases --- otherwise the index-to-heap
tuple pointers will be wrong. (Even then, there's a small problem
of getting the index's catalog entries to exist in the production
database without going through CREATE INDEX, but that could be
surmounted ... painfully ... by creating all the tuples by hand.)

In practice that would probably mean that the production database would
have to treat the table as read only. If that's how you're using it,
I don't see the benefit of having two databases at all: you can do
SELECTs from a table while an index is being built on it, so why not do
it all in one database and keep things simple?

regards, tom lane