Логіювання на рівні бази даних Postgresql

Логування даних на рівні тригерів в базі даних PostgreSQL

Логування даних на рівні тригерів є потужним засобом для відстеження та аналізу змін у базі даних. У PostgreSQL це досягається за допомогою тригерів, які можуть визивати функції після вставки, оновлення або видалення даних. Нижче наведено огляд того, як можна реалізувати логування даних на рівні тригерів в PostgreSQL.

Створення таблиці для зберігання журналу змін:

Перш за все, потрібно створити таблицю, яка буде використовуватися для зберігання журналу змін. У цій таблиці будуть зберігатися дані про кожну зміну, такі як час, тип операції, таблиця та інші деталі.

CREATE TABLE change_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255),
    operation_type CHAR(1),
    record_id INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Створення функції для логування:

Наступним кроком є створення функції, яка буде викликатися після виконання операцій вставки, оновлення чи видалення. У цій функції буде виконуватися вставка запису в таблицю change_log.

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Визначення типу операції
    IF TG_OP = ’INSERT’ THEN
        INSERT INTO change_log (table_name, operation_type, record_id) VALUES (TG_TABLE_NAME, ’I’, NEW.id);
    ELSIF TG_OP = ’UPDATE’ THEN
        INSERT INTO change_log (table_name, operation_type, record_id) VALUES (TG_TABLE_NAME, ’U’, NEW.id);
    ELSIF TG_OP = ’DELETE’ THEN
        INSERT INTO change_log (table_name, operation_type, record_id) VALUES (TG_TABLE_NAME, ’D’, OLD.id);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Створення тригера для виклику функції:

Останнім кроком є створення тригера, який пов'язаний з таблицею, за якою ви хочете відстежувати зміни. Цей тригер буде викликати функцію log_changes після виконання вставки, оновлення чи видалення записів.

CREATE TRIGGER log_changes_trigger
AFTER INSERT OR UPDATE OR DELETE
ON your_table
FOR EACH ROW
EXECUTE FUNCTION log_changes();

У цьому прикладі your_table - це таблиця, яку ви хочете відстежувати. Кожен раз, коли виконується операція вставки, оновлення або видалення в цій таблиці, тригер викликає функцію, яка вставляє відповідний запис в таблицю change_log.

Таким чином, застосовуючи логування на рівні тригерів в PostgreSQL, ви можете ефективно відстежувати зміни в базі даних і зберігати цю інформацію для подальшого аналізу чи відновлення даних.

Логіювання змін в форматі XML

Для створення універсального прикладу логування збереження даних в формат XML або YAML (YML), ми можемо використовувати функції, які автоматично аналізують структуру таблиці та генерують відповідний XML або YAML-документ. Для прикладу використаємо формат XML. Додайте до бази даних функцію, тригер та таблицю для логування:

Створення таблиці для збереження журналу змін:

CREATE TABLE change_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255),
    operation_type CHAR(1),
    record_data XML,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Створення функції для логування в форматі XML:

CREATE OR REPLACE FUNCTION log_changes_xml()
RETURNS TRIGGER AS $$
DECLARE
    xml_data XML;
BEGIN
    -- Створення XML-структури з нових та старих даних
    xml_data := XMLFOREST(NEW.*) - XMLFOREST(OLD.*);

    -- Визначення типу операції
    IF TG_OP = ’INSERT’ THEN
        INSERT INTO change_log (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’I’, xml_data);
    ELSIF TG_OP = ’UPDATE’ THEN
        INSERT INTO change_log (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’U’, xml_data);
    ELSIF TG_OP = ’DELETE’ THEN
        INSERT INTO change_log (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’D’, xml_data);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Створення тригера для виклику функції:

CREATE TRIGGER log_changes_xml_trigger
AFTER INSERT OR UPDATE OR DELETE
ON your_table
FOR EACH ROW
EXECUTE FUNCTION log_changes_xml();

В цьому прикладі your_table - це таблиця, яку ви хочете відстежувати. Функція log_changes_xml генерує XML-структуру, яка містить нові та старі дані запису, а потім ця структура записується в таблицю change_log разом із типом операції та іншими деталями.

