Автоматическое исключение RDP-записей из sc_ttyrec на подписчике логической репликации

Контекст

Таблица aioc.sc_ttyrec — служебная таблица PAM-приложения, в которую пишутся записи сессий (ttyrec):

• текстовые логи SSH-сессий;
• видео-записи RDP-сессий.

Тип сессии (протокол) хранится не в самой sc_ttyrec, а в связанной таблице aioc.sc_sessions (связь по session_id).

Таблица физически партиционирована по месяцам (sc_ttyrec_part_YYYYMM, схема inheritance-партиционирования, не declarative).

Данные в базу попадают через подписочную логическую репликацию (logical replication) с отдельного publisher-инстанса. Задача: RDP-записи (видео) на этой базе-подписчике хранить не нужно вовсе — их необходимо отбрасывать сразу в момент репликации, не дожидаясь отдельного batch-удаления.

Почему выбран триггер, а не периодическая очистка

Периодический batch-DELETE (по расписанию, например через pg_cron) — рабочий вариант, но у него есть минусы именно для этого сценария:

• RDP-данные (bytea, видео) успевают физически записаться на диск, попасть в TOAST, реплицироваться по сети — и только потом удаляются;
• лишняя нагрузка на диск и WAL ради данных, которые заведомо не нужны;
• задержка между появлением записи и её удалением (данные какое-то время видны в базе).

BEFORE INSERT триггер, который отменяет вставку (RETURN NULL) до физической записи строки, решает это чище: RDP-данные вообще не попадают на диск подписчика.

Ключевые нюансы, характерные именно для logical replication и партиционирования

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

1. Обычные триггеры не срабатывают при применении реплики

Apply worker логической репликации применяет изменения с session_replication_role = replica. Триггеры в состоянии по умолчанию (ENABLE = origin) в этом режиме игнорируются.

Решение: явно перевести триггер в режим REPLICA:

ALTER TABLE aioc.<table_name> ENABLE REPLICA TRIGGER trg_skip_rdp_ttyrec;

2. Публикация была создана с явным перечислением партиций

Проверка на publisher:

SELECT * FROM pg_publication_tables WHERE pubname = 'pub_sc_ttyrec';

показала, что публикация включает не только родительскую таблицу sc_ttyrec, но и каждую партицию-лист (sc_ttyrec_part_202606, _202607 и т.д.) отдельной строкой.

Это означает, что apply worker на подписчике вставляет данные напрямую в партицию с соответствующим именем, минуя родительскую таблицу и её partition routing.

3. Партиционирование — legacy (inheritance), а не declarative

Таблица создана через классическое INHERITS, а не через PARTITION BY RANGE (...). Из-за этого триггер, созданный только на родительской таблице, не наследуется дочерними партициями автоматически (в отличие от declarative-партиционирования, где триггер клонируется на все партиции сам).

Итог: триггер, созданный только на sc_ttyrec, никогда не срабатывал для реплицируемых данных, потому что запись шла напрямую в конкретную партицию, а там триггера не было.

Итоговое решение

1. Функция триггера

CREATE OR REPLACE FUNCTION aioc.trg_skip_rdp_ttyrec()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_protocol varchar;
BEGIN
    SELECT access_protocol INTO v_protocol
    FROM aioc.sc_sessions
    WHERE session_id = NEW.session_id;

    IF v_protocol = 'RDP' THEN
        RETURN NULL;  -- отменяем вставку строки
    END IF;

    RETURN NEW;
END;
$$;

Логика: если для session_id входящей строки в sc_sessions найден протокол RDP — вставка отменяется. Если сессия ещё не реплицирована (v_protocol IS NULL) — строка пропускается как есть (безопасное поведение по умолчанию, чтобы не терять текстовые логи из-за гонки при первичной синхронизации).

2. Установка триггера на родителя и на все существующие партиции

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT relname FROM (
            SELECT 'sc_ttyrec' AS relname
            UNION ALL
            SELECT c.relname
            FROM pg_inherits i
            JOIN pg_class c ON c.oid = i.inhrelid
            JOIN pg_class p ON p.oid = i.inhparent
            JOIN pg_namespace n ON n.oid = p.relnamespace
            WHERE p.relname = 'sc_ttyrec' AND n.nspname = 'aioc'
        ) t
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS trg_skip_rdp_ttyrec ON aioc.%I', r.relname);
        EXECUTE format(
            'CREATE TRIGGER trg_skip_rdp_ttyrec
             BEFORE INSERT ON aioc.%I
             FOR EACH ROW
             EXECUTE FUNCTION aioc.trg_skip_rdp_ttyrec()',
            r.relname
        );
        EXECUTE format('ALTER TABLE aioc.%I ENABLE REPLICA TRIGGER trg_skip_rdp_ttyrec', r.relname);
        RAISE NOTICE 'Триггер установлен на %', r.relname;
    END LOOP;
END $$;

3. Очистка уже накопившегося бэклога

Данные, попавшие в таблицу до установки триггера, нужно удалить отдельно, одноразово:

CREATE OR REPLACE PROCEDURE aioc.purge_rdp_ttyrec(
    p_batch_size int DEFAULT 5000
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_deleted int;
BEGIN
    LOOP
        DELETE FROM aioc.sc_ttyrec t
        WHERE t.db_id IN (
            SELECT t2.db_id
            FROM aioc.sc_ttyrec t2
            JOIN aioc.sc_sessions s ON s.session_id = t2.session_id
            WHERE s.access_protocol = 'RDP'
            LIMIT p_batch_size
        );
        GET DIAGNOSTICS v_deleted = ROW_COUNT;
        RAISE NOTICE 'Удалено RDP-записей: %', v_deleted;
        COMMIT;
        EXIT WHEN v_deleted = 0;
    END LOOP;
END;
$$;

CALL aioc.purge_rdp_ttyrec(5000);

Удаление сделано батчами с промежуточным COMMIT, чтобы не держать одну длинную транзакцию и не раздувать WAL при работе с крупными bytea-данными.

Проверка результата

Диагностика: убедиться, что триггер установлен в режиме REPLICA на всех партициях, а не только на родителе.

SELECT tgrelid::regclass AS table_name, tgname, tgenabled
FROM pg_trigger
WHERE tgname = 'trg_skip_rdp_ttyrec'
ORDER BY 1;

Ожидается tgenabled = 'R' для родительской таблицы и для каждой партиции.

Проверка отсутствия новых RDP-записей после включения триггера (запустить дважды с интервалом, число не должно расти):

SELECT count(*)
FROM aioc.sc_ttyrec t
JOIN aioc.sc_sessions s ON s.session_id = t.session_id
WHERE s.access_protocol = 'RDP'
  AND t.rec_time > now() - interval '1 hour';

Важно для эксплуатации: новые партиции

Так как партиционирование реализовано через INHERITS (legacy), новые месячные партиции не наследуют триггер автоматически — это не declarative-партиционирование, где триггер клонируется сам.

Необходимо встроить создание и включение триггера (CREATE TRIGGER + ALTER TABLE ... ENABLE REPLICA TRIGGER) в процесс, которым создаются новые партиции sc_ttyrec_part_YYYYMM. Без этого шага при появлении очередной партиции защита от RDP-записей вновь исчезнет, и проблема повторится.

Резюме

• ←Предыдущая
• Следующая→