How to migrate a complex Rails database to use Postgresql UUID primary keys

Question

I have a database that I would like to convert to use the UUID as the primary key in postgresql.

I have approximately 30 tables with deep multi-level associations. Is there a “simple” way to convert the entire current identifier to a UUID?

From this: https://coderwall.com/p/n_0awq , I see that I can change the table during the migration. I thought something like this:

for client in Client.all # Retrieve children underwritings = client.underwritings # Change primary key execute 'ALTER TABLE clients ALTER COLUMN id TYPE uuid;' execute 'ALTER TABLE clients ALTER COLUMN id SET DEFAULT uuid_generate_v1();' # Get new id - is this already generated? client_id = client.id for underwriting in underwritings locations = underwriting.locations other_record = underwriting.other_records... execute 'ALTER TABLE underwritings ALTER COLUMN id TYPE uuid;' execute 'ALTER TABLE underwritings ALTER COLUMN id SET DEFAULT uuid_generate_v1();' underwriting.client_id = client_id underwriting.saved underwriting_id = underwriting.id for location in locations buildings = location.buildings execute 'ALTER TABLE locations ALTER COLUMN id TYPE uuid;' execute 'ALTER TABLE locations ALTER COLUMN id SET DEFAULT uuid_generate_v1();' location.undewriting_id = underwriting_id location.save location_id = location.id for building in buildings ... end end for other_record in other_records ... end ... ... end end 

Questions:

• Will this work?
• Is there an easier way to do this?
• Will child records be returned properly until they are restored before the primary key is changed?
• Will a new primary key be already generated right after the alter table is called?

Thanks so much for any help or advice on this.

Answer 1

I found them quite tedious. To convert a table with existing data, you can use direct queries to PostgreSQL.

For primary key:

  ALTER TABLE students ALTER COLUMN id DROP DEFAULT, ALTER COLUMN id SET DATA TYPE UUID USING (uuid(lpad(replace(text(id),'-',''), 32, '0'))), ALTER COLUMN id SET DEFAULT uuid_generate_v4() 

For other links:

  ALTER TABLE students ALTER COLUMN city_id SET DATA TYPE UUID USING (uuid(lpad(replace(text(city_id),'-',''), 32, '0'))) 

The above left overlays an integer value with zeros and translates to UUIDs. This approach does not require identifier matching and, if necessary, the old identifier can be restored.

Since data copying is not performed, this approach is fairly fast.

To deal with these and more complex cases of polymorphic associations, use https://github.com/kreatio-sw/webdack-uuid_migration . These pearls add additional helpers to ActiveRecord :: Migration to facilitate these migrations.

Answer 2

I think trying to do something like this through Rails will just complicate things. I will completely ignore some of the Rails stuff and just do it in SQL.

Your first step is to capture a full backup of your database. Then restore this backup to another database to:

• Make sure your backup is working.
• Give you a realistic playpen where you can make mistakes without consequences.

First, you want to clear your data by adding real foreign keys to match all your Rails associations. There is a good chance that some of your FKs will fail if you have to clear your broken links.

Now that you have clean data, rename all your tables to make room for new versions of UUIDs. For table t we will refer to the renamed table as t_tmp . For each t_tmp create another table to preserve the mapping from the old integer id to the new UUID id s, something like this:

 create table t_id_map ( old_id integer not null, new_id uuid not null default uuid_generate_v1() ) 

and then fill it in:

 insert into t_id_map (old_id) select id from t_tmp 

And you probably want to index t_id_map.old_id while you are here.

This gives us old tables with integer ids and a lookup table for each t_tmp , which maps the old id to the new one.

Now create new tables with a UUID, replacing all the old integer and serial columns that contained id s; I would add real foreign keys at this point; you must be paranoid about your data: broken code is temporary, broken data is usually forever.

Filling in new tables is quite simple: just use the insert into ... select ... from and JOIN constructors in the corresponding t_id_map tables to match the old id with the new ones. After the data has been matched and copied, you will want to perform some health checks to make sure that everything makes sense. Then you can leave your tables t_tmp and t_id_map and continue your life.

Practice this process on a copy of your database script and you quit.

Of course, you want to close all applications that access your database while you do this work.

Answer 3

I did not want to add foreign keys and I wanted to use rail migration. In any case, this is what I did if others want to do this (example for two tables, 32 in total):

  def change execute 'CREATE EXTENSION "uuid-ossp";' execute <<-SQL ALTER TABLE buildings ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL; ALTER TABLE buildings ALTER COLUMN guid SET DEFAULT uuid_generate_v1(); ALTER TABLE buildings ADD COLUMN location_guid uuid; ALTER TABLE clients ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL; ALTER TABLE clients ALTER COLUMN guid SET DEFAULT uuid_generate_v1(); ALTER TABLE clients ADD COLUMN agency_guid uuid; ALTER TABLE clients ADD COLUMN account_executive_guid uuid; ALTER TABLE clients ADD COLUMN account_representative_guid uuid; SQL for record in Building.all location = record.location record.location_guid = location.guid record.save end for record in Client.all agency = record.agency record.agency_guid = agency.guid account_executive = record.account_executive record.account_executive_guid = account_executive.guid unless account_executive.blank? account_representative = record.account_representative record.account_representative_guid = account_representative.guid unless account_representative.blank? record.save end execute <<-SQL ALTER TABLE buildings DROP CONSTRAINT buildings_pkey; ALTER TABLE buildings DROP COLUMN id; ALTER TABLE buildings RENAME COLUMN guid TO id; ALTER TABLE buildings ADD PRIMARY KEY (id); ALTER TABLE buildings DROP COLUMN location_id; ALTER TABLE buildings RENAME COLUMN location_guid TO location_id; ALTER TABLE clients DROP CONSTRAINT clients_pkey; ALTER TABLE clients DROP COLUMN id; ALTER TABLE clients RENAME COLUMN guid TO id; ALTER TABLE clients ADD PRIMARY KEY (id); ALTER TABLE clients DROP COLUMN agency_id; ALTER TABLE clients RENAME COLUMN agency_guid TO agency_id; ALTER TABLE clients DROP COLUMN account_executive_id; ALTER TABLE clients RENAME COLUMN account_executive_guid TO account_executive_id; ALTER TABLE clients DROP COLUMN account_representative_id; ALTER TABLE clients RENAME COLUMN account_representative_guid TO account_representative_id; SQL end