The results and ramblings of research

phewww!

Moving all indices in a Database to a new Tablespace

leave a comment »

As the title outlines, I need to move all the indices in a database to a new tablespace. Turns out it isn’t too tough. Firstly, we can list all indices in a database using the following command:

select * from pg_catalog.pg_indexes where schemaname = 'public' order by tablename

Thus, the simple way to get all indices in database is the command:

select indexname from pg_catalog.pg_indexes where schemaname = 'public' order by tablename

And so, a simple set of instructions can be then got by executing:

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

After that, copy paste and execute the above in DB shell.

Advertisements

Written by anujjaiswal

September 10, 2013 at 3:53 pm

Posted in Database, PostGRES

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: