Sensor network: postgresql on docker

For a while, I have been working on a sensor network/home IOT system. (for the lack of a better name, I have been calling it sensor net.) There are currently two sensors, a weather station with a solar panel and a wood stove water temp. These sensors upload their data using HTTP requests to the data collector, which is a raspberry pi 2. Originally, the web api would write that data into files on the raspberry pi. Last week or two, I created a postgresql database to use instead of the files. This database running on the raspberry pi was incredibly slow. It took about 20 seconds to fully load the web page.

The only option to speed up the database was to move it to another machine. Luckily I had an Asus Beebox laying around. It uses about 10 watts, from what I measured, but does have a lot more power than a raspberry pi. The trick to this was that I didn’t want to reprogram any of my sensors nor did I want to go through and reinstall PHP, Nginx, and the website code…just the database is what I wanted to move. This database will also move again in the future, so it has to be easy to bring up and take down…welcome DOCKER and auto SSH!

Docker is a lot like a virtual machine, but without the extra overhead. There are some good articles that talk about this better than I, but the basic concept is, it acts like a new computer with very few applications on it. These machines can be defined using a script. It is possible to restart them every hour and it wouldn’t interrupt services that much since they start up in seconds. It is also possible to take this definition move it to another machine and be running on the new machine.

AutoSSH is a program that will keep an SSH tunnel session alive. SSH is a protocol to remote into another machine. SSH can also be used to make a “tunnel” to another computer. For example, my raspberry pi has a tunnel to the beebox on port 5432. Any program running on my raspberry pi tries to access the local port 5432, it is immediately sent through the tunnel out to the beebox on port 5432, where my docker container is listening.

The Postgres container gave me a few issues. First one is that Docker containers don’t have a network time protocol installed. They have to get their time from the host server by mounting the clock over the container’s clock. This is done using the mount command when defining the docker container “-v /etc/localtime:/etc/localtime:ro”, as described from StackOverflow. If you want more information on docker mounting volumes their documentation seems pretty good.

These steps may not be exactly right, but here they are….

First, make sure ssh is installed on all machines.

sudo apt-get install ssh

Install docker on a ubuntu server. (I used ubuntu)

On the ubuntu server, get the postgresql docker image, which is on the docker hub. The site on the hub contains details about how to use it. A few things I needed to change was the PGDATA and the initdb.d directory.

sudo docker pull postgres

(I created three directories on the docker host/ubuntu machine. postgresOpt, to mount to the /opt directory, which will hold my database backup file. postgresInit, which will mount to the initialization directory. postgresDb, which will mount to the PGDATA directory to hold the database files.)

sudo mkdir /opt/dockerVolumes/postgresOpt

sudo mkdir /opt/dockerVolumes/postgresInit

sudo mkdir /opt/dockerVolumes/postgresDb

In the postgresInit directory I created a file called “init-db.sh” this file is what will create the database when the postgres container first runs and imports my database backup from /opt directory.

#!/bin/bash

set -e

psql -v ON_ERROR_STOP=1 –username “postgres” <<-EOSQL

CREATE DATABASE sensor_net;

EOSQL

psql –username “postgres” sensor_net < /opt/sensor_net.sql

psql -v ON_ERROR_STOP=1 –username “postgres” <<-EOSQL

\c sensor_net;

DROP ROLE IF EXISTS webapiuser;

CREATE ROLE webapiuser;

GRANT INSERT ON sensor_view TO webapiuser;
GRANT SELECT ON sensor_view TO webapiuser;
ALTER USER webapiuser WITH PASSWORD ‘password’;
EOSQL

One thing to note about the script if you wish to modify it. Inside the EOSQL block, is all SQL statements, so if you want comments have to use”–” if you want comments outside of those blocks have to use “#” for a comment.

At some point before here, we would have to backup our SQL database. A pd_dump as described in the PostgreSQL documentation won’t copy over the passwords, so those will have to be reset.

Assuming our database is sensor_net.sql and it is in the /opt/dockerVolumes/postgresOpt directory, we can launch the container with:

docker run –name some-postgres -p 5432:5432 -v /etc/localtime:/etc/localtime:ro -v /opt/dockerVolumes/postgresOpt:/opt -v /opt/dockerVolumes/postgresInit:/docker-entrypoint-initdb.d -v /opt/dockerVolumes/postgresDb:/var/lib/postgresql/data/pgdata -e POSTGRES_PASSWORD=mysecretpassword -e PGDATA=/var/lib/postgresql/data/pgdata -d postgres

