Migrating from mysql to postgresql - Pistos/diaspora GitHub Wiki

This is a rough sketch of the steps used to migrate from mysql to postgresql on calispora.org. I use Ubuntu, so this article is slanted that way.

This guide assumes you use production environment for diaspora.

Prepare

  1. Install postgresql 9.1 if it isn't already on your server.
  • On ubuntu, it is a simple sudo apt-get install postgresql on recent release (like 11.10).
  • If you have an earlier version of postgresql (like 8.4), the templates will have an encoding incompatible with the diaspora databases (ascii instead of unicode). You can either change the template encoding or upgrade to postgresql 9.1. There are a variety of ways to upgrade. This page has backports of pg 9.1 for older versions of ubuntu: https://launchpad.net/~pitti/+archive/postgresql
  1. Create the database and add your diaspora user if desired
  • myuser$ sudo su postgres
  • postgres# createuser -dP diaspora
  • postgres# createdb -O diaspora diaspora_production
  1. Add the mysqltopostgres ruby gem following the instructions in this excellent guide: http://ruby.zigzo.com/2011/12/03/migrating-data-from-mysql-to-postgresql/
  • Run it once to create the yml file. Edit the yml file to include mysql and postgres users/passwords. Don't run it again (yet).
  1. Edit config/database.yml to reflect postgres user/password. Change the common line to comment out mysql instead of postgresql.
  2. Take diaspora offline
  3. Back up your mysql database (mysqldump or other)
  4. Take a deep breath. Take another. Here we go.

Caution:

This migration (if unattended) will result in an error when users try to delete a contact or ignore someone. The problem is that this conversion does not add "ON DELETE CASCADE" to the foreign key constraints.

Two solutions:

Write your mysql data to a file instead of transferring directly into postgresql.

mysqltopostgres has an option to do this in the yml file. Edit the file to add "ON DELETE CASCADE" to the foreign key statements near the bottom of the dump file. There are about 18 at the time of this writing.

Change:

ALTER TABLE ONLY aspect_memberships
    ADD CONSTRAINT aspect_memberships_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(id);

To:

ALTER TABLE ONLY aspect_memberships
    ADD CONSTRAINT aspect_memberships_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(id)
    ON DELETE CASCADE;

(and 17 more)

OR

Change the database once it is already in postgresql.

From inside postgresql, you must drop each foreign key constraint and then add it back in with cascading, like so:

ALTER TABLE ONLY aspect_memberships
    DROP CONSTRAINT IF EXISTS aspect_memberships_contact_id_fkey ;
ALTER TABLE ONLY aspect_memberships
    ADD CONSTRAINT aspect_memberships_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(id)
    ON DELETE CASCADE;

(and 17 more)

There is a rake task db:psql_repair that attempts to do this automatically. You may want to review the task before running it to make sure that all affected tables are covered. It is defined in lib/tasks/mysql-to-psql-cleanup.rake.

Migrate

  1. from your diaspora directory:
  • DB="postgres" RAILS_ENV="production" bundle install --without heroku test development
  • DB="postgres" RAILS_ENV="production" bundle exec rake db:migrate
  • mysqltopostgres (runs the actual database transfer from mysql to postgresql) [See CAUTION above]
  1. Start diaspora