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,
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 suggestions
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 theAchievementstable
[-] 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 suggestions
