How I fixed a broken database

(Jay Pfaffman) #1

I’m trying to restore a database and it’s failing like this:

ALTER TABLE                                                                                                                  
ERROR:  could not create unique index "posts_pkey"                                                                           
DETAIL:  Key (id)=(750) is duplicated.                                                                                       
ERROR:  current transaction is aborted, commands ignored until end of transaction block                                      
ERROR:  current transaction is aborted, com

I think that I understand that in my bumbling at the Rails console I’ve created a duplicate key in some table and then it’s trying to build an index and that fails.

Is there some way that I can edit the dump file to stop that index from getting created, import the data, delete the errant record, and then, maybe, do a db:migrate to get things fixed up again?

Or maybe I could remove the duplicate record from the dump file?

Or maybe that index has been added recently and I could install Discourse before that index was added, restore the database, fix the data, and then upgrade?

I can’t quite figure out what in the dump file is building the index that’s causing the problem.

Edit: Does ‘posts_pkey’ mean that it’s the primary key of the posts table? and somehow there are two posts with id of 750? It would seem that if I’d some something boneheaded with posts, the post ID would be bigger than 750. . .

Edit: I had a great idea. Remove all of the “CREATE INDEX” lines from the SQL dump; surely that would solve any problems with a unique index (then I could add them back until I found the errant index!), but, alas, that didn’t work, so I’m back to thinking that the posts table somehow has duplicate post IDs in it. Figuring out how to edit that wall of text seems decidedly more complicated.

Edit: And here’s what fixed it

  • remove the ALTER TABLE queries that had to do with post_ids from the backup file.
  • restore the database
  • delete the entries from posts, posts_search_data that had duplicates
  • perform the missing ALTER TABLE queries
  • restart the server

And I’ve successfully upgraded to latest, so I think I’m safe. I should probably restore the database elsewhere just to make double-extra sure.