Software 5. All About the Database

This documentation is for software version 0.2.0 and earlier. Click here to see this page in the latest docs.

On this Page

1. Overview

2. Database Structure

3. Database Location

4. Accessing Database Shell

5. Using a Remote InfluxDB Instance

6. Database Backup

7. Retention Policies

---

Overview

This project relies on a time-series database called InfluxDB. Time-series databases are ideal for storing values that rely heavily on time and InfluxDB has some advantages for this purpose over your standard MySQL/PostgreSQL database implementations. As of the v0.2.0 release, InfluxDB is installed directly into the Linux OS. (Previously, it used a docker container). If you're new to InfluxDB, please consult the official documentation, linked below. This page is only intended to provide specific details about my implementation of the project and how it uses InfluxDB.

InfluxDB Documentation: https://docs.influxdata.com/influxdb/v1.8/

Note: Make sure you select the documentation version that matches the version you've installed!

---

Database Structure

All data is saved to a single database named power_monitor. Inside of this database, there are several measurements (aka "tables" if you're familiar with SQL).

Here is an overview of the measurements and their fields and tags, followed by a more detailed description of each measurement:

Measurement: home_load

This measurement holds the calculated power and current that represent your entire home's load. This is intended to include only the CTs that are installed on your electrical panel's mains. By default, all of the CTs are included in the home load figure. You may need to specify which CTs should be included manually in the home_consumption_power and home_consumption_current variables in power_monitor.py.

Sample Queries:

     Peak power draw from today:

     SELECT MAX("power") FROM "home_load" WHERE time > now() - 1d;

     Total power consumption (in kWh) from today:

     SELECT integral("power") / 3600000 FROM "home_load" WHERE time > now() - 1d;

Measurement: net

This measurement holds the calculated net power and net current. Net power and net current are typically only useful for those with a secondary source of power such as solar panels, generators, or wind turbines. If you only have a grid connection, your net power will equal your home consumption power.

The status tag holds the current net status - either Producing or Consuming.

Sample Queries:

     Today's Net Power Amount (in kWh):

     SELECT integral("power") / 3600000 FROM "net" WHERE time > now() - 1d;

Measurement: solar

This measurement holds the calculated current, power, and power factor for the CT measuring your solar panel. Unless explicitly enabled in power_monitor.py, these values will always be zero. Please see the Wiki page about Solar Integration to set this up.

Sample Queries:

     Today's Peak Solar Output (in W):

     SELECT MAX("power") FROM "solar" WHERE time > now() - 1d;

     Today's Total Solar Power Production (in kWh):

     SELECT integral("power") / 3600000 FROM "solar" WHERE time > now() - 1d;

Measurement: voltages

This measurement holds the calibrated voltage reading from the AC transformer input. The tag v_input holds the voltage input channel number.

Sample Queries:

     Current Line Voltage:

     SELECT LAST("voltage") FROM voltages;

     Voltage Input 0's Current Voltage:

     SELECT LAST("voltage") FROM voltages WHERE "v_input" = '0';

Measurement: raw_cts

This measurement holds the calculated current, power, and power factor for all CTs. The tag ct holds the CT number for that particular entry.

Sample Queries:

     CT1's instantaneous amperage reading:

     SELECT LAST("current") FROM raw_cts WHERE "ct" = '1';

     Amount of power CT2 measured in the previous 6 hours (in kWh):

     SELECT integral("power") / 3600000 FROM raw_cts WHERE "ct" = '2' AND time >= now() - 6h;

     Amount of power CT3 measured during 0000 hrs and 0600 hrs on 12/25/2020 (in kWh):

     SELECT integral("power") / 3600000 FROM raw_cts WHERE "ct" = '3' AND (time >= '2020-12-25T00:00:00Z' AND time <= '2020-12-25T06:00:00Z');

---

Database Location

As of v0.2.0, the database is stored on disk in the /var/lib/influxdb/ directory. Before v0.2.0 (the docker implementation), a mapped folder at /opt/influxdb/ was used to link to the data inside the container.

---

Accessing Database Shell

Version 0.2.0 and above:

Access the Influx shell with the following command:

influx -database power_monitor -precision rfc3339

Version 0.1.x:

Since InfluxDB is running inside of a Docker container, you'll need to use a Docker command to get access to the InfluxDB shell. Run the following command from your Raspberry Pi's terminal to get access to the InfluxDB shell for manual data management.

Note: The InfluxDB container needs to be running!

docker exec -it influx influx -database power_monitor -precision rfc3339

You can now execute any of the example queries listed above.

Exit the database shell by either pressing Ctrl + d or entering the exit command.

---

Using a Remote InfluxDB Instance

TBD...

---

Database Backups

TBD...

---

Retention Policies

TBD...