Overview

The database setup uses PostgreSQL with TimescaleDB and pg_cron extensions. TimescaleDB was chosen as the time series backend because of the excellent documentation, its low memory requirements and SQL query language support. In summary the following tables are created:

• A live table is created and filled every second with raw data from the Smartmeter. A retention policy is setup to delete live data which is older than 15 days to keep space requirements small.
• Two continuous aggregates are created which consolidate the one second live data into a daily energy and into 5-min power averages.
• An archive table allows to insert older data and to make corrections to the daily data, i.e. when the Smartmeter was not running 24/7.
• A materialized view overlays the archive data with the daily table to generate a complete data set. The daily energy bill is calculated from the daily energy consumption.
• Further materialized views consolidate the daily data into monthly and yearly summaries.
• pg_cron jobs are created to keep the materialized views up-to-date. Updates are scheduled every 15 minutes.

Installation

These instructions are for Arch Linux, but the PostgreSQL setup itself is in principle applicable to any other operating system (although not tried).

Install PostgreSQL and TimescaleDB packages:

$ yaourt -S postgresql timescaledb

Initialize the postgres database (as root):

$ su - postgres -c "initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'"

Install pg_cron package:

$ git clone https://aur.archlinux.org/pg_cron.git

Edit the PKGBUILD to bump the version from 1.2.0 to 1.5.1 (until the maintainer uploads an updated PKGBUILD):

pkgver=1.5.1
sha512sums=('f87fffad0ab603baa76c11c9e3c2d76407aea677d952a78f538cdaccd7b7ebea0789ee59bc9a26d7357548f13c2ffa1be053d5c46f621068e6f1015282fe9fe2')

Build and install the package:

$ makepkg -Cfs
$ sudo pacman -U pg_cron-1.5.1-1-aarch64.pkg.tar.xz

Database setup

Edit the main PostgreSQL config file to enable the TimescaleDB and pg_cron extensions:

$ nano /var/lib/postgres/data/postgresql.conf:

shared_preload_libraries = 'timescaledb,pg_cron'        # (change requires restart)
cron.database_name = 'pg_cron'

Enable and start the systemd service:

$ systemctl enable postgresql
$ systemctl start postgresql

Create users nodejs and grafana with default passwords:

psql -U postgres -c "CREATE USER nodejs WITH PASSWORD 'nodejs';"
psql -U postgres -c "CREATE USER grafana WITH PASSWORD 'grafana';"

Create smartmeter and pg_cron databases:

psql -U postgres -c "CREATE DATABASE smartmeter;"
psql -U postgres -c "CREATE DATABASE pg_cron;"

Create tables, queries and cron jobs

Smartmeter tables:

psql -U postgres < /usr/share/smartmeter/postgres/smartmeter.sql

Archive table:

psql -U postgres < /usr/share/smartmeter/postgres/archive.sql

Continuous aggregates:

psql -U postgres < /usr/share/smartmeter/postgres/aggregates.sql

Materialized views:

psql -U postgres < /usr/share/smartmeter/postgres/views.sql

Cron jobs:

psql -U postgres < /usr/share/smartmeter/postgres/pg_cron.sql

Run the following script to verify all tables, queries and jobs have been created successfully:

psql -U postgres < /usr/share/smartmeter/postgres/verify.sql

After some testing, enable overlay with the live data (real-time updates):

psql -U postgres -d smartmeter
smartmeter=# ALTER MATERIALIZED VIEW cagg_power SET (timescaledb.materialized_only = false);
smartmeter=# ALTER MATERIALIZED VIEW cagg_daily SET (timescaledb.materialized_only = false);

Customize your setup

The smartmeter.sql script pre-populates the sensors and plan tables with default meta data. The Smartmeter IDs need to be update to reflect the actual hardware, which are transmitted in the beginning of the raw datagram:

$ picocom -b 9600 -d 7 -y e /dev/ttyUSB0

/EBZ5DD3BZ06ETA_107                # serial number
1-0:0.0.0*255(1EBZ0100507409)      # custom ID
1-0:96.1.0*255(1EBZ0100507409)     # device ID
....

Issue the update query:

$ psql -U postgres -d smartmeter
UPDATE sensors SET 
  serial = 'EBZ5DD3BZ06ETA_107', 
  custom_id = '1EBZ0100507409', 
  device_id = '1EBZ0100507409'
WHERE "id" = '1';

The default electricity tariff set in the Smartmeter configuration file most likely needs to be updated as well to match your actual plan:

UPDATE plan SET price = '0.2426', rate = '14.66' WHERE id = '1';

If your contract finishes or your plan changes for any reason, you need to update the configuration file and insert the new plan information into the plan table:

INSERT INTO plan (id, price, rate) VALUES ('2', '0.2154', '15.99');

Fill the archive table with data

Import data from a backup

