How to store sensor data from a Raspberry Pi into PostgreSQL

Introduction

The Raspberry Pi is a relatively inexpensive (~$50) but powerful general purpose computer. Although it was first developed for teaching basic computer science, it has now become incredibly popular for a variety of projects (even more here on HackerNews).

One of the main areas of usage for the Raspberry Pi has been IoT, in particular for edge deployments. In the context of IoT, developers can use a Raspberry Pi to serve as a prototype for larger-scale edge initiatives.

If you are collecting data from a Raspberry Pi powered IoT prototype, you will need a reliable database to store that data. Enter PostgreSQL, the fastest growing database today. However, your IoT prototype is likely going to generate a lot of time-series data, and one of the challenges of PostgreSQL is that it does not scale well for time-series data. That is why we built TimescaleDB - a time-series database powered by PostgreSQL. In fact, TimescaleDB looks and operates just like PostgreSQL.

In this tutorial, we demonstrate how to create an IoT system based on a Raspberry Pi (the “edge”) and Timescale Cloud (for hosted TimescaleDB/PostgreSQL and hosted Grafana) to enable time-series data storage, processing, and visualization.

Before you start

• Set up a Raspberry Pi device with a connected photocell light sensor
• Create a new TimescaleDB instance via Timescale Cloud
• Create a Grafana instance via Timescale Cloud
• Install the PostgreSQL database adapter for Python

Overview

All the code and detailed instructions for the below can be found in this GitHub repository.

“The Edge” is a Raspberry Pi that has been wired to a photocell light sensor. Running on the device is a script (photoresister.py) which is recording the status of the light sensor at 5Hz.

Each observation is sent to a TimescaleDB instance (hosted on Timescale Cloud) and recorded in a hypertable. A Grafana instance (also hosted on Timescale Cloud) is connected to the database to visualize the light values.

Step 1 - Setting up the cloud

We need to create two things in Timescale Cloud: a TimescaleDB instance (to store the light sensor readings sent from the device) and a Grafana instance (to visualize the data in TimescaleDB).

Connect to your TimescaleDB instance using psql and create the schema:

CREATE TABLE pi_obs(
  time timestamptz,
  metric text,
  value numeric);
  
SELECT create_hypertable('pi_obs', 'time', chunk_time_interval=>'1 week');

With the TimescaleDB instance running and the schema in place, it’s time to create a new Grafana instance.

Next, log into your Grafana instance and configure your TimescaleDB instance as a data source. With the data source in place, you can create a Grafana Dashboard that connects to that data source and will visualize the data in TimescaleDB.

You can create the dashboard by importing the Grafana dashboard JSON found here.

Note: The above Grafana dashboard JSON assumes you have created datasource “Timescale Cloud”, so please adjust as necessary.

Step 2 - Setting up the edge

We are starting with a standard Raspberry Pi setup with Raspbian. For our light sensor, these resources will explain how to configure a photocell:

• Photocell overview
• Basic photocell reading

With the photocell in place, we will setup the photoresistor.py python script. This script runs on the device, which will write aggregate sensor readings and batch insert the data into the TimescaleDB instance in the cloud.

Create a systemd startup script which will help to ensure the sensor comes back online during reboot. Be sure to enable the TIMESCALEDB_CONNECTION within the environment variable.

Step 3 - User experience

With all the pieces in place (and letting the system run for a bit), here’s a snapshot of how the Grafana dashboard fluctuates over the course of ~25 minutes. You can see the light sensor readings show when we are in the light and in darkness.

Added bonus: Since Grafana supports creating alert rules, if you are feeling adventurous, you can setup a rule to send an alert when we are in the dark. Checkout our docs for more information on configuring Grafana for alerting.

Conclusion

This is a small scale demonstration of what it looks like to get an IoT sensor system running with a Raspberry Pi and Timescale Cloud. We hope this gives you some ideas about what you can create on your own.

If you are ready to get started, you can use this demonstration to get up and running today!