Search code examples
postgresqlpostgresql-9.1database-restore

Accidently deleted data from PostgreSQL 9.1 table. Is there any way to restore the data?

We were doing an in house tool with data base as PostgreSQL 9.1. Accidentally running a delete script we lost the data in three tables.

We didn't have a backup. :(

A try on the manuals, it didn't help. A quick look at the data files in /PostgreSQL/9.1/data/base/ , we found that data is not deleted ( atleast not completely )

Is there a way to recover this data ?

Solution

  • Thanks Daniel, the directions were useful.

    And luckily we found a tool to do the same. Find the tool and instructions in the below link

    pg_dirtyread

    The instructions provided were simple and accurate.

    Additionally what we had to do was.

    1. Create temporary tables for restoration
    2. Instead of the SELECT statement in the instructions, used INSERT statements to insert into the backup tables.
    3. Filter the corrupted data ( manually )
    4. Insert back to the original tables.

    There were corrupted entries, but not many, as we could stop the running service immediately and avoid any updates to those tables.

    Thanks to OmniTI Labs team. This tool saved us the day ( night :) ) .