How to restore specific tables on heroku from backup

restore.md

How to restore specific tables on heroku from backup

1. Create a new database instance attached to current application
2. Restore latest database backup on newly created database
3. Get the connection url of this backup database. Use this URL in later sql queries.
4. Connect to database where we have incorrect data

heroku pg:psql --app ck-api-prod

5. On postgres prompt run following queries. This will create copy table from data in backup database.

CREATE EXTENSION dblink;

create table questions_copy_table
as 
SELECT * FROM dblink('postgres://XXXXX', 'SELECT id,text,description FROM questions') AS questions(id integer, text text, description text);

create table answers_copy_table
as 
SELECT * FROM dblink('postgres://XXXXX', 'SELECT id,text FROM answers') AS answers(id integer, text text);

6. Now we will restore data from these newly created tables

UPDATE questions
SET 
text = qc.text,
description = qc.description
FROM questions_copy_table AS qc
WHERE questions.id = qc.id;

UPDATE answers
SET 
text = ac.text
FROM answers_copy_table AS ac
WHERE answers.id = ac.id;