indexes_tablespace_changer.rb

#!/usr/bin/env ruby

# Usage sample:
#   CONNECTION_STRING='dbname=some_db port=5432 user=some_user' \
#   TABLESPACE=some_db_indexes_tablespace \
#   ruby indexes_tablespace_changer.rb

require 'pg'

CONNECTION_STRING = ENV['CONNECTION_STRING']
TABLESPACE        = ENV['TABLESPACE']

connection = PG.connect(CONNECTION_STRING)
connection.exec('SET search_path TO public, pg_catalog')

indexes = connection.exec <<-SQL
  SELECT * FROM pg_indexes
  WHERE schemaname != 'pg_catalog'
SQL

indexes.each do |index|
  puts <<-TEXT
    Moving index "#{index['schemaname']}"."#{index['indexname']}" to tablespace "#{TABLESPACE}"...
  TEXT

  connection.exec <<-SQL
    ALTER INDEX "#{index['schemaname']}"."#{index['indexname']}"
    SET TABLESPACE "#{TABLESPACE}"
  SQL
end