How to install and setup Postgres on a Raspberry Pi (Part 2)
Introduction
The Raspberry Pi is an inexpensive computer the size of a credit card that plugs into a monitor or TV, but uses a standard-size computer keyboard and mouse. It allows for a simple introduction to programming languages, such as Python. This is part two of a tutorial series explaining how to setup Linux on a Raspberry Pi single-board computer to run a Postgre development server for a local network. Note that the examples in this tutorial use “Raspbian Buster,” a version of Debian Linux that is essentially an ARM-based version of Ubuntu.
Prerequisites
A wireless router must be set up so the computer can communicate with the Postgre server over the local network.
Raspberry Pi must be connected to the local network, using a CAT 5e cable if necessary.
This tutorial assumes the Raspberry Pi computer is running a Debian-based distro of Linux that is able to install packages using the APT repository. Execute the following command in a terminal window to confirm the version of Linux running on the Raspberry Pi:
1 | cat /etc/*-release |
The system should return a response that starts with the Linux distro’s release name as shown here:
The hostname -I
command can also be used to get the Raspberry Pi’s local IP address. Note that this will be needed for networking and accessing the Postgre server on other machines connected to the network.
PostgreSQL on Raspberry Pi
Connect to the Raspberry Pi and install Postgre. Be certain to plug the Raspberry Pi device in after inserting the SD card containing the Linux installation. Note that an adapter that is able to supply enough voltage must be used to power the single-board device.
Enable SSH on the Raspberry Pi
Newer images of Raspbian require manually enabling SSH in the desktop before the ssh
commands can be executed.
Click on the Raspberry Pi icon on the left-hand side of the menu. Next, hover over the Preferences drop down menu with the cursor and click the Raspberry Pi Configuration button as shown in the following image:
A modal window should pop-up with the configuration settings. Make certain to navigate to the Interface tab and click the
Enable
SSH radio button before clicking the OK button.
SSH into the Raspberry Pi
If the Raspberry Pi and PC or Macbook are on the same router network, SSH into the Raspberry Pi using the single-board PC’s local IP address as shown here:
1 | ssh pi@192.168.100.36 |
NOTE: Make sure to input the same password that was used to setup the Raspberry Pi SD card. The Raspberry Pi’s IP address won’t exactly match the above example, however, it should start with 192.168
.
Install PostgreSQL on Raspbian Linux
Since Raspbian is a variety of Ubuntu, a Debian-based distro of Linux, the APT or APT-GET repository must be used to install Postgre.
Execute the following apt-get install
command to install Postgre on the Raspberry Pi:
1 | sudo apt install postgresql postgresql-contrib |
The results should resemble the following:
Postgres ‘buster-pgdg’ doesn’t support ARM architecture
Raspberry Pi’s use an ARM-based CPU architecture, as opposed to x86 architecture. Therefore an ARM-compatible version of Postgre must be installed.
If problems should arise trying to get Postgres to work on the current version of Raspbian, try an older version of postgresql
. Execute the following command to obtain v9.6 that supports the ARM architecture of single-board devices:
1 | sudo apt install postgresql-9.6 |
Now execute the pg_lsclusters
command to obtain the Postgres cluster status. It should return the location of the log file. If the cluster is down or having problems, use the following nano command to view the file:
1 | nano /var/log/postgresql/postgresql-11-main.log |
Access the Postgres user
Once Postgres is working, the following su postgres
command can be used to access the admin role for the Postgre server:
1 | sudo su postgres |
Configure PostgreSQL on the Raspberry Pi
Execute the following SHOW
SQL command to obtain the location of the pg_hba.conf
Postgres configuration file on the Raspberry Pi:
1 | SHOW hba_file; |
Copy the path for the sudo nano /etc/postgresql/11/main/pg_hba.conf
file and then type q
to exit the psql CLI. Now type exit
to leave the Postgres role.
Now locate the postgresql.conf
file using the following find
command:
1 | sudo find / -name "postgresql.conf" |
NOTE: Press CTRL+C to quit the operation if the command hangs up or doesn’t stop.
Now use those files to configure the server so it can be accessed on the local network.
Get the main computer’s local IP address and execute the following command to return the local IP address for Macintosh computers:
1 | ifconfig |grep inet |
Now use nano to edit the pg_hba.conf
file as shown here:
1 | sudo nano /etc/postgresql/11/main/pg_hba.conf |
Make sure to have host all all
enabled for the machines that Postgres will be given access to. The following example will use trust
and the 192.168.100.3
IP address:
1 | host all all 192.168.100.3/24 trust |
NOTE: This file must be modified and Postgres restarted every time a new IP address is assigned to the device by the local router. The 24
value is to cover a range of IP addresses on the network.
The following catch-all can be used so that all devices on the network will have access:
1 | host all all 0.0.0.0/0 md5 |
Configure the postgresql.conf file for the Raspberry Pi
The postgresql.conf
file must be edited so it can listen for addresses. This file will typically be located in /etc/postgresql/11/main
where 11
is the version number for Postgre. Execute the following command to edit the file:
1 | sudo nano postgresql.conf |
Now, as shown below, change the listen_addresses
option from localhost
to *
so that it accepts all IP address. For added security, hard code the Raspberry Pi and other device’s IP addresses. However, remember the file must be modified every time the IP address changes.
1 | listen_addresses = '*' |
Now restart the Raspberry Pi’s Postgre server using the following command:
1 | sudo service postgresql restart |
Create a Postgres database
Enter the following postgres
user command again to access the psql CLI and create a database:
1 | sudo su postgres |
Now type the following psql
command to access the interface and create a database:
1 | CREATE DATABASE rasp_pi_db; |
Use the l
command to list all databases. The results should resemble the following:
Type exit
to return back to the normal SSH interface for Postgre and then type exit
a second time to close the SSH connection.
Conclusion
This was part two of the Postgre and Raspberry Pi tutorial series explaining how to install and setup Postgre on a Raspberry Pi as a development server to use for Postgre projects. This second part of this series explained how to write a Raspbian Linux distro image to an SD card and run the OS on a Raspberry Pi single-board computer to host a local Postgres server. Part two of this series also covered how to set up, enable and configure Postgre on the Raspberry Pi and install Postgre on Raspbian Linux. Part two further explained how to configure the postgresql.conf file for the Raspberry Pi and create a Postgres database. Remember the postgresql.conf
file must be edited so it can listen for addresses and must be modified every time the IP address changes.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started