Heroku, Rails, Postgres and database purge and setup

If you delete Rails migrations after they are used, and then reset your production Heroku Postgres database:

heroku pg:reset DATABASE

All you can do to get your database schema back (error free) is heroku run rake db:schema:load. If you then try to heroku run rake db:seed (and you have foreign keys) you will get errors like:

PG::InsufficientPrivilege: ERROR:  permission denied: "RI_ConstraintTrigger_a_12949367" is a system trigger

The solution is to reset your development database and then push that pristine copy to heroku, ie.

# wipe and recreate your development database
bundle exec rake db:purge db:setup
# wipe your production database
heroku pg:reset DATABASE
# push your local development database to Heroku
heroku pg:push yourappname_development HEROKU_POSTGRESQL_PINK_URL --app yourappname

yourappname_development - your local development database name
HEROKU_POSTGRESQL_PINK_URL - your production Heroku database name (heroku pg:info to find out)
yourappname - your Heroku application name

---

Another case where this error comes up is using fixtures to update "static" data in production database. If you are using Heroku you cannot just become the Postgres super-user and disable the constraint.

But this works (from a psql prompt, eg. heroko pg:psql) ...

# verify the foreign key constraint on the table and it's name:
\d table_name
# drop the foreign key constraint
alter table table_name drop constraint fk_rails_01234abcde;
#
# now run your fixtures and/or whatever re-seeding you need to do
#
# create the dropped foreign key
alter table table_name add constraint fk_rails_01234abcde foreign key(column_name) references other_table(id);