Best method to fix my dev postgres database after a botched restore cleared numerous constraints
I only got involved in a project a little while ago, and while our prod dB is fine, at some stage a full restore to the dev db turned off constraints, didn't fully work and didn't turn them back on, and then was reapplied, which duped a lot of data, and then tried to turn the constraints back on, and failed, but things just continued.
Now we've multiple tables which lost their primary keys and foreign keys, and a few other things, and prisma tells us we've drifted and should reset. The only issue is, we've loads of other data in the dB that we can't lose.
Ideally, and this may not even be a prisma question necessarily, what I'd like to do is do a backup, do the dB reset, then do a "ignore insert errors" restore on it.
My question is about what I could use prisma for. Can I get the diffs of what's wrong with the table, in a programmatic way? If I can, I could handle a lot of this programmatically. Like the first error I get,
[*] Changed the Achievements table
[-] Dropped the primary key on columns (id)
If I can get that, programmatically, I'll drop all dupe id rows on that table, and then re-add the primary key - I know that's all necessary because that's the state of that table, it has no primary key field, and has dupe rows.
Am I crazy, is that over-thinking it? Is there a better method I should at least try first? Many thanks for any and all suggestions2 Replies
You decided to hold for human wisdom. We'll chime in soon! Meanwhile,
#ask-ai is there if you need a quick second opinion.One possible option could be to Export only the tables / rows that matter from your dev database and then reinsert into a new dev db using:
INSERT ... ON CONFLICT DO NOTHING
The new dev db could have the exact same schema which you get by doing db pull from your production database