Overview of all database indices


I have updated my pod today to I had some issues with the database migration as the script was complaining that some indices do not exists. The indices were there but with a wrong name (starting with idx_…).

I assume that it is a side effect of moving from MySQL to Postgres. So I want to check all indices on my database (sounds like a good task for a rainy sunday afternoon).

I was snooping the repository a bit and I found this file: https://github.com/diaspora/diaspora/blob/develop/db/migrate/0000_create_schema.rb

Does this file contain all database indices? Or do I have to take care about another file?

Thank you in advance.


No, indexes are in all migrations, not only the first (0000_create_schema.rb), so you would need to check all of them and sometimes an index is removed and recreated differently.

The easiest way to check if your database is in a correct state is:

  • Install diaspora somewhere else with postgres (locally or a second DB)
  • After the RAILS_ENV=production bin/rake db:create db:migrate step copy the db/schema.rb
  • Compare this file with the file on your production pod, every difference in this file is something that’s probably wrong in your database compared to a clean installation (maybe not only indexes).

You can post the diff of these two files here if you have more questions.

Hi Benjamin,

thanks for your reply… okay looks like more work than for a rainiy sunday afternoon…
I got a small server running at home, I will set up Diaspora there, check for diffs and will get back if I need further support.