Truncated a table in postgresql. How to recover it back?

Question

Truncate table tablename;

How to recover it back in dbeaver

I believe this question might be a duplicate of http://stackoverflow.com/questions/28321999/i-truncated-a-table-how-do-i-get-the-data-back — craig.kaminsky, May 16 '16 at 14:54

score 1 · Accepted Answer · answered May 16 '16 at 15:01

1

You cannot. See doc: http://www.postgresql.org/docs/9.1/static/sql-truncate.html

It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

The space is returned to OS, it could be occupied by new data etc.

answered May 16 '16 at 15:01

rouen

5,003
2
25
48

If you're desperate enough you could attempt standard deleted file recovery techniques. If you were able to recover the file, you could try "plumbing it back into" the catalog and you might be able to read most of it, depending on how much had recovered as well as some other considerations. – Jim Nasby May 16 '16 at 17:13

score 1 · Answer 2 · answered May 17 '16 at 10:33

Tuncate uses file-level operations to delete the data, and this has a number of implications:

On commit you cannot recover
This is not MVCC safe. In other words, other concurrent transactions see the table as empty despite transaction isolation levels that should let them see the old rows.

This means you basically have to recover from backup and this is a great case to use to warn that backups are about more than hardware failure. They are also there in case of administrator error (and that is why replication is not a backup).

Truncated a table in postgresql. How to recover it back?

2 Answers2