AWS RDS post-migration checks.md

Before booting the pods

1. verify the count and schema for all records

old_db_url = ""
new_db_url = ""


connection_klass = Class.new(ApplicationRecord) do
  self.abstract_class = true
  def self.name
    "ConnectionKlass"
  end
end

connection_klass.establish_connection(old_db_url)

check_result = connection_klass.connection.tables.map do |table|
  old_model_klass = Class.new(ApplicationRecord) do
    def self.name
      "OldModelKlass"
    end
  end.tap { |klass| klass.table_name = table }.tap { |klass| klass.establish_connection(old_db_url) }
  new_model_klass = Class.new(ApplicationRecord) do
    def self.name
      "NewModelKlass"
    end
  end.tap { |klass| klass.table_name = table }.tap { |klass| klass.establish_connection(new_db_url) }

  old_count = old_model_klass.count
  new_count = new_model_klass.count
  counts_match = (old_count == new_count)
  begin
    schema_match = (old_model_klass.last&.attributes&.keys == new_model_klass.last&.attributes&.keys)
    old_attributes_keys_count = old_model_klass.last&.attributes&.keys&.count
    new_attributes_keys_count = new_model_klass.last&.attributes&.keys&.count
  rescue StandardError => e
    schema_match = e.message
    old_attributes_keys_count = e.message
    new_attributes_keys_count = e.message
  end
  { table: table, old_count: old_count, new_count: new_count, old_attributes_keys_count: old_attributes_keys_count, new_attributes_keys_count: new_attributes_keys_count,  counts_match: counts_match, schema_match: schema_match }.tap { |result| puts result }
end
check_result.find { |hash| hash[:counts_match] == false || hash[:schema_match] == false }

2. Verify indexes:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   old_result = old_connection_klass.connection.execute("SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;").values
   
   new_result = new_connection_klass.connection.execute("SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;").values
   
   old_result.sort_by { |_, name, _| name } == new_result.sort_by { |_, name, _| name }

3. Verify foreign key constraints:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   new_result = new_connection_klass.connection.execute("SELECT conrelid::regclass AS table_name,  conname AS foreign_key,  pg_get_constraintdef(oid)  FROM   pg_constraint WHERE  contype = 'f' AND    connamespace = 'public'::regnamespace  ORDER  BY conrelid::regclass::text, contype DESC;").values
   
   old_result = old_connection_klass.connection.execute("SELECT conrelid::regclass AS table_name,  conname AS foreign_key,  pg_get_constraintdef(oid)  FROM   pg_constraint WHERE  contype = 'f' AND    connamespace = 'public'::regnamespace  ORDER  BY conrelid::regclass::text, contype DESC;").values
   
   old_result.sort_by { |_, name, _| name } == new_result.sort_by { |_, name, _| name } 

4. Ensure schema migrations is correct:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   new_connection_klass.connection.execute("SELECT * FROM schema_migrations;").values == old_connection_klass.connection.execute("SELECT * FROM schema_migrations;").values

5. Verify default values:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   old_defaults = []
   old_connection_klass.connection.tables.each do |table_name|
     puts table_name
     old_connection_klass.connection.columns(table_name).each do |c| 
       unless c.default.nil?
         old_defaults << { table_name: table_name, column: c.name, default: c.default } 
       end
     end
   end
   
   new_defaults = []
   new_connection_klass.connection.tables.each do |table_name|
     puts table_name
     new_connection_klass.connection.columns(table_name).each do |c| 
       unless c.default.nil?
         new_defaults << { table_name: table_name, column: c.name, default: c.default } 
       end
     end
   end
   
   new_defaults.sort_by { |h| h[:table_name] } == old_defaults.sort_by { |h| h[:table_name] }

6. Verify types:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   old_types = []
   old_connection_klass.connection.tables.each do |table_name|
     puts table_name
     old_connection_klass.connection.columns(table_name).each do |c| 
       if c.sql_type == "character varying"
         old_types << { table_name: table_name, column: c.name, type: c.sql_type } 
       end
     end
   end
   
   new_types = []
   new_connection_klass.connection.tables.each do |table_name|
     puts table_name
     new_connection_klass.connection.columns(table_name).each do |c| 
       if c.sql_type == "character varying"
         new_types << { table_name: table_name, column: c.name, type: c.sql_type } 
       end
     end
   end
   
   new_types.sort_by { |h| h[:table_name] } == old_types.sort_by { |h| h[:table_name] }

7. Verify null constraints:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   old_nulls = []
   old_connection_klass.connection.tables.each do |table_name|
     puts table_name
     old_connection_klass.connection.columns(table_name).each do |c| 
       old_nulls << { table_name: table_name, column: c.name, type: c.null } 
     end
   end
   
   new_nulls = []
   new_connection_klass.connection.tables.each do |table_name|
     puts table_name
     new_connection_klass.connection.columns(table_name).each do |c| 
       new_nulls << { table_name: table_name, column: c.name, type: c.null } 
     end
   end
   
   old_nulls.sort_by { |h| h[:table_name] } == new_nulls.sort_by { |h| h[:table_name] }

8. Verify constraints overall:

   old_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "OldConnectionKlass"
     end
   end
   
   old_connection_klass.establish_connection(old_db_url)
   
   new_connection_klass = Class.new(ApplicationRecord) do
     self.abstract_class = true
     def self.name
       "NewConnectionKlass"
     end
   end
   
   new_connection_klass.establish_connection(new_db_url)
   
   old_result = old_connection_klass.connection.execute("SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace;")
   new_result = new_connection_klass.connection.execute("SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace;")
   
   new_result.values.sort_by { |_, name, *| name }.map { |_, name| name }.sort == old_result.values.sort_by { |_, name, *| name }.map { |_, name| name }.sort

9. Rebuild Primary Key sequences and verify it for some tables

new_connection_klass = Class.new(ApplicationRecord) do
  self.abstract_class = true
  def self.name
    "NewConnectionKlass"
  end
end

new_connection_klass.establish_connection(new_db_url)

new_connection_klass.connection.execute(" SELECT NEXTVAL('public.accounts_id_seq');").values

# the pattern is: public.TABLE_NAME_id_seq

If all good, we could bring the pods up!

After pods are up

just for the final check:

# after booting the pods
SomeModelToCheck.count
record = SomeModelToCheck.last
record.update!(updated_at: Time.current)
record.reload.updated_at