Обслуживание базы данных

Автоматическое обновление материализованных представлений

База данных системы содержит несколько материализованных представлений, которые требуют регулярного, автоматического обновления. Обновление реализовано при помощи bash скрипта запускающегося утилитой cron.

строка в файле /etc/crontab
*/15 * * * * /opt/lms/refresh-mv.sh lms mv_toxic_luminaires

Содержимое файла refresh-mv.sh

#!/usr/bin/env bash
psql -d $1 <<EOF
    \c $1;
    set search_path to lms;
    do \$\$
    declare 
        last_at head_metrics.read_at%type;
    begin
        select max(read_at) into last_at from head_metrics where read_at between now() - interval '15 minutes' and now();
        if last_at is not null then
            REFRESH MATERIALIZED VIEW $2;
            insert into mv_refresh_times(id, refreshed_at) values('$2', now()) on conflict (id) do update set refreshed_at = now();
            raise INFO '$2 has been refreshed';
        else 
            raise INFO '$2 does not require refresh';
        end if;
    end 
    \$\$
EOF

Скрипт принимает два параметра:

  • название базы данных
  • название материализованного представления

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

Добавление секций для таблиц содержащих метрики

Таблицы в которых хранятся метрики секционированы средствами СУБД Postgres. Например это таблицы:

  • meter_readings: показания измерительных приборов электроэнергии;
  • head_metrics: метрики блоков управления светильниками;
  • meter_instant_powers: мгновенные значения мощности со счётчиков в ШУНО.

Ожидается наличие секции на каждый месяц года. Создается командой следующего вида:

CREATE TABLE meter_instant_powers_202112 
PARTITION OF meter_instant_powers 
FOR VALUES FROM (to_timestamp('01.12.2021','DD.MM.YYYY')) TO (to_timestamp('01.01.2022','DD.MM.YYYY'));

где суффикс в названии содержит год и месяц в формате YYYYMM начала интервала.

После добавления секции необходимо перестроить индексы таблицы.

create unique index meter_instant_powers_idx on meter_instant_powers(read_at desc, meter_id);

Процедура добавления секций в среднем занимает 5-15 минут.

Материализованные представления

Светильники требующие внимания: mv_toxic_luminaires

Список смонтированных светильников оснащенных блоками управления с которых:

  • не поступили метрики в течении последних N часов;
  • две подряд метрики, при уровне димминга 100, показывают мощность потребления отличную от паспортной для данной модели светильника на X процентов в любую сторону;
  • две подряд метрики, при уровне димминга 100, показывают нулевую мощность потребления.

Используется

  • Для отображения списка светильников требующих внимания в консоли управления;
  • Для автоматического формирования email со списком проблемных светильников.