If you own Raspberry Pi device, you can turn your Pi into a powerful PostgreSQL Server in few minutes. This tutorial is going to show you how to install PostgreSQL on Raspberry Pi. My Raspberry Pi is running Raspbian (based on Debian 9 Stretch). 

What you will learn

• Install PostgreSQL on Raspberry Pi (Raspbian)
• Configure remote access for PostgreSQL

Steps to install PostgreSQL on Raspberry Pi

Open Terminal and type the following command to install PostgreSQL 

sudo apt update
sudo apt install postgresql

Wait until the installation process is finished. Next, make sure PostgreSQL service is up and running

sudo systemctl status postgresql

You should see something like this:

root@raspberrypi:/etc/postgresql/9.6/main# sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Thu 2018-07-19 14:03:43 UTC; 38min ago
Process: 4031 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 4031 (code=exited, status=0/SUCCESS)
CPU: 0
CGroup: /system.slice/postgresql.service

Jul 19 14:03:43 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Jul 19 14:03:43 raspberrypi systemd[1]: Started PostgreSQL RDBMS.
lines 1-10/10 (END)

At this point, PostgreSQL has been installed.

Connect to PostgreSQL console

In Terminal, use this command to connect to PostgreSQL console.

sudo -i -u postgres
psql

Output:

root@raspberrypi:~# sudo -i -u postgres
postgres@raspberrypi:~$ psql

psql (9.6.9)
Type "help" for help.

postgres=#

Let’s try to list all the database

\l

Output:

Configure Remote Access for PostgreSQL

By default, PostgreSQL can only be accessed from localhost. To enable remote access, we need to configure some settings. 

Enable Client Authentication

Login as postgres user

su - postgres

Edit the pg_hba.conf

nano /etc/postgresql/9.6/main/pg_hba.conf 

Add the following line to the configuration file:

host all all 192.168.100.1/24 trust

Make sure you change the IP address with your own.

Enable Networking for PostgreSQL

To allow PostgreSQL to be accessed from the network via TCP/IP, do the following

nano /etc/postgresql/9.6/main/postgresql.conf 

Find the following line

listen_addresses='localhost'

Change it to

listen_addresses='*'

Close and save the file. Finally, restart PostgreSQL service

sudo systemctl restart postgresql

Done.

Closing word

We have shown you how to install PostgreSQL and configure remote access. Now it’s your turn to practice. Please leave us comments if you found any difficulties.