2

I know that Postgres uses MVCC and stores older versions of rows on disk. I would like to access (read-only) some old, discarded versions of a concrete row. How can I do it?

Konrad Garus
  • 53,145
  • 43
  • 157
  • 230
  • Here is similar question http://stackoverflow.com/questions/5325067/is-it-possible-to-access-an-old-version-of-a-row-in-postgres-that-hasnt-been-vac – Grzegorz Szpetkowski Aug 19 '11 at 12:02
  • @Grzegorz thanks. I found it was possible with pg_filedump, except for vacuumed rows. Something like http://fossplanet.com/f15/%5Bgeneral%5D-rows-missing-table-despite-fk-constraint-34240/index2.html – Konrad Garus Aug 19 '11 at 12:16

1 Answers1

2

In PostgreSQL 9.1 and later, the best solution for this is https://github.com/omniti-labs/pgtreats/tree/master/contrib/pg_dirtyread which is an extension that provides a functional interface in sql to access old, unvacuumed versions of rows. Other tools may exist for other dbs. This works well for a number of cases data recovery, just exploring the utility of mvcc, and the like. With some effort it might work on earlier versions.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182