Database Reference
In-Depth Information
As this literally moves all objects that do not have an explicitly defined tablespace into
new_
tablespace
. For a large database, this will take a very long time, and your database will be
completely locked while it runs. Not cool, if you do it by accident.
There's more...
If you just discovered that indexes don't get moved when you move a table, then you may want
to check to see if any indexes are in different tablespaces from their parent tables. Run the
following to check:
SELECT
i.relname as indexname
,tsi.spcname
,t.relname as tablename
,tst.spcname
FROM
(((pg_class t
/* tables */
JOIN pg_tablespace tst
ON
t.reltablespace = tst.oid)
JOIN pg_index pgi
ON pgi.indrelid = t.oid)
JOIN pg_class i
/* indexes */
ON
pgi.indexrelid = i.oid)
JOIN pg_tablespace tsi
ON
i.reltablespace = tsi.oid
WHERE
i.relname NOT LIKE 'pg_toast%'
AND
i.reltablespace != t.reltablespace
;
If we have one table with an index in a separate tablespace, we might see this as psql definition:
postgres=# \d y
Table "public.y"
Column | Type | Modifiers
--------+------+-----------
val | text |
Indexes:
"y_val_idx" btree (val), tablespace "new_tablespace"
Tablespace: "new_tablespace2"
Running the query presented previously, gives the following:
relname | spcname | relname | spcname
-----------+------------------+---------+---------------
y_val_idx | new_tablespace | y | new_tablespace2
(1 row)
