Database maintenance
Automatic update of materialized views
The system database contains several materialized views that require regular, automatic updates. The update is implemented using a bash script launched by the cron utility.
line in file /etc/crontab
*/15 * * * * /opt/lms/refresh-mv.sh lms mv_toxic_luminaires
File content 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
The script takes two directions:
- name of the database
- name of the materialized view
When updating the materialized view, it is recommended to make changes to the system table mv_refresh_times, to control the functionality of the mechanism in the user interface.
Adding sections for tables containing metrics
The tables in which the metrics are stored are partitioned by the Postgres DBMS.. For example, these are tables:
- meter_readings: electricity meter readings;
- head_metrics: metrics of luminaires heads;
- meter_instant_powers: instantaneous power values of the luminaires
A section for each month of the year is expected. Created by a command of the following form:
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'));
where the suffix in the name contains the year and month in the format YYYYMM.
After adding a section, you need to rebuild the table indexes.
create unique index meter_instant_powers_idx on meter_instant_powers(read_at desc, meter_id);
The procedure for adding sections takes on average 5-15 minutes.
Materialized views
Luminaires requiring attention: mv_toxic_luminaires
List of mounted luminaires equipped with heads from which:
- no metrics have been received during the last N hours;
- two metrics received in a row, with a dimming level of 100, show the power consumption that differs from the rating for a given luminaire model by X percent in any direction;
- two metrics received in a row, with a dimming level of 100, show zero power consumption.
Is used to
- To display a list of luminaires requiring attention in the control console;
- To automatically generate an email with a list of problematic luminaires.
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.