db:migrate error

[waynegraham]

There is an issue with the database migrations which generates the following error:

StandardError: An error has occurred, this and all later migrations canceled:

PG::DuplicateTable: ERROR:  relation "proposals" already exists

This isn't presenting a problem as the database schema hasn't changed, just the in the migrations.

[waynegraham]

Need to export individual table data, reset the database, and reinsert the data. There were some missing columns in the User object in this migration as well.

Local Fix

• Create a backup of data (heroku pg:backups:capture)
• Download a local copy (heroku pg:backups:download)
• Restore local copy to development database (pg_restore --verbose --clean --no-acl --no-owner -h localhost -U `whoami` -d mydb latest.dump
• Dump individual data tables:

pg_dump --column-inserts --data-only --table=proposals participatory_voter_development > proposals.sql
pg_dump --column-inserts --data-only --table=users participatory_voter_development > users.sql
pg_dump --column-inserts --data-only --table=votes participatory_voter_development > votes.sql

Then delete the tables locally and fix.

Restore

See https://devcenter.heroku.com/articles/heroku-postgres-import-export#import-to-heroku-postgres

To restore, you need a dump file somewhere with a URL (like AWS with resigned URL):

aws s3 presign s3://your-bucket-address/your-object

Then, use pg:backup:restore

heroku pg:backups:restore '<SIGNED URL>' DATABASE_URL

[waynegraham]

This method ran into issues. Ended up manually fixing the migrations table to reflect the new dates:

DELETE FROM schema_migrations;

select * from schema_migrations;

INSERT INTO schema_migrations VALUES (20200415142945);
INSERT INTO schema_migrations VALUES (20200414162703);
INSERT INTO schema_migrations VALUES (20200415134236);
INSERT INTO schema_migrations VALUES (20200415145413);
INSERT INTO schema_migrations VALUES (20200415152813);
INSERT INTO schema_migrations VALUES (20200421182813);