Fix up broken id seqs on Postgres after migrating from MySQL using ar_dbcopy gem

fix-postgres-seqs.rb

require 'pg'

unless ARGV[0]
  puts "Pass the name of the database"
  exit 1
end


# Get our Postgres connection
conn = PGconn.open(:dbname => ARGV[0], :host => 'localhost')

# Get a list of all the tables
res = conn.exec("select c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', '') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)")
tables = res.collect { |row| row.collect { |col| col[1] } }.flatten

# Iterate through all the tables except 'schema_migrations'
tables.reject { |t| t == "schema_migrations" }.each do |t|
  puts "Checking table #{t}"
  sequences = conn.exec("SELECT seq.relname::text FROM pg_class src, pg_class seq, pg_namespace, pg_attribute, pg_depend WHERE pg_depend.refobjsubid = pg_attribute.attnum AND pg_depend.refobjid = src.oid AND seq.oid = pg_depend.objid AND src.relnamespace = pg_namespace.oid AND pg_attribute.attrelid = src.oid AND src.relname = '#{t}'")

  seq = sequences.collect { |s| s['relname'] }.reject { |s| s.start_with? "index" }.first

  # Reset the sequence using the max id of the table.
  conn.exec("select setval('#{seq}', (select max(id) from #{t}))")
end