The AR-DBcopy gem, by Michael Siebert, made this very easy. The ReadMe doesn’t cover use of the gem, so I’ll cover that here.
Overview:
AR-DBcopy uses ActiveRecord to achieve database agnosticism. Both the source and target databases are accessible a single instance of Rails (in my case, both databases were on the same machine, but if you configure database.yml to connect to a different host, then they need not be). The source contains the data; the target starts out empty, but with the correct schema (generated by running rake db:migrate).
Steps: (Be sure that nobody will be interacting with the web application during this process.)
- Set up everything you need to in order for your Rails app to work with Postgres (install Postgres server, pg gem, create a Postgres user, change your database.yml file (keep the MySQL settings handy), and run rake db:create and rake db:migrate). That leaves you with a MySQL database with your data and an empty Postgres database with the correct schema.
- Modify database.yml so that it matches the example. This is easy, simply copy your old MySQL settings to “source:” and your new Postgres settings to “target:”
Original database.yml:
production: adapter: mysql encoding: utf8 database: old_dbname username: old_dbuser password: old_pass socket: /var/run/mysqld/mysqld.sock
Modified database.yml:
source: adapter: mysql encoding: utf8 database: old_dbname username: old_dbuser password: old_pass socket: /var/run/mysqld/mysqld.sock target: adapter: postgresql encoding: unicode database: new_dbname username: new_dbuser password: new_pass production: adapter: postgresql encoding: unicode database: new_dbname username: new_dbuser password: new_pass
- Install the ar-dbcopy gem:
$ sudo gem install ar_dbcopy - From the Rails root directory, run the gem:
$ ar_dbcopy config/database.yml - After this finishes (for me, it took on the order of 5 minutes for 100K records), you will need to reset the PostgreSQL id sequences by performing
select setval(‘users_id_seq’, (select max(id) + 1 from users));
for every table in the database that has a corresponding _id_seq
Good write-up. Heroku uses Postgres and I’m still writing apps for MySQL, so this is something I’ll need to do in the near future.