Introduction

In this post I am going to detail prototyping a test Postgresql database server, installing Postgres support on the Raspberry PI (for HTSQL) and then serving up the contents of the DB server via HTSQL over http.

The purpose of this task is to familiarise ourselves with each of the components in the system before we design how the actual database server will be setup and built out.

Throw away test machine

I like to keep a couple of old Windows machine on hand for building out quick test servers that I can use to prototype a system and then wipe out ready for my next project. I find this saves a lot of cost of going out and buying hardware, only to discover your original plan has changed for some technical/design reason and then having to purchase more equipment.

For this task I am using an old Pentium 4 machine running XP with about 60 Gigs of HDD space. I’ve hooked it up with a monitor and keyboard and plugged it into my router via a network cable. At this point it is worth grabbing the IP address of the machine you have just plugged in. This should be available via your routers DHCP table.

Consult your router documentation on how to access this information if you are not familiar with the process. Note: I checked my DHCP table after installing Ubuntu – since this is the only Ubuntu machine on my network currently, it appeared as “ubuntu” in the table

Now my first step is going to be to set it up as a dual-boot machine and load Ubuntu desktop edition onto it.

Ubuntu setup

Depending on the type of machine you use for prototyping the instructions below will obviously be different, for those of you with an old XP machine at hand, the steps below should work for you.

1. Downloaded Ubuntu’s latest version windows installed here.
2. Run the installer and follow the on-screen instructions – Note: select your OS type as Ubuntu.
3. Once your machine has rebooted into Windows follow the next steps:
4.  Right click My computer and select Properties
5. Select the Advanced tab from the pop-up box
6. Under Start-up and Recovery select Settings
7. Select Time to display list of operating systems checkbox – change the drop-down to 10 seconds
8. Select the Default operating system drop-down and change this to “Ubuntu”
9. Save your changes and re-boot your machine into Ubuntu.

Setting up Postgresql

Our Ubuntu machine is going to act as a database server, a Postgresql one in fact. For those of you not familiar with Postresql, it is a powerful, free, open source relational database and has been around for 15+ years. You can read more about it here.

Your first step will be to install the necessary software on Ubuntu.

Follow the instructions below to set up Postgresql

1. Open Terminal in Ubuntu and type the following command:

sudo apt-get install postgresql postgresql-client postgresql-contrib

2. The above command once it has completed running will have installed all the necessary components to start a Postgresql server on your Ubuntu machine.

3. Navigate via the command line to the following directory:

/etc/postgresql/9.1/main/

Note: depending on the version of Postgres you have installed the directory 9.1 may be different. As of August 26, 2012 9.1 is the latest version installed via the apt-get command above.

4. You will now need to edit the postgresql.conf in order to tell Postgresql to listen on the IP address assigned to your new dev machine via DHCP. Using vi type:

sudo vi postgresql.conf

Once you have the file open, edit the following line (you may have to un-comment it, and it will probably be set to localhost):

listen_addresses = 'ip address from dhcp table'

Save the file and exit.

5. We can now login to the “template1” DB that comes setup by default with pgsql

sudo -u postgres psql template1

From here for example we can change the password of the postgres account:

ALTER USER postgres with encrypted password 'my_new_password';

6. We now want to create a test database to serve via HTSQL, to do this we type the following:

CREATE DATABASE temptest;

7. With our new database created we can add a dummy table and a dummy row of data:

CREATE TABLE temperature (code char(5) );

INSERT INTO temperature (code) VALUES ('12345');

Feel free to add some more test rows.

8. Now we can quit postgresql and edit the configuration in order to allow remote access to the DB. In order to do this, you will need to edit the pg_hba.conf file

Change the configuration to include the following (you can use Vi):

local   all         postgres                          md5
host   all         postgres              samenet            md5

Then save and exit

9. We now need to restart our postgresql instance to pick up the changes, this can be done by:

sudo /etc/init.d/postgresql restart

Once the instance has restarted, we are now ready to make some configuration changes on the Raspberry PI.

Raspberry PI configuration

In order to connect our HTSQL server to our newly built Postgresql DB we need to install some extra packages on the Raspberry PI. First of all you will need ssh into the RPI.

Once logged in, follow the steps below:

1. Install python developer packages and the HTSQL postgres package as follows:

sudo apt-get install libpq-dev python-dev
pip install HTSQL-PGSQL

Once these have successfully been installed you should now see:

Successfully installed HTSQL-PGSQL psycopg2

Now we can connect to the postgresql instance.

2. We can test our connection to the testtemp database we created as follows:

htsql-ctl shell -p pgsql://postgres@host:port/database

The host above should be replaced with the IP address of the Ubuntu server you setup with postgresql installed on it. Also remember to include the port number after the IP address, by default postgresql runs on 5432. The final option is the database we created – temptest.

Once you can log in via the HTSQL shell to the DB instances then you are good to go with actually running a server.

3. Exit out of the HTSQL shell and then create a HTSQL server as follows:

htsql-ctl server 'engine://username:password@host:port/database'

engine should be replaced with pgsql, username and password are those that you set on the DB server, host and port are the DB servers IP address and postgresql port (as mentioned above this will probably be 5432) and finally database is our temptest database.

Once you have started the server you will see:

Starting an HTSQL server on raspberrypi:8080 over temptest

4. We can now check that everything is running as expected. Load up your web browser and in the URL bar type:  http://<ip of raspberry pi>:8080

If everything is working as expected you will see

Welcome to HTSQL!

Please enter a query in the address bar.

5. You can then hit the test table we created as follows:

http://<ip of raspberry pi>:8080/temperature

And see some test data (note I have added a few extra rows to my temperature table of random test data)

temperature
	code
1	12345
2	1245
3	56789

6. And that’s it, you now have HTSQL running on the Raspberry PI, pointing to a database server.

Conclusion

The above demonstrates the process of setting up a database server and using HTSQL on the Raspberry PI to communicate with it.  However this is only the beginning of what is required in order to build a system that can record our thermostat temperature data.

Using a prototype is useful for the purposes of ensuring we are building a modular system, where each component can be swapped out as necessary. Now we have demonstrated that we can plug a database into the system, we can concentrate on sourcing the right hardware that will replace the prototype.

Of course whilst our Raspberry PI now runs HTSQL, there is still a lot of work to be done on that end of things. We need to configure the RPI so that it is secure and also build out our controller software – which will use the data stored in the Postgres DB to tune our heating system.

The next steps then are going to be to complete the setup of the Raspberry PI and use our prototype DB server to test this configuration against. Once this is complete we will then concentrate on designing a relational database for the heating data, and building out the server hardware.