Software – Database Server

PostgreSQL is described as the world’s most advanced open-source database, and underpins a number of features of the home hub. It runs quite happily on the raspberry pi.

sudo apt-get install postgresql

There are three areas that require configuring, in order to allow the controller and website scripts to access the database. In addition, we should include access from our desktop PC, which will allow us to administer the database using the management tool pgAdmin.

The first task is to edit the configuration file:

cd /etc/postgresql/9.4/main
sudo nano postgresql.conf

Uncomment and modify the listen_addresses connection setting and change the value to ‘*’ to listen on all interfaces. Save the file. Restart the database server.

sudo /etc/init.d/postgresql restart

The next task is to edit pg_hba.conf to enable connections from localhost and your PC.

sudo nano pg_hba.conf

Edit the file to enable connections from your desktop machine…

# IPv4 local connections:
host             all             all             client.ip.add.ress/32           md5

Access from the pi itself should already be configured…

# IPv6 local connections:
host             all             all             ::1/128                                         md5

save the file, and reload the configuration.

sudo /etc/init.d/postgresql reload

The final task is to set the postgres user password. Replace ‘raspberry’ with your chosen password.

sudo -u postgres psql -c "alter user postgres password 'raspberry';"

You should now be able to connect to the database server from pgAdmin running on your client PC.

In the next post we will create a skeleton database.