Home > PostgreSQL > A quick way to change index tablespace

A quick way to change index tablespace

On one of the database servers, I had to quickly move all indexes from all the databases on to a different disk in order to free up some space from the main disk. I thought of sharing it so that someone else wanting to do the same thing can get an idea for doing the same….

A quick how to,

First made a query to get all the indexes within a database using the following:

select pg_namespace.nspname||'.'||pg_class.relname  from pg_class, pg_namespace
where pg_class.relnamespace  = pg_namespace.oid and pg_class.relkind = 'i' and pg_class.relname
not like 'pg_%'

and then using a simple script, plugging in the the above mentioned query to do an ALTER INDEX for each index by setting the new table space:

#!/bin/bash
DNAME_LIST=$(psql postgres -A -t -c "select datname from pg_database where
not datistemplate and datname != 'postgres'")
for DATABASE in $DNAME_LIST; do
INAME_LIST=$($PSQL $DATABASE -A -t -c "select pg_namespace.nspname||'.'||pg_class.relname
from pg_class, pg_namespace where
pg_class.relnamespace  = pg_namespace.oid and pg_class.relkind = 'i' and pg_class.relname
not like 'pg_%'")
    ( for INDEX in $INAME_LIST; do echo "ALTER INDEX $INDEX SET TABLESPACE
new_disk_index;"; done ) | psql $DATABASE
done


Shoaib Mir
shoaibmir[@]gmail.com

Advertisements
  1. September 30, 2009 at 7:16 am

    It seems a little baroque to run this through the shell when psql can do this job by setting \o to ‘|psql’.

  2. September 30, 2009 at 9:44 am

    Thanks for the tip David, never thought about that. I was just looking for a very quick and hacky way to get around this but for the next time I will keep the \o option 🙂

  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: