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

Folks,

We have a database with 1.2 billion records (about 300GB not
including indices).

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.

The question is:

Is there a way to copy the index from one copy of the database
to another copy without copying all of the data?

Right now, we are copying the entire data directory, which is
a bit painful.

--Martin

===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower
University of Massachusetts
Amherst, MA  01003-4525

Martin Weinberg <weinberg@osprey.astro.umass.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