postgresql — восстановление случайно удалённых данных

Предположим, сделана и закомитчена команда

DELETE FROM testtable;

Если повезёт (не пройдёт VACUUM), восстановить данные можно так:

• остановить сервер
• отключить автовакуум
• запустить сервер
• сделать копию данных rsync при работающем сервере,
• остановить сервер
• сделать копию данных rsync при остановленном сервере,
• запустить сервер

Теперь у нас есть данные.

• Устанавливаем тестовую машину с постгресом,
• копируем ему сохранённые данные,
• отключаем автовакуум,
• настраиваем конфиги,

Скачиваем https://github.com/omniti-labs/pgtreats.git и берём из него pg_dirtyread,
компилируем, ставим. Надо обратить внимание, чтобы расширение легло туда куда нужно,
так как при установке на систему с несколькими версиями postgresql скопируется всюду
кроме нужной версии.

Это расширение постгреса. В документации написано запускать его как CREATE EXTENSION,
но например в 9.0 этого не было. Можно просто запустить SQL команду

CREATE FUNCTION pg_dirtyread(oid)
RETURNS setof record
AS 'pg_dirtyread'
LANGUAGE C;

(это подправленная оригинальная pg_dirtyread—1.0.sql

CREATE FUNCTION pg_dirtyread(oid)
RETURNS setof record
AS 'MODULE_PATHNAME'
LANGUAGE C;

Запускать надо в той базе, которая будет восстанавливаться.

Теперь можно следовать инструкции — найти удалённые данные:

SELECT * FROM pg_dirtyread(‘foo’::regclass) as t(bar bigint, baz text);

(bar bigint, baz text) — это определение столбцов исходной таблицы.
Берётся из соманды CREATE TABLE из схемы, исправляется — удаляются default, primary key, not null,
типы serial меняем на integer и так далее, то есть упрощаем схему.

Что получится — как повезёт.