Friday, May 7, 2010

moving indexes in postgres to a new tablespace

Recently I upgraded my server and added a SSD raid system of 3 128GB disks and for obvious reasons want to have my indexes on it. So the first step was to create a new tablespace

create tablespace 'name' owner 'owner' location 'directory'

and than creating a quick script to move all the indexes

select 'ALTER INDEX '||indexname||' SET TABLESPACE compoundindex ;' from pg_catalog.pg_indexes where schemaname = 'public' order by tablename;

which than just needs to be executed

No comments:

Post a Comment