I have been trying to setup a home network of connected devices for... lets just say a long time! I have made some progress in recent days and the next step is to start using some sort of backing store to store some values.

While MongoDB works well with the Nodejs stack, it isn't well supported on Raspbian because Raspbian is 32bit and MongoDB stopped supporting 32 bit fully due to data size restrictions (biggest DB you can have is 2GB). Raspbian Stretch does provide an updated MongoDB but it is still a few (minor versions) short of the shiniest.

However using Jessie Backports I was able to install PostgreSQL 9.6 (the shiniest version). Having installed it, I had no idea what to do next. Here's my journey to get PostgreSQL to a place where I can start using it.

My dev environment's base OS is OSX and I run my DB in a VM hosted by VirtualBox. So I have setup a Debian Stretch VM called dev-db before I start with PostgreSQL installation. I am trying to setup a parallel runtime environment on a Raspberry Pi as well so if there are any exceptions to these steps I'll document them for the Raspbian as well.

Installing on Debian/Raspbian Stretch

Both the distros have the required repositories in the apt list, out of the box, so all you have to do is

sudo apt-get install postgresql-9.6

This will create a PostgreSQL default user called 'postgres'.

sudo service start postgres

Setting up your User Account

PostgreSQL databases are usually tied to a user. Out of the box, PostgreSQL users are not the same as *nix account users, however, if you a common name between your *nix account and PostgreSQL account things become much easier. So lets assume account I am using to log in to Debian stretch is called 'dbuser'. It is in the sudoers list and I logged in to the terminal/desktop using it.

First step is to change user from 'dbuser' to 'postgres' that PostgreSQL created during installation.

sudo -i -u postgres

This is the admin account for postgresql, in a production environment, it is a good idea to protect this with a password at least.

Next step is to tell PostgreSQL, we have a 'dbuser' account that we want to be able to use for create databases in PostgreSQL.

postgres@dev-db: ~$ createuser dbuser --pwprompt

It will ask for password, I entered the same password as my *nix account but that may not be a best practice.

Finally create a db for the dbuser

postgres@dev-db: ~$ createdb dbuser

Now log out of the postgres account and go back to dbuser account

postgres@dev-db: ~$ exit dbuser@dev-db: ~$

Start psql client and you should get logged into the pi database using the PLSQL

dbuser@dev-db: ~$ psql psql (9.6.4) Type "help" for help. pi=>

Enter \q to exit psql.

We have setup PostgreSQL and have established local client access

Enabling remote access

Default PostgreSQL setup enables local client access only. In Dev environment I like to access my VM or PI using a client on my local desktop/laptop. This needs a few additional steps.

Step 1: Open up firewall on VM or Raspberry Pi hosting the DB Server

sudo iptables -A INPUT -s 192.168.0.21 -p tcp --destination-port 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

sudo iptables -A OUTPUT -s 192.168.0.21 -p tcp --destination-port 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

Step 2: Edit PostgreSQL config to allow remote access

Edit the pg_hba.conf file

sudo nano /etc/postgresql/9.6/main/pg_hba.conf

Scroll to the bottom and add the following line

host all all 192.168.0.21/24 trust

Edit the file postgresql.conf

sudo nano /etc/postgresql/9.6/main/postgresql.conf

Find the setting 'listen_address' and set its value to the IP address of the VM/Raspberry Pi. This line maybe set commented (starts with #), so uncomment it first.

listen_addresses = '192.168.0.21'

Restart PostgreSQL server

sudo service postgresql restart

You should now be able to access if from a Remote Client.

I use the standard pgAdmin 4 and the connection settings were as follows

And done!

Up next compiling and setting up Redis on the Pi :-)