Postgres + Rails - Correct way to migrate all tables from one schema to another

Question

I currently have a Rails app that uses Postgres and stores all it's tables under the default public schema.

I'm setting up a more multi-tenant architecture, so I want to move all those tables from public into another schema foo.

I'm using the following Rails Migration to accomplish this -

class MovePublicToFoo < ActiveRecord::Migration
  def up
    [
      "ALTER SCHEMA public RENAME TO foo;",
      "CREATE SCHEMA public;"
    ].each { |s| ActiveRecord::Base.connection.execute(s) }
  end

  def down
    [
      "DROP SCHEMA public;",
      "ALTER SCHEMA foo RENAME TO public;"
    ].each { |s| ActiveRecord::Base.connection.execute(s) }
  end
end

Is this the correct way to approach this in Postgres?

I didn't know what the dangers were of blindly moving the default schema, and what impacts that may have on the search_path (which I'm not quite sure what that does either)

Thanks!

Answer 1

The impact of this sort of migration is outside the scope of Rails. It depends on what database user (postgres role) Rails uses to connect to the database and what privileges that user has/lacks in the public schema. You'll want to make sure that the privileges will be maintained after the schema has been renamed as well as database ownership. I haven't tested this sort of migration, but I think it should work fine from the perspective of the Rails' app db user. If you have other pg users relying on the public schema though, you may interfere with their connectivity and privileges.

Renaming a postgres schema, particularly the default schema, probably shouldn't be done in a Rails migration unless the particular postgres installation is dedicated to the Rails app.

Despite my opinions, postgres' documentation doesn't suggest anything that should be of concern regarding your migration: http://www.postgresql.org/docs/9.4/static/sql-alterschema.html