CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecordmulti-tenant
kmurph79
kmurph79

Reputation: 1202

Postgres: Restructuring to Schemas

I have a Rails 3.2 multi-tenant subdomain based app which I'm trying to migrate over to PostgreSQL's schemas (each account getting its own schema -- right now all of the accounts use the same tables).

So, I'm thinking I need to:

  1. Create a new DB
  2. Create a schema for each Account (its id) and the tables under them
  3. Grab all the data that belongs to each account and insert it into the new DB under the schema of said account

Does that sound correct? If so, what's a good way of doing that? Should I write a ruby script that uses ActiveRecord, plucks the data, then inserts it (pretty inefficient, but should get the job done) into the new DB? Or does Postgres provide good tools for doing such a thing?

EDIT:

As Craig recommended, I created schemas in the existing DB. I then looped through all of the Accounts in a Rake task, copying the data over with something like:

Account.all.each do |account|

  PgTools.set_search_path account.id, false
  sql = %{INSERT INTO tags SELECT DISTINCT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."tagger_id" = #{admin.id} AND "taggings"."tagger_type" = 'User'}
  ActiveRecord::Base.connection.execute sql

  #more such commands

end

Upvotes: 1

Views: 568

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325051

I'd do the conversion with SQL personally.

Create the new schemas in the same database as the current one for easy migration, because you can't easily query across databases with PostgreSQL.

Migrate the data using appropriate INSERT INTO ... SELECT queries. To do it without having to disable any foreign keys, you should build a dependency graph of your data. Copy the data into tables that depend on nothing first, then tables that depend on them, and so on.

You'll need to repeat this for each customer schema, so consider creating a PL/PgSQL function that uses EXECUTE ... dynamic SQL to:

  • Create the schema for a customer
  • Create the tables within the schema

  • Copy data in the correct order by looping over a hard-coded array of table names, doing:

    EXECUTE `'INSERT INTO '||quote_ident(newschema)||'.'||quote_ident(tablename)||' SELECT * FROM oldschema.'||quote_ident(tablename)||' WHERE customer_id = '||quote_literal(customer_id)'||;'

    where newschema, tablename and customer_id are PL/PgSQL variables.

You can then invoke that function from SQL. While you could do just select convert_customer(c.id) FROM customer GROUP BY c.id, I'd probably do it from an external control script just so each customer's work got done and committed individually, avoiding the need to start again from scratch if the second-to-last customer conversion fails.

For bonus crazy points it's even possible to define triggers on the main customer schema's tables that replicate changes to already-migrated customers over to the copy of their data in the new schema, so they can keep using the system during the migration. I'd avoid that unless the migration was just too big to do without downtime, as it'd be a nightmare to test and you'd still need the triggers to throw an error on access by customer id x while the migration of x's data was actually in-progress, so it wouldn't be fully transparent.

If you're using different login users for different customers (strongly recommended) your function can also:

  • REVOKE rights on the schema from public
  • GRANT limited rights on the schema to the user(s) or role(s) who'll be using it
  • REVOKE rights on public from each table created
  • GRANT the desired limited rights on each table to the user(s) and role(s)
  • GRANT on any sequences used by those tables. This is required even if the sequence is created by a SERIAL pseudo-column.

That way all your permissions are consistent and you don't need to go and change them later. Remember that your webapp should never log in as a superuser.

Upvotes: 3

Related Questions