Важливо зазначити, що цей приклад є узагальненим і може працювати для будь-якої таблиці у вашій базі даних, незалежно від її структури. Однак, вам може знадобитися адаптувати цей підхід в залежності від конкретних вимог і конвенцій вашого проекту.

Зберігання змін в форматі JSON

Для логування в форматі JSON універсальна функція та тригер можуть виглядати наступним чином:

Створення таблиці для збереження журналу змін:

CREATE TABLE change_log_json (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255),
    operation_type CHAR(1),
    record_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Створення функції для логування в форматі JSON:

CREATE OR REPLACE FUNCTION log_changes_json()
RETURNS TRIGGER AS $$
DECLARE
    json_data JSON;
BEGIN
    -- Створення JSON-структури з нових та старих даних
    json_data := json_build_object(
        ’new’, NEW,
        ’old’, OLD
    );

    -- Визначення типу операції
    IF TG_OP = ’INSERT’ THEN
        INSERT INTO change_log_json (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’I’, json_data);
    ELSIF TG_OP = ’UPDATE’ THEN
        INSERT INTO change_log_json (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’U’, json_data);
    ELSIF TG_OP = ’DELETE’ THEN
        INSERT INTO change_log_json (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’D’, json_data);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Створення тригера для виклику функції:

CREATE TRIGGER log_changes_json_trigger
AFTER INSERT OR UPDATE OR DELETE
ON your_table
FOR EACH ROW
EXECUTE FUNCTION log_changes_json();

У цьому прикладі, подібно до попереднього, your_table - це таблиця, яку ви хочете відстежувати. Функція log_changes_json використовує json_build_object для створення JSON-структури, яка містить нові та старі дані запису, і потім ця структура записується в таблицю change_log_json разом із типом операції та іншими деталями.

Цей приклад є узагальненим і може працювати для різних таблиць у вашій базі даних, незалежно від їх структури. Аналогічно до попереднього прикладу, вам може знадобитися адаптувати цей підхід залежно від конкретних вимог та конвенцій вашого проекту.

Збереження інформації в форматі YML

Для логування в форматі YAML (YML) ми можемо використати аналогічний підхід, але використовувати функцію для створення YML-структури. Проте, слід зауважити, що PostgreSQL сам за себе не має прямої підтримки YML, тому ми можемо зберігати YML як текстовий рядок у відповідному полі. Тут є приклад:

Створення таблиці для збереження журналу змін:

CREATE TABLE change_log_yml (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(255),
    operation_type CHAR(1),
    record_data TEXT, -- Текстовий рядок для YML
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Створення функції для логування в форматі YML:

CREATE OR REPLACE FUNCTION log_changes_yml()
RETURNS TRIGGER AS $$
DECLARE
    yml_data TEXT;
BEGIN
    -- Створення YML-структури з нових та старих даних
    yml_data := to_jsonb(NEW.*)::text;

    -- Визначення типу операції
    IF TG_OP = ’INSERT’ THEN
        INSERT INTO change_log_yml (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’I’, yml_data);
    ELSIF TG_OP = ’UPDATE’ THEN
        INSERT INTO change_log_yml (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’U’, yml_data);
    ELSIF TG_OP = ’DELETE’ THEN
        INSERT INTO change_log_yml (table_name, operation_type, record_data) VALUES (TG_TABLE_NAME, ’D’, yml_data);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Створення тригера для виклику функції:

CREATE TRIGGER log_changes_yml_trigger
AFTER INSERT OR UPDATE OR DELETE
ON your_table
FOR EACH ROW
EXECUTE FUNCTION log_changes_yml();

У цьому прикладі, подібно до попередніх, your_table - це таблиця, яку ви хочете відстежувати. Функція log_changes_yml використовує to_jsonb для перетворення нових даних в JSONB та потім в текстовий рядок. Цей текстовий рядок записується в таблицю change_log_yml разом із типом операції та іншими деталями.

Важливо зауважити, що YAML та JSON є різними форматами, і на практиці важко створити "чистий" YAML без використання специфічних типів, які визначає лише стандарт YAML. У цьому прикладі, для простоти, ми використовуємо текстовий рядок для збереження YAML-подібних даних.

Автор: Рудюк С.А. 2023. K2 Cloud ERP.