Setting up Remote PostgreSQL on a Raspberry Pi

If you’re reading this you probably have a specific use case in mind. However, if you just stumbled upon this and are wondering why you would want to set up a database on a Raspberry Pi, I’d say “why the heck not!” This project will help give you a better understanding of:

1. Databases — (PostgreSQL specifically, but we all know data is everywhere and getting familiar with SQL and databases in general is definitely a good idea even if you don’t use them in your day to day role)
2. Linux — Linux is the OS which drives most enterprise servers as well as your smartphone and systems in autonomous cars. Again, just a good thing to get familiar with.
3. Networking — There isn’t too much networking in this project, but you will touch on things like CIDR notation and IP Reservations.

This write-up assumes that you have Raspian installed on a Raspberry Pi, access to your router’s web interface with an admin login and another computer on the same subnet.

Give your Raspberry Pi a static IP:

Chances are if you haven’t changed the configuration on your home router it is set to DHCP (Dynamic Host Control Protocol). This means your router has a pool of IP Addresses to hand out and when a new device connects to your router it assigns the device whatever IP Address is available. This is usually all well and good until we want to connect to another device on our network using it’s IP Address.

Say I have a printer set up on my computer with an IP Address of 192.168.10.12. Then one day it’s restarted and reconnects to the router and receives the IP Address 192.168.10.165. My computer is going to say that the printer is offline. Which it is kind of. It is not on the network where my PC is expecting it to be.

To begin give your Raspberry Pi a static IP address on your router. I’m using a Netgear C3000–100NAS, the configuration on other routers will be different but similar. You should be able to find details on how to do this with your router with a simple Google Search.

If you are unsure how to login to your router and you’re using Linux you can open a terminal and type:

route -n 

and look for the entry that begins with 0.0.0.0

Look for the IP Address that lists Default Gateway.

Open a browser and go to that IP Address. Most likely you will be prompted for the username and password. Again a quick Google Search will give you the default credentials of your router by make and model. And anyone else can look up this information as well so if you haven’t changed your router’s password, for the love of god and all that is holy CHANGE THE DEFAULT PASSWORD!

On my router after I log in I go to ADVANCED>> LAN Setup

At the bottom notice there is a section for Address Reservations. If I click the +Add button at the bottom I am taken to a list of devices connected to the router.

Here I’ve obfuscated some information, but the idea should be straight forward. I click the radial button next to my raspberrypi entry and then click +Add at the top. I can then see that this device will always be assigned the IP Address that is listed.

Setup Remote Access on your Raspberry Pi:

In order to connect to your Raspberry Pi remotely you will first need to make sure that connections are allowed on Port 22 (SSH). Begin by navigating to the Application Menu in the upper right hand corner it should be indicated by a Raspberry Image.

Select Preferences from the drop down menu and then select Raspberry Pi Configuration. From the Raspberry Pi Configuration menu select the Interfaces Tab at the top.

Click the radial button for SSH to allow SSH connections and click OK.

Setup PostgreSQL on your Raspberry Pi:

You can now SSH from your other PC into the Raspberry Pi. You can also do the next steps on your Raspberry Pi itself, you can just skip the SSH part.

From your machine open a terminal by pressing Ctrl+Shift+T. Type SSH pi@<ip address of your pi>. The default password is most likely raspberry, but if you haven’t changed it and it is not working check your version of Raspian and look it up.

Once you are connected type

sudo apt-get install postgresql postgresql-contrib

It will stop to ask you if you’re okay with using the additional space it will take up. Just answer “Y”.

Then type:

sudo su postgres

To swith to the postgres user and then type:

psql

To enter PostgreSQL. You should see something similar to the below.

Type

SHOW hba_file;

This is the file you will need to modify in order to be able to connect to the PostgreSQL Instance on the Raspberry Pi from another machine. Copy this path and you can type exit to exit Postgres.

Now from the terminal type:

sudo nano <path to your pg_hba.conf file>

In my case this would be:

sudo nano /etc/postgresql/11/main/pg_hba.conf

Use the arrow keys to move down the file until you start seeing uncommented (white) text. If you want to be able to connect from any machine on your home network add an entry under IPv4 local connections with your network address and the CIDR notation of your subnet mask. Most likely something like:

host     all     all     192.168.1.0/24     trust

Or if you know you will always be connecting from the same machine and you have an IP Reservation for that machine as well you can just give it that exact IP Address and a /32 at the end.

If you’re not familiar with nano those “^” next to the commands at the bottom are the Ctrl key. So Ctrl+x will exit and ask you if you want to save. Type “Y” and then press Enter to keep the name the same.

Now you will need to edit the postgresql.conf file. This is most likely in the same directory where you found the pg_hba.conf file. That is, if your hba.conf file was at /etc/postgresql/11/main/pg_hba.conf your postgresql.conf file should be there. Now execute:

sudo nano /etc/postgresql/11/main/postgresql.conf

If you see a red box when it opens saying it cannot be edited you forgot the sudo.

As you scroll down look for a section that says:

CONNECTIONS AND AUTHENTICATION

You will want to uncomment (delete the # sign) in front of the listen_adress and change the ‘localhost’ to ‘*’. This means if your Raspberry Pi gets a different address you can still connect to it. If you have your IP Reservation in place you can change this to be the IP of your Raspberry Pi.

Again just Ctrl+x then “Y” and Enter and you’re done. You will want to restart PostgreSQL though with:

sudo /etc/init.d/postgresql restart

Setting up pgAdmin III:

Personally I like a GUI when working with databases. The one I’ve been using lately is pgAdmin III. Setup on Linux is pretty straight forward. Just execute:

sudo apt-get install pgadmin3

You can then start pgAdmin III from the terminal by executing:

pgadmin3

Or by going to your menu and typing pgadmin and you should see it come up.

I’m using Linux Mint so your menu may look different.

Once it opens click on the connect button that looks like a plug in the upper right hand corner.

You can give the connection a Name, enter the IP Address of the Raspberry Pi and use the default 5432 for the port. You should not have to enter a password for the postgres user.

Click OK and you should be all set. You should see something similar to this.

You will need to expand out and get to at least the default posgres database in order for the SQL magnifying glass icon to be available. It is probably a good idea though to right click on Databases and create a new database you can experiment with.

Just click the SQL magnifying glass icon and the SQL Editor window will open up.

Giving credit where credit is due:

Resources:

a lot of what is written here is based off of this post by Data Pilot

How to install and setup Postgres on a Raspberry Pi (Part 2)

The postgreSQL documents are a great resource to help understand postgreSQL. I found this one particularly helpful:

PostgreSQL: Documentation: 9.1: The pg_hba.conf File

Also to understand listen_address:

PostgreSQL Documentation: listen_addresses parameter

And if you want to know why postgres user doesn’t need a password this stackoverflow post is good. I wanted to touch on it in this article, but thought that might be “going down the rabbit hole.”

https://stackoverflow.com/questions/39462209/why-doesnt-postgres-ask-for-password-for-user-postgres#:~:text=1%20Answer&text=Setting%20a%20password%20only%20provides,authentication%20methods%20that%20require%20it.&text=The%20peer%20auth%20mode%20does,re%20trying%20to%20connect%20as.

Happy learning everyone. This is my first time posting a tutorial so if you have any questions, concerns, constructive criticism, soul cutting criticism, want to tell my how I’m a horrible human being and my actions have stunted the progress of humanity as a whole or would like my recipe for totchos feel free to comment below.