Using Timescale DB on Edge Device

Recently we got a chance to chance to work with Timescale DB on our edge device. For those who are new to Timescale DB, its an additional extension on top of PostgreSQL, to save timeseries data in it.

For that we can use any image from the public docker hubs. Here the below manifest will show how you can use the same for your deployments.

you need to change the password with yours

"postgres": {
"settings": {
"image": "your_acr/timescaledb:2.1.1-pg13",
"createOptions": "{\"HostConfig\":{\"Binds\":[\"/var/lib/docker/volumes/postgres/_data:/var/lib/postgresql/data\"],\"PortBindings\":{\"9180/tcp\":[{\"HostPort\":\"9180\"}],\"5432/tcp\":[{\"HostPort\":\"5432\"}]}},\"LogConfig\":{\"Type\":\"json-file\",\"Config\":{\"max-size\":\"10m\",\"max-file\":\"3\"}}}"
},
"type": "docker",
"version": "1.0",
"env": {
"POSTGRES_PASSWORD": {
"value": "yourpassword"
}
},
"status": "running",
"restartPolicy": "always"
}

Once we deploy the modules to edge device we are good to set up the tables.

We can use Azure Data Studio to connect to the Timescale DB by using Postgres extension on the same

once we are connected we can run below steps

Create Table
Create table for saving the information from your edge

CREATE TABLE SensorReadings ( 
temperature FLOAT,
pressure FLOAT,
createdtime timestamp );

Updating permission
This step will be applicable if you need to give permission to any other users to the table. Here the ‘postgres’ is the default username of the server. you can use specific username based on your need.

ALTER TABLE SensorReadings OWNER TO postgres;

Creating Hyper Tables
This is the different and main step we do to make the previously created table to a hyper table. This will create the aove table as a timeseries data table by creating respective index tables in it.
Here we need to specify the table name along with the timestamp column name to prepare it as a timeseries table.

SELECT create_hypertable (‘SensorReadings’, ‘createdtime’);

Now we are good to insert data into postgres

You can use the below, very basic steps to try insering data to the DB or you can use EF Core to connect to the same. You need to update the connection string with appropriate vallues

public async Task writetotimescale(string messageString)
{
  var connString = "Server=192.168.0.1;Port=5432;Database=postgres;User Id=postgres;Password=yourpassword;";
  
  using (var conn = new NpgsqlConnection(connString))
  {
    try
     {
      dynamic sr = JsonConvert.DeserializeObject(messageString);
      Console.WriteLine(messageString);
      string sql = $"insert into SensorReadings VALUES ({sr.machine.temperature},{sr.machine.pressure},'{sr.timeCreated}')";
      await conn.OpenAsync();
      using (var cmd = new NpgsqlCommand(sql, conn))
      {
        await cmd.ExecuteNonQueryAsync();
      }
     }
     catch (Exception ex)
     {
        Console.WriteLine(ex.Message);
     }
   }
}

After insering the data you can use Azure data studio to check the data.

You can also use time series based query on the same table.