Type sudo docker ps, to see the status of the containers. Note that if nothing is there or you stop a machine, the definition is still there. Type “sudo docker ps -a” to show the volumes that are still active. I hate to reference a paid page, but it is worth it if you are interested in docker. It is on plural sight.

Notice the “4ed718ad5d9”, that is the container ID. If you want to connect to the container as if it was a linux computer you can use “sudo docker exec -i -t <containerID> /bin/bash”, as described on askubuntu.com. You don’t have to enter the full container ID to reference it. Just need to enter enough characters so it can’t be confused with another container. For example, since I have one container I can reference the ID with just “4” otherwise I might need to reference it with “4ed”

Now that we should have our docker container running with ports exposed on the host. We need to redirect the postgres database on the raspberry pi to the ubuntu server.

On raspberry pi we need to create a public/private key pair, so we can login to the ubuntu machine with out using a password. (You really don’t want to have to enter the user name and password everytime the computer restarts and you need to run the tunnel command.) For more details check out  Jon Cairns’ blog.

To create this pair, go to the raspberry pi (the client) and enter the following: (I left my password blank because I didn’t want to enter the password in. From reading some articles, it sounds like the password encrypts the private key, which is stored on the machine. In other words…there has to be a better way than leaving it blank.)

ssh-keygen

ssh-copy-id ubuntuUser@ubuntuMachineIP

now we can ssh ubuntuUser@ubuntuMachineIP without entering a password.

It is a good idea to make sure we can tunnel before we use autossh…

on the raspberry pi:

ssh -L 5432:localhost:5432 ubuntuUser@ubuntuMachineIP

if that works then we can use

autossh -L 5432:localhost:5432 ubuntuUser@ubuntuMachineIP

There are other parameters for autossh that may be useful. Also, autoSSH has to run at startup. I haven’t gotten this far yet, but this site looks like it has the answer.

Over on the ubuntuMachine (in my case beebox) I have to make sure that the docker container also starts on startup. That process is described here.

Here is an image of the running website for those who are interested: (it still needs lots of work, but the data is there)

 

–edit

One more thing I should add is that as long as your PGDATA directory is being mounted to the same place…you can completely destroy your docker image and your database should be fine. Just create a new container and mount that Postgres data directory. Since all of the data in that folder on your host machine, any docker container you mount it on has access to it. DO NOT try to write to this directory from multiple containers. It could cause some issues.

 

–update 10/30/2016:2:00:

Getting the raspberry pi to autossh to the beebox at boot wasn’t as easy at the links in the article implied.

First issue: postgresdb wouldn’t DIE! I tried apt-get remove postgres, which removed some files, but it kept coming up at boot. I found the fix at: https://geekforum.wordpress.com/2015/05/20/purge-postgresql-uninstall-completely/

Problems with autossh:

  1. When I ran ssh-keygen that created a key for the currently logged in user. In my case “pi”.
  2. you have to tell auto ssh not to run in interactive mode.
  3. There is a prompt when you first ssh into a machine, which asks to accept a key from it. This will happen when autossh runs at start up, so before creating the startup entry run the autossh with sudo and accept the prompt.

Here are the full commands:

sudo ssh-keygen
sudo ssh-copy-id ubuntuUser@ubuntuMachineIP

now test the autossh:

sudo autossh -N -M 0 -i /root/.ssh/id_rsa -o “ServerAliveInterval 60” -o “ServerAliveCountMax 3” -L 5432:localhost:5432 ubuntuUser@ubuntuMachineIP

add autossh to boot up script. (there are many ways of doing this, but I chose the simplest with the crontab -e)

sudo crontab -e

now add the line to the end of the file…

@reboot sudo autossh -N -M 0 -i /root/.ssh/id_rsa -o “ServerAliveInterval 60” -o “ServerAliveCountMax 3” -L 5432:localhost:5432 ubuntuUser@ubuntuMachineIP

Now all should be working as it’s supposed to.

–update 10/30/2016:2:37:

Asus Beebox is now starting the docker container at boot. I followed the documentation from docker without any issues.

–update: 11/19/2016:

Everythin has been working great, but I should have added the code to link to the docker container…on the docker host enter

docker run -it –rm –link some-postgres:postgres postgres psql -h postgres -U postgres

Leave a Reply

Your email address will not be published. Required fields are marked *