Filling Missing Rows in PostgreSQL Database from Backup

djangopgadminpostgresqlpostgresql-9.4

I have a big problem and I'm not that good with postgresql since I use Django to make queries and I only have the basics of sql for querys.

My problem is that there was a problem where a super user deleted a field that was related with my table "clients" (I don't know how she did it, I didn't design the system, I just manage it), so there are like 120 clients that dissapeared from the table, I made a backup from yesterday (I know how important is to make backups) and I want to "copy" all the data from my backup to database with the missing rows.

I tried to restore only the table from pgadmin (the easy way) but it says that "duplicates key values violates unique constraint "user_client_pkey", detail: Key(id) = 461 already exist. So I'm guessing that I cannot restore because there are values that match the exact id's, is there any easy way (or program) that can copy the missing row only giving the id's and fill with the data that I have in my backup?

Something like compare rows and if there are no row with that id, insert the data there? I know is not an easy query, but any help would be great. The most easy way I'm sure that is to rollback, delete the database and restore, but there are so many tables that have more information and clients is the only one that really needs to be restore.

I tried to create a new table and restore the data from backup but it doesn't fill (probably because it doesn't match the name with the new tables), I also changed the name of my table with the missing rows and I create a new table and restore from the backup, but the pk got missing and I receive this error:

I changed the original of the table cliente, then I created a new table and name it like the original one, restore the database, it fills, but gives me this error:

Since the table public."admin_clienteB" doesn't have a primary key or OIDs, you can view the data only. Inserting new rows and changing existing rows isn't possible for the Edit Data tool without primary key.

Any helps would be great.

Best Answer

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-the-database-with-a-different-schema).

Once the table is in a separate schema, you can compare them by primary key, and insert the missing ones, like so for each affected table:

INSERT INTO production.table
SELECT b.*
FROM backup.table b
LEFT JOIN production.table p ON (p.pkey = b.pkey)
WHERE p.pkey IS NULL;

You will just need to find out the list of affected tables, restore their backup in a separate schema, determine what are their primary keys, create the script with the inserts, and run it.

Related Question