May 2018
Intermediate to advanced
576 pages
30h 25m
English
If you just discovered that indexes don't get moved when you move a table, then you may want to check whether any indexes are in tablespaces different from their parent tables. Run the following to check:
SELECT i.relname as index_name , tsi.spcname as index_tbsp , t.relname as table_name , tst.spcname as table_tbsp FROM ( pg_class t /* tables */ JOIN pg_tablespace tst ON t.reltablespace = tst.oid OR ( t.reltablespace = 0 AND tst.spcname = 'pg_default' ) ) JOIN pg_index pgi ON pgi.indrelid = t.oid JOIN ( pg_class i /* indexes */ JOIN pg_tablespace tsi ON i.reltablespace = tsi.oid OR ( i.reltablespace = 0 AND tsi.spcname = 'pg_default' ) ) ON pgi.indexrelid = i.oid WHERE i.relname NOT LIKE 'pg_toast%' AND i.reltablespace != t.reltablespace ...