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.
