Christoph Berg: Recovering Deleted Data From PostgreSQL Tables

PostgreSQL is very good at keeping your data safe so it doesn’t disappear by itself. Unfortunately, the same holds in reverse–if data has been deleted, it stays unrecoverable. In this article, we’ll explore options for recovering deleted data from PostgreSQL tables. Option 1: cautious people use transactions If you never make any mistakes when working with data, there won’t be any need for emergency procedures. But since we are all human, things like this happen: -- remove an entry from our contact list db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt'; DELETE 3 -- PANIC: WHY WERE THERE 3 ROWS AND NOT JUST ONE A good habit to acquire is to always use transactions, and only COMMIT them after checking everything is okay. -- remove an entry from our contact list db=> BEGIN; BEGIN db=*> DELETE FROM addressbook WHERE name = 'Heinz Schmidt'; DELETE 3 -- NOTICE: huh? db=*> ROLLBACK; ROLLBACK With a transaction, the unexpected row count was much less stressful. Option 2: careful people have backups If you have working backups, you can recover from any data handling mistakes by fetching everything from backup again. -- remove an entry from our contact list db=> DELETE FROM addressbook WHERE name = 'Heinz Schmidt'; DELETE 3 -- WARNING: oh no, not again db=> SELECT now(); now ----------------------------- 2024-03-11 16:22:25.1679+01 You could now perform a Point In Time Recovery (PITR) to a timestamp just before the undesirable DELETE command. Option 3: quick people use pg_dirtyread Let’s say options 1 and 2 failed, and we really have to recover the data from the running PostgreSQL instance. The good news is that DELETE does not actually delete data, it just marks it as invisible for subsequent transactions. This is done to allow concurrent transactions to still read the data. The actual removal of rows occurs only when VACUUM (or autovacuum) cleans up the table. (For those interested in more details about that mechanism, see the MVCC chapter in the PostgreSQ[...]