I have an index that is very expensive to calculate, and I'm looking for ways to have it be restored or transfered in a simple copy fashion. It would be done along with the underlying data of course.

Alternatively could I do it with files? Something akin to using pg_class.relfilenode to find where the index lives, and copy these files.


What you want is only possible with filesystem-level backup. Index entries ultimately contain references to tuple IDs (TID) of table rows, which are essentially physical pointers to records within database files. Every row update will move it to a different location with a different TID, making the old TID useless unless updated concurrently.

A table restored from a dump will have (possibly all) row TIDs different from the original; if you somehow managed to duplicate the original index and use it to access this new table, results would be undefined.

Only a filesystem-level backup of the index files together with the corresponding table file(s) can guarantee that TIDs remain unchanged.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.