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:

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:

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:

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:

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:

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 find command:

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:

Now use nano to edit the pg_hba.conf file as shown here:

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:

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:

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:

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.

Now restart the Raspberry Pi’s Postgre server using the following command:

Create a Postgres database

Enter the following postgres user command again to access the psql CLI and create a database:

Now type the following psql command to access the interface and create a database:

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.