Complex Restore of Postgres data where multiples rows are deleted from a single row.

Complex Restore of Postgres data where multiples rows are deleted from a single row.

We ran into an issue with a client database where an application deleted upwards of 360 K records. In this process we found we needed to restore a database from several hours ago but we were unable to do a full system restore,  so here is the recepie for how to resolve

  1. restore a database gziped file to a ‘sandbox’ database
    #gunzip -c database_back.1014|psql -Uuser database_restore_20150909
  2. alter the public schema to rename it
    #echo ‘alter schema public rename to ‘restore_09122015’|psql -Uuser database_restore_20150909
  3. dump the database and load to the live db in the new schema
    #pg_dump -Uuser database _restore_20150909|psql -Uuser livedb
  4. create insert statements which will insert all missing records from the new schema into the live (public) schema
    #awk ‘/tbl/{gsub(“tbl”,””); print “insert into tbl”$1″ select r.* from restore_09122015.tbl”$1″ r left join tbl”$1” n using(“$1″id) where n is null; “}’ tbllist.save | psql –U user livedb.

Note that this relies on your database being in the same consistent naming format tblshortname.shortnameid.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *