Возникла необходимость вести аудит изменения данных в существующей системе.
Требования:
- Простота подключения/отключения логгирования отдельных таблиц.
- Сократить до минимума изменения в уже существующих функциях БД.
- Минимизировать деградацию производительности.
Первая мысль была добавить в логгируемые таблицы поля _user, _create_date, _delete_date.
На операции INSERT, UPDATE, DELETE повесить триггеры, работающие с этими полями.
При добавлении записи заполнять поля _user и _create_date.
Вместо обновления делать копию обновляемой строки (с измененными значениями), а в самой обновляемой строке заполнять поле _delete_date.
Вместо удаления записи заполнять поле _delete_date.
При обращении к такой таблице в блок WHERE необходимо добавлять _delete_date IS NULL.
Этот механизм мог бы сработать, если бы был заложен в архитектуру БД изначально, но у нас к моменту внедрения логгирования было написано более 3000 функций, в каждую из которых пришлось бы вносить изменения.
Затем появилась идея хранить логи отдельно от данных. Идея заключалась в следующем:
В схеме logs создается копия структуры таблицы плюс несколько служебных полей.
На каждую логгируемую таблицу вешается триггер, который выполняет всю грязную работу по сохранению изменившихся данных.
CREATE OR REPLACE FUNCTION logs.tf_log_table()
RETURNS trigger AS
$BODY$
DECLARE
query text;
safe_table_name text;
BEGIN
SELECT quote_ident(nspname||'.'||relname)
FROM pg_class cl INNER JOIN pg_namespace nsp ON (cl.relnamespace=nsp.oid)
WHERE cl.oid=TG_RELID INTO safe_table_name;
query='INSERT INTO logs.'||safe_table_name||' SELECT ($1).*, now(),$2,session_user;';
IF (TG_OP = 'DELETE')
THEN
EXECUTE query using OLD,'D';
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE query using OLD,'U';
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE query using NEW,'I';
RETURN NEW;
END IF;
/*Если таблица для логов не создана или ее структура отличается от текущей, то пересоздаем ее, и снова пытаемся записать туда данные*/
EXCEPTION
WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN
PERFORM logs.create_log_tables(TG_RELID::regclass);
IF (TG_OP = 'DELETE') THEN
EXECUTE query using OLD,'D';
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE query using OLD,'U';
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE query using NEW,'I';
RETURN NEW;
END IF;
/* Если что-то другое, игнорируем ошибку и возвращаем стандартный ответ*/
WHEN OTHERS then
IF (TG_OP = 'DELETE') THEN RETURN OLD;
ELSE RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
В этом триггере есть несколько конструкций, свойственных только plpgsql, попробую расписать их более подробно.
SELECT quote_ident(nspname||'.'||relname)
FROM pg_class cl INNER JOIN pg_namespace nsp ON (cl.relnamespace=nsp.oid)
WHERE cl.oid=TG_RELID INTO safe_table_name;
TG_RELID специальная переменная, существующая только при срабатывании триггерной функции, в ней хранится идентификатор таблицы, которая вызвала триггер.
С ее помощью мы генерируем имя таблицы, в которую будут записаны логи.
query='INSERT INTO logs.'||safe_table_name||' SELECT ($1).*, now(),$2,session_user;';
Для вставки самих данных используется динамический SQL.
На место переменной $1 подставляются данные из строки, на которой сработал триггер (туда подставляется целиком вся строка, ее необходимо развернуть на отдельные поля — это делается конструкцией (ROW).*
now() — функция, возвращающая время начала транзакции.
session_user — имя пользователя текущего сеанса
IF (TG_OP = 'DELETE')
THEN
EXECUTE query USING OLD,'D';
TG_OP — еще одна переменная, существующая только в триггерных функциях, в ней хранится имя операции, от которой сработал триггер (INSERT, UPDATE, DELETE или TRUNCATE)
OLD, NEW — в этих переменных хранятся старая и новая версия строки.
Дальше, на тот случай, если что-то пойдет не так, предусмотрена достаточно простая обработка ошибок:
EXCEPTION
WHEN SQLSTATE '42P01' OR SQLSTATE '42801' OR SQLSTATE '42804' THEN
PERFORM logs.create_log_tables(TG_RELID::regclass);
Если была изменена структура таблицы, или же по какой-то причине таблица с логами не была создана, то она создается заново и выполняется попытка записать в нее лог.
Во всех остальных случаях при ошибке процедура логгирования игнорируется.
CREATE OR REPLACE FUNCTION logs.create_log_tables(table_oid oid) RETURNS int AS $BODY$
DECLARE
log_namespace oid=(SELECT oid from pg_namespace WHERE nspname='logs');
p_relname text;
new_tbl_name text;
safe_table_name text;
BEGIN
SELECT relname FROM pg_class WHERE oid=table_oid INTO p_relname;
SELECT quote_ident(nspname||'.'||relname) FROM pg_class cl inner join pg_namespace nsp ON (cl.relnamespace=nsp.oid) where cl.oid=table_oid INTO safe_table_name;
/*Генерация нового имени для таблицы*/
SELECT safe_table_name||'_'||(now()::date)::text||'('||i||')' FROM generate_series(1,10) a(i)
WHERE safe_table_name||'_'||(now()::date)::text||'('||i||')' not in(select relname from pg_class where relnamespace=log_namespace and relpersistence='p')
ORDER BY i LIMIT 1 INTO new_tbl_name;
/*Переименовываем старую таблицу с логами*/
EXECUTE 'ALTER TABLE IF EXISTS logs.'||safe_table_name|| ' RENAME TO '||quote_ident(new_tbl_name)||';';
/*Создаем таблицу с такой же структурой, как логгируемая, плюс служебные поля*/
EXECUTE 'create table logs.'||safe_table_name||' (like '||table_oid::regclass||');';
EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_timestamp" timestamp with time zone;';
EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_operation" char;';
EXECUTE 'ALTER TABLE logs.'||safe_table_name||' ADD COLUMN "'||p_relname||'_user" text;';
/*Подключаем триггер*/
EXECUTE '
DROP TRIGGER IF exists tr_log_table ON '||table_oid::regclass::text||';
CREATE TRIGGER tr_log_table
BEFORE UPDATE OR DELETE OR INSERT
ON '||table_oid::regclass::text||'
FOR EACH ROW
EXECUTE PROCEDURE logs.tf_log_table();';
RETURN 0;
end;
$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
Алгоритм подключения логгирования достаточно прост. Для начала на основе имени логгируемой таблицы создается ее клон (если таблица с таким именем уже существовала, старая таблица переименовывается), затем в этот клон добавляются необходимые служебные поля, и на логгируемую таблицу подключается триггер.
Плюсы такого варианта:
- Не нужно ничего менять в существующих функциях.
- Запросы на выборку никак не пострадают по производительности.
- При изменениях в структуре логгируемой таблицы таблица с логами будет автоматически пересоздана.
- Логи можно быстро очистить, удалив старые таблицы.
Комментарии (27)
VolCh
10.03.2017 12:21+1Насколько проседает производительность при вставках и изменениях в устоявшихся режимах?
XareH
10.03.2017 13:35+1Вставки и изменения у нас обернуты в функции с достаточно сложной логикой, на их фоне влияние двойной записи на диск стремится к нулю.
Сильная потеря производительности может получиться только на таблицах с частыми обновлениями, или на таблицах с большими записями(т.к. там используется механизм TOAST и при обновлении такой строки постгрес сильно оптимизирует эту операцию)
yusman
10.03.2017 12:28Спасибо за статью, а можно ли читать изменения из WAL? На одной из конференций я слышал про такую возможность стороннего плагина (берем из WAL и кладем в очередь), но что-то найти не могу.
yusman
10.03.2017 12:47Нашел вот такую штуку
https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw — читает WAL и генерит SQL стейтментыBasilioCat
10.03.2017 13:12На мой взгляд, идея не самя удачная — WAL логи не слишком хорошо подходят для извлечения из них запросов, это лог изменения данных на диске. Из самих WAL файлов (без наличия реплики с реплицированной базой) эту информацию извлечь невозможно.
arzonus
10.03.2017 12:36+2Не знаю какая у вас структура таблиц, но хотелось узнать какой был просед производительности при внедрении?
Все таки используется динамический SQL при каждой операции с таблицей.XareH
10.03.2017 13:18В нашем случае проседание производительности практически нулевое, т.к. логгируются таблицы, которые не часто и не сильно изменяются.
Динамический SQL используется не при каждой операции, а только при изменении данных.
К тому же при вставке данных в таблицу логов не производится никаких проверок(внешние ключи)
Проседание производительности может получиться при массовой вставке, но мы отключаем логгирование в таких случаяхarzonus
10.03.2017 17:21А каким образом отключаете?
В рамках транзакции удаляете триггер, а потом добавляете заново?
AlexZaharow
10.03.2017 14:06А какие действие предусмотрены на случай изменения схемы данных?
Я к тому, что можно ли было пользоваться форматом JSON для хранения логов конкретной записи? Сама «ячейка» логов (если так можно выразиться) является JSON-массивом, куда вы только добавляете данные.Такой способ вполне может переварить и изменение колонок и изменение данных логов через триггеры. И таблица логов вполне себе может быть одна. (не знаю, насколько критична эта схема будет по производительности под хорошей нагрузкой).
heleo
10.03.2017 14:21Как по мне главный минус такого подхода это большая куча в одной таблице. Зато бесспорный плюс то, что лог будет удобнее читать при работе сразу с несколькими таблицами в одной функции.
Как вариант можно добавить дробление таблицы с логами горизонтально по дате или как уже тут проделано по имени таблицы.
XareH
10.03.2017 14:47При изменении схемы данных, возникнет исключение и таблица с логами будет пересоздана.
Насчет использования JSON не задумывались, можно попробовать
heleo
10.03.2017 14:12Знакомая тема. И всё же чем вам не угодил вариант с дополнительными служебными полями таблиц на триггерах? Ограниченность лога я так понимаю?
И ещё вопрос про обращение к таблице. Вызов напрямую или используете view?VolCh
10.03.2017 14:27Есть два основных варианта логирования изменений без дополнительных таблиц:
- просто редактирование записи с обновлением служебных полей — сложно всю историю и хранить, и смотреть
- создание новых записи при обновление с пометкой старых как архивных (собственно как сам постгресс делает, если ничего не путаю) — сложно выбирать текущее значений, проблемы с первичными ключами и уникальными индексами, да и простыми, раздувание таблицы с оперативными данными.
Проще логи хранить отдельно всегда, не пытаясь их прилепить к основной записи. Или даже вообще на уровне архитектуры приложения переходить на событийную модель и не хранить состояния явно (или явно только в целях кэширования), а редусить его из потока событий до заданного предела, то есть делая логи не служебными записями, а полноправными бизнес-событиями.
heleo
10.03.2017 16:13Спасибо, мне это известно) Вопрос больше касался почему не хватало просто наблюдать за информацией о том что со строкой происходит. В требованиях выданы лишь качественные характеристики к системе логирования, но не было сказано чего именно от лога ожидают. У меня в своё время была такая же система из служебных полей, по задумке автора интерес представляло только дата изменения и кто поменял, если запись удалялось то это даже не интересовало.
Причём как по мне тут прежде всего надо разобраться с самим понятием, а что есть «лог». И если уж быть честным, то у меня язык не поворачивается назвать такую систему логом) больше похоже на метаинформацию фаловой системы. Она не столь нужна для лога, сколько для быстрого получения информации которую вполне можно взять как раз из логирования, но скорость при этом критична.VolCh
10.03.2017 18:30по задумке автора интерес представляло только дата изменения и кто поменял, если запись удалялось то это даже не интересовало
Процентов в 90 случаев где-то за несколько месяцев эксплуатации бизнес начинает интересовать не только кто и когда поменял, но и что было до изменения. Причём уверены, что раз можем посмотреть кто последний поменял, то вытащить всю историю изменения как самой записи, так и метданных (кто, когда, откуда и т. п.) это максимум полчаса чтобы запрос написать сложный.
heleo
10.03.2017 16:36Создание новых записей в той же таблице и пометка их как архивные это не лог и с постгрессом здесь схожесть только в том, что записи постгресса хранятся кортежами которые не меняются, а просто создаются новые. При этом если включить autovacuum, то старые записи сотрёт, но это так вкратце и не совсем точное описание)
Что же касается множественного набора записей, то это в первую очередь прерогатива архитектуры.
По такому принципу как правило строятся системы версий. Вкратце, если запись создана с такими и только такими параметрами, то любое их изменение влечёт изменение зависимых от неё (или ссылающихся на неё) записей.
По такому принципу строят, к примеру, международный словарь стран и их кодов ISO-3166. Стоит стране поменять площадь государства как сразу обновляется её номер.
Проще логи хранить отдельно всегда, не пытаясь их прилепить к основной записи.
А тут я с Вами полностью согласен, так как цель логов не только сохранить предыдущее состояние объекта, но и дополнительную метаинформацию и при этом ни в коем случае не влиять на саму информацию своим существованием.
XareH
10.03.2017 14:41Еще раз нипишу минусы доп полей
1.Удаленные записи продолжают лежать в таблице(нужно вносить изменения в существующие функции, дополнительные расходы при выборках)
2.Если одна запись обновляется неоднократно, теряется история изменений
3.Могут возникнуть ошибки при добавлении новых ограничений на таблицу, т.к. логгировать предполагалось не все таблицы, то логгируемая таблица вполне может ссылаться на нелоггируемую
XareH
10.03.2017 14:55Приложение обращается к таблицам через обертки из функций, сами функции обращаются напрямую в таблицы.
heleo
10.03.2017 15:47Приложение обращается к таблицам через обертки из функций, сами функции обращаются напрямую в таблицы.
Я имел ввиду просмотр данных. Для просмотра данных так же используете функции с возвратом? Если да, то это напоминает механизм доп контроля над данными таблиц доступных оператору.
smple
12.03.2017 00:39эх извращаться так извращаться.
А что мешало в первом варианте что вы писали сделать следующее:
- Переименовать таблицу которую логируете, ну любое имя или префикс.
- Создать новое представление (view) с именем таблицы которое было до переименования чтобы он брал данные из таблицы без флага _deleted например
- Повесить тригеры на изменения данных во view (insert, update, delete)
Итого получаем, старые скрипты работают с view вместо таблицы и даже не замечают подмены, действия на данными во view обрабатываются тригерами
XareH
12.03.2017 21:36Такой вариант тоже пробовали, проблема в том, что некоторые функции работают напрямую с системными таблицами(pg_class/pg_attribute/pg_constraint) и на логгируемых таблицах они ломались.
К тому в варианте с представлениями удаленные/обновленные записи будут влиять на производительность при SELECT`ах
zip_zero
12.03.2017 21:18Класс, сделали аудит «на триггерах», потому что скорость IUD настолько мизерная, что триггер — не bottleneck.
Эт понятно как его делать, было бы интереснее посмотреть, как проходил ваш поиск, какие решения рассматривали. Какие были бизнес-требования (хотели параметры «до» и «после» или достаточно было SQL запросов — по ним и так понятно, что было до, а что стало)?
Кстати, https://github.com/pgaudit/pgaudit/blob/master/README.md — не смотрели или не подошел?
Rupper
12.03.2017 22:35Неэффективный способ.
Аргументы.
1. При обновлении одной записи приходится сохранять всю запись.
2. Управление историей требует специальных инструментов и обработки каждой отдельной таблицы.
3. Изменение структуры таблицы требует изменения структуры таблицы лога, что опять же делается для каждой таблицы одинаково и в вашем вариенте не зависит от размера таблицы.
Как избавится от этих проблем можно посмотреть в Ultimate 2C
ls18
Работал с нечто подобным. В вашем варианте вы создаете отдельно таблицу логгирования на каждую таблицу данных? В PostgreSQL нет триггеров событий? Или я не внимательно читал. В Oracle есть такое. В любом случае спасибо за статью, сейчас работаю с PostgreSQL и была идея реализовать у себя логгирование на основе лог-таблиц. Пока делаю через первый вариант, описанный в статье.
ls18
UPDATE
Увидел создание таблицы. Я копал в сторону триггеров событий, что бы при создании таблицы данных создавалась и лог таблица для нее. Правда что-то как-то заморчоено все с этими триггерами в PostgreSQL.