{"id":9980,"date":"2022-01-06T10:00:16","date_gmt":"2022-01-06T09:00:16","guid":{"rendered":"https:\/\/www.credativ.de\/blog\/credativ-inside\/postgresql-and-undelete\/"},"modified":"2022-01-06T10:00:16","modified_gmt":"2022-01-06T09:00:16","slug":"postgresql-and-undelete","status":"publish","type":"post","link":"https:\/\/www.credativ.de\/en\/blog\/postgresql-en\/postgresql-and-undelete\/","title":{"rendered":"PostgreSQL and Undelete"},"content":{"rendered":"

pg_dirtyread<\/h1>\n

I recently updated pg_dirtyread<\/a> to work with PostgreSQL 14<\/a>. pg_dirtyread is a PostgreSQL extension that allows reading “dead” rows from tables, i.e., rows that have already been deleted or updated. Of course, this only works if the table has not yet been cleaned by a VACUUM command or autovacuum, PostgreSQL’s garbage collection. <\/p>\n

Here is an example of pg_dirtyread in action:<\/p>\n

# create table foo (id int, t text);<\/i>\nCREATE TABLE<\/i>\n# insert into foo values (1, 'Doc1');<\/i>\nINSERT 0 1<\/i>\n# insert into foo values (2, 'Doc2');<\/i>\nINSERT 0 1<\/i>\n# insert into foo values (3, 'Doc3');<\/i>\nINSERT 0 1<\/i>\n\n# select * from foo;<\/i>\n id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n 1 \u2502 Doc1<\/i>\n 2 \u2502 Doc2<\/i>\n 3 \u2502 Doc3<\/i>\n(3 rows)<\/i>\n\n# delete from foo where id < 3;\n<\/i>DELETE 2<\/i>\n\n# select * from foo;<\/i>\n id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n  3 \u2502 Doc3<\/i>\n(1 row)<\/i><\/pre>\n
Oops! The first two documents are gone. <\/p>\n
Let’s now use pg_dirtyread to view the table:<\/p>\n
# create extension pg_dirtyread;<\/i>\nCREATE EXTENSION<\/i>\n\n# select * from pg_dirtyread('foo') t(id int, t text);<\/i>\n  id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n  1 \u2502 Doc1<\/i>\n  2 \u2502 Doc2<\/i>\n  3 \u2502 Doc3<\/i><\/pre>\n
All three documents are still present, but only one of them is visible.<\/p>\n
pg_dirtyread can also display the system columns of PostgreSQL with information about the position and visibility of the rows. For the first two documents, xmax is set, which means that the row has been deleted: <\/p>\n
# select * from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text);<\/i>\n\n ctid \u2502 xmin \u2502 xmax \u2502 id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n (0,1) \u2502 1577 \u2502 1580 \u2502 1 \u2502 Doc1<\/i>\n (0,2) \u2502 1578 \u2502 1580 \u2502 2 \u2502 Doc2<\/i>\n (0,3) \u2502 1579 \u2502 0 \u2502 3 \u2502 Doc3<\/i>\n(3 rows)<\/i><\/pre>\n
Undelete<\/h1>\n
Caveat:<\/strong> I cannot promise that any of the ideas listed below will work in practice. There are some uncertainties and a good deal of complicated knowledge about PostgreSQL internals may be required to succeed. I would ask you to consider consulting your preferred PostgreSQL service provider for advice before attempting to restore data on a production system.  <\/em> Do not try this at work!<\/strong><\/p>\n
I always had plans to extend pg_dirtyread with an “Undelete” command to make deleted rows reappear, but unfortunately, I never got around to trying it out in the past. However, rows can already be restored by using the output of pg_dirtyread itself: <\/p>\n
# insert into foo select * from pg_dirtyread('foo') t(id int, t text) where id = 1;<\/i><\/pre>\n
However, this is not a real \u201cUndelete\u201d or “undo” – it only inserts new rows from the data read from the table.<\/p>\n
pg_surgery<\/h1>\n
Let’s welcome pg_surgery<\/a>, a new PostgreSQL extension that comes with PostgreSQL 14. It contains two functions for “surgery on a broken relationship”. As a side effect, they can also make deleted tuples reappear.  <\/p>\n
As I have now discovered, one of the functions, heap_force_freeze(), works well with pg_dirtyread. It takes a list of ctids (row positions) that it marks as “frozen” but also as “not deleted”. <\/p>\n
Let’s apply it to our test table using the ctids that pg_dirtyread can read:<\/p>\n
# create extension pg_surgery;<\/i>\nCREATE EXTENSION<\/i>\n\n# select heap_force_freeze('foo', array_agg(ctid))<\/i>\n    <\/i>from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text) where id = 1;<\/i>\n heap_force_freeze<\/i>\n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n\n(1 row)<\/i><\/pre>\n
And voil\u00e0, our deleted document is back:<\/p>\n
# select * from foo;<\/i>\n  id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n  1 \u2502 Doc1<\/i>\n  3 \u2502 Doc3<\/i>\n(2 rows)<\/i>\n\n#select * from pg_dirtyread('foo') t(ctid tid, xmin xid, xmax xid, id int, t text);<\/i>\n  ctid \u2502 xmin \u2502 xmax \u2502 id \u2502 t<\/i>\n\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500<\/i>\n (0,1) \u2502 2 \u2502 0 \u2502 1 \u2502 Doc1<\/i>\n  (0,2) \u2502 1578 \u2502 1580 \u2502 2 \u2502 Doc2<\/i>\n  (0,3) \u2502 1579 \u2502 0 \u2502 3 \u2502 Doc3<\/i>\n(3 rows)<\/i><\/pre>\n
Disclaimer<\/h1>\n
Most importantly, none of the above methods will work if the data you just deleted has already been cleaned by VACUUM or Autovacuum. This actively deletes the recovered storage space. Restore your data from the backup.  <\/p>\n
Since both pg_dirtyread and pg_surgery operate outside of the normal PostgreSQL MVCC machinery, it is easy to create corrupt data with them. This includes duplicate rows, duplicate primary key values, indexes that are not synchronized with the tables, broken foreign key constraints, and others.  You have been warned.<\/em><\/p>\n
Furthermore, pg_dirtyread does not (yet) work if the deleted rows contain any toasted<\/a> values. Possible other approaches are the use of pageinspect<\/a> and pg_filedump<\/a> to get the ctids of the deleted rows. Please make sure you have working backups and do not need any of the above methods.  <\/p>\n
We are Happy to Support You<\/h1>\n
The necessity of the above operations can be completely prevented in most cases by professional administration or setup of the PostgreSQL infrastructure by a competent service provider. If you are currently facing one of these problems and are looking for ways out of a predicament, please contact us by email at info@credativ.de or via the contact form. <\/p>\n
With over 22+ years of development and service experience in the PostgreSQL and Open Source area, credativ GmbH can professionally accompany you with an unprecedented and individually configurable support and fully support you in all questions regarding your Open Source infrastructure.<\/p>\n
 <\/p>\n
This article was originally written by Christoph Berg<\/em><\/p>\n
Also Worth Reading<\/h1>\n