Skip to content

Instantly share code, notes, and snippets.

@yuryroot
Last active January 14, 2016 09:08
Embed
What would you like to do?
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment