CODEWITHSUNDEEP
ruby-on-railspostgresqlschemarails-migrations
user2490003
user2490003

Reputation: 11890

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

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!

Upvotes: 3

Views: 1030

Answers (1)

Sean Huber
Sean Huber

Reputation: 3985

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

Upvotes: 2

Related Questions