In case you have older data from a backup you have the possibility to import previous data into the archive table. The CSV file needs to have the following format:

$ cat archive.csv

bucket_1d,sensor_id,plan_id,energy_1d,total
2021-03-25T00:00:00Z,1,2,10.271127,4805.073300
2021-03-26T00:00:00Z,1,2,8.165218,4815.344427
2021-03-27T00:00:00Z,1,2,8.835051,4823.509645
2021-03-28T00:00:00Z,1,2,8.583229,4832.344696
...

Copy the data into the archive table:

psql -U postgres -d smartmeter -c "\COPY "archive" FROM 'archive.csv' DELIMITER ',' CSV HEADER"

Archive previously collected data

When the Smartmeter has gathered some data it will continuously show up in the aggregate tables. This live data is read only and cannot be edited directly. Hence the archive table also serves the purpose of permanently storing the aggregated data and making corrections in case the daily data is incomplete due to an offline period of the Smartmeter.

The data in the cagg_daily table which you want to edit needs to be copied into a temporary CSV file. For this to work you need to know the last day of the already processed archive data:

SELECT * FROM archive ORDER BY bucket_1d DESC LIMIT 1;

       bucket_1d        | sensor_id | plan_id |    energy_1d     |     total     
------------------------+-----------+---------+------------------+---------------
 2022-03-28 01:00:00+01 |         1 |       2 | 6.28441928000029 | 7816.79017053

Then you export the unprocessed data from the cagg_daily table:

COPY (SELECT * FROM cagg_daily WHERE bucket_1d > '2021-03-28 01:00:00+01' ORDER BY bucket_1d) TO '/tmp/daily.csv' WITH CSV DELIMITER ',' HEADER;

Finally import the edited and processed CSV file daily.csv into the archive table:

\copy "archive" FROM 'daily.csv' DELIMITER ',' CSV HEADER;

Adjust the start date of cagg_daily

The aggregate daily_view is a union between the archive table and the cagg_daily table. After populating the archive table there is probably now an overlap between the old and the new data. Hence the start date of cagg_daily (= end date of archive) needs to be modified to filter out the overlapping date range:

$ vim views.sql

CREATE MATERIALIZED VIEW daily_view
AS
SELECT
  bucket_1d AS time,
  energy_1d AS energy,
  energy_1d * price + rate * 12.0 / 365.0 AS bill,
  total,
  price,
  rate
FROM archive JOIN plan ON archive.plan_id = plan.id
GROUP BY bucket_1d, energy_1d, total, price, rate
UNION
SELECT
  bucket_1d AS time,
  energy_1d AS energy,
  energy_1d * price + rate * 12.0 / 365.0 AS bill,
  total,
  price,
  rate
FROM cagg_daily JOIN plan ON cagg_daily.plan_id = plan.id
--- insert end time of archive
WHERE bucket_1d > TIMESTAMP WITH TIME ZONE '2021-05-01 01:00:00+01'
GROUP BY bucket_1d, energy_1d, total, price, rate
ORDER BY time;

Now recreate the materialized views:

psql -U postgres -d smartmeter < views.sql

Optional: tune TimescaleDB

Install timescaledb-tune:

$ yaourt -S timescaledb-tune

Tune the PostgreSQL performance to your available cpus and memory:

$ timescaledb-tune -cpus 4 -memory 4GB -yes
$ systemctl restart postgresql

TimescaleDB upgrade

Once in a while the TimescaleDB maintainer release a new package and the Smartmeter database stops working. In order to fix this, the database needs to be upgraded to use the new version of the TimescaleDB library. Connect without loading any plugins and issue the alter extension command:

$ psql -X -U postgres -d smartmeter -c "ALTER EXTENSION timescaledb UPDATE;"

You might see a warning about the old format and PostgreSQL version 15:

WARNING:  Continuous Aggregate: public.cagg_daily with old format will not be supported with PG15. You should upgrade to the new format

In TimescaleDB 2.7 and above, continuous aggregates use a new format that improves performance and makes them compatible with more SQL queries. If the tables were created with TimescaleDB version older than 2.7, then the table format needs upgrading. There is an upgrade script you can call. The true parameter means you perform the upgrade in place and keep the old aggregate in cagg_daily_old:

CALL cagg_migrate('cagg_daily', true, true);

Then refresh the aggregate using the command given by the update script:

CALL public.refresh_continuous_aggregate('public.cagg_daily_new', CAST('2023-02-15 00:00:00' AS timestamp with time zone), NULL);

PostgreSQL major version upgrade

In case you have already upgraded the postgresql package through your package manager, the PostgreSQL server won't start anymore and you might get the following message:

An old version of the database format was found.
See https://wiki.archlinux.org/index.php/PostgreSQL#Upgrading_PostgreSQL

The PostgreSQL data format is incompatible between major versions (i.e. between 14.x and 15.x) and a manual upgrade needs to be performed. Hence I suggest to pin the PostgreSQL packages in pacman.conf so that the upgrade won't happen unintentionally without prior confirmation:

IgnorePkg   = postgresql postgresql-libs timescaledb pg_cron

To start the upgrade procedure, stop the old cluster (in case it is still running) and backup the current cluster data directory to a safe place:

# systemctl stop postgresql
# cd /var/lib/postgres
# sudo -u postgres scp -rp data/* [email protected]:/var/lib/postgres/data/

Then move the old cluster to a different directory and save the currently installed pg_cron library as it will be needed to later start the old cluster:

# mv data olddata
# cp /usr/lib/postgresql/pg_cron.so /tmp

Now upgrade the PostgreSQL packages and copy the previously saved pg_cron library to the library folder of the 'old-upgrade' 14.x cluster:

# pacman -Sy postgresql postgresql-libs timescaledb postgresql-old-upgrade timescaledb-old-upgrade
# cp /tmp/pg_cron.so /opt/pgsql-14/lib/

Recompile the pg_cron extension for the new PostgreSQL 15.x version:

$ cd /home/alarm/aur/pg_cron
$ makepkg -Cfs
$ sudo pacman -U pg_cron-1.5.1-1-aarch64.pkg.tar.xz

Disable the archive mode in postgresql.conf, create a temporary runtime directory and start the old cluster. Then upgrade the extensions in all databases to the latest version:

archive_mode = off

# mkdir /run/postgresql
# chown postgres:postgres /run/postgresql
# cd /var/lib/postgres
# sudo -u postgres /opt/pgsql-14/bin/pg_ctl start -D /var/lib/postgres/olddata
# psql -X -U postgres -h localhost -d smartmeter -c "ALTER EXTENSION timescaledb UPDATE;"

Create a new cluster data directory and a temporary folder:

# mkdir /var/lib/postgres/data /var/lib/postgres/tmp
# chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
# chmod 0700 /var/lib/postgres/data /var/lib/postgres/tmp

Now stop the old cluster and initialize the new cluster using the same initdb arguments that were used to create the old cluster:

# sudo -u postgres /opt/pgsql-14/bin/pg_ctl stop -D /var/lib/postgres/olddata
# sudo -u postgres initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'

Adjust the configuration files of new cluster (i.e. copy pg_hba.conf and postgresql.conf from olddata to data) to match the old cluster and enable the timescaledb and pg_cron extensions. Then perform the upgrade of the old cluster:

# cp -a olddata/pg_hba.conf olddata/postgresql.conf data/
# cd /var/lib/postgres/tmp
# sudo -u postgres pg_upgrade -r -b /opt/pgsql-14/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Wait until the process completes and start the new database. Optionally vacuum the tables and delete the old cluster's data files:

# systemctl start postgresql
# sudo -u postgres /usr/bin/vacuumdb --all --analyze-in-stages
# sh ./delete_old_cluster.sh

Backup and restore

In this section I will cover my database backup strategy and how to recover from a complete data loss using the point-in-time recovery. The postgres data directory gets copied once a week to a remote folder on my NAS server while the server is offline for a short time. This is equivalent to a PostgreSQL base backup taken with the pg_basebackup utility. For the very recent data I use continuous write-ahead-log (WAL) archiving which allows for point-in-time recovery. The WAL log records every change made to the database files and is replayed since the last checkpoint. For details how this works see the PostgreSQL documentation.

Enable automatic WAL archiving in postgresql.conf:

archive_mode = on
archive_command = 'rsync -aq --ignore-existing %p [email protected]:/home/wal/%f'
wal_buffers = 16MB

WAL log files which are older than 15 days are automatically deleted from the remote folder on the NAS. The log files, which take about 5 GB space in total, are pruned daily using the following command:

find /home/wal -mtime +15 -delete

To restore the data from the remote folder, we need to copy the PGDATA folder back to the database server, delete previous WAL files and create a special file which triggers the WAL logfile replay:

rsync -av [email protected]::NetBackup/postgres/data/* /var/lib/postgres/data
rm -rf pg_wal/* pg_dynshmem/* pg_notify/* pg_serial/* pg_snapshots/* pg_stat_tmp/* pg_subtrans/*
touch recovery.signal

Next we need to enable the replay of the WAL log files. Most importantly archival mode must be disabled on the server to prevent overwriting the WAL backup files on the remote folder. In postgresql.conf:

archive_mode = off
restore_command = 'rsync -aq --ignore-existing [email protected]:/home/wal/%f %p'

When the postgresql server is started, it begins to replay the write ahead logs between the time of the base backup and the WAL files available on the remote folder. Verify that the process completed successfully:

journalctl -f --unit postgresql
psql -U postgres -d smartmeter -c "SELECT time FROM live ORDER BY time DESC LIMIT 1"