patternsqlMinor
Have postgres' pg_dump export an index
Viewed 0 times
pg_dumppostgresexportindexhave
Problem
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.
Alternatively could I do it with files? Something akin to using pg_class.relfilenode to find where the index lives, and copy these files.
Solution
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.
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.
Context
StackExchange Database Administrators Q#209017, answer score: 4
Revisions (0)
No revisions yet.