How to install and setup Postgres on a Raspberry Pi (Part 2)
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.
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:
The system should return a response that starts with the Linux distro’s release name as shown here:
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:
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
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:
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:
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:
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:
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:
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
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:
ifconfig |grep inet
Now use nano to edit the
pg_hba.conf file as shown here:
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:
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:
host all all 0.0.0.0/0 md5
Configure the postgresql.conf file for the Raspberry Pi
postgresql.conf file must be edited so it can listen for addresses. This file will typically be located in
11 is the version number for Postgre. Execute the following command to edit the file:
sudo nano postgresql.conf
Now, as shown below, change the
listen_addresses option from
* 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.
listen_addresses = '*'
Now restart the Raspberry Pi’s Postgre server using the following command:
sudo service postgresql restart
Create a Postgres database
Enter the following
postgres user command again to access the psql CLI and create a database:
sudo su postgres
Now type the following
psql command to access the interface and create a database:
CREATE DATABASE rasp_pi_db;
l command to list all databases. The results should resemble the following:
exit to return back to the normal SSH interface for Postgre and then type
exit a second time to close the SSH connection.
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