We have a Rails app backed by a PostgreSQL DB and hosted on Heroku.
We recently had a bug that deleted some (very few before we caught it) records.
Excuse my ignorance but is there a straightforward way to directly pull a record (or 3) off of a backup and insert it back into the live DB? Or must these be recreated manually from the Rails console?
Thanks for any advice, warnings, etc.
On way would be to restore only the relevant table to a new empty dummy database. Then find rows with standard SQL tools. Not sure if you are restricted by Heroku in any way. On the shell, as postgres user (or any user with sufficient privileges) first create an empty staging DB:
createdb -T template0 dummy
If the DB isn't too big and you have plenty of disk space, just restore the whole DB for simplicity. Else, selectively restore tables:
pg_restore --dbname=dummy --schema=public --table=foo my_backup
Or if it's just 1 or 3 records I would open the (default plain-text) backup with vim or your editor of choice, search manually, copy selected rows (lines in the backup) to a new file my_copy_file.sql. If you are sure about the data you can COPY into existing table directly, rows will be added.
If you need to manipulate data first COPY to a new, empty table. Create it first if it's not there, yet:
CREATE TABLE staging (Like foo);
COPY staging FROM 'path/to/my_productive_db`
Then proceed from there.
