Installing Postgresql on Raspberry Pi

PostgreSQL is a popular database option used teams and companies of all sizes. From the smallest of hobbyist projects, to the largest of Enterprise online web applications.

In this tutorial, I’m going to explain how to install the latest Postgresql on the Raspberry Pi. I’m going to assume that you have Raspbian installed on your Raspberry Pi.

Installing the Essentials

In an open Terminal session window, type the following command to install PostgreSQL:

1
2
sudo apt update
sudo apt install postgresql

Wait until the installation process is finished. Part of the installation process will involve the setup of PostgreSQL as a system service. Once the installation has finished, you can verify it’s running via:

1
sudo systemctl status postgresql

You should see something like this:

1
2
3
4
5
6
7
8
9
10
11
pi@raspberrypi:~ $ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
Active: active (exited) since Sat 2019-09-21 15:53:47 MDT; 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

Sep 03 15:53:47 raspberrypi systemd[1]: Starting PostgreSQL RDBMS...
Sep 03 15:53:47 raspberrypi systemd[1]: Started PostgreSQL RDBMS.

It is really that simple; PostgreSQL has been installed!

Connect to the PostgreSQL console

In an open Terminal session window, use this command to connect to PostgreSQL the running console.

1
sudo -i -u postgres

Still connected to the console, you can run psql to help verify what version you’re working with:

1
2
3
4
5
postgres@raspberrypi:~$ psql
psql (11.5 (Raspbian 11.5-1+deb10u1))
Type "help" for help.

postgres=#

You can display a list of all the current databases.

1
\l

Since we haven’t added one, you should see a list of 3 databases.

To quit the console, just type \q.

Enable Networking for PostgreSQL

In most cases, your PostgreSQL database will be accessed through a web application server running on the same machine - such as through PHP, Node, Ruby on Rails, etc. In which case, you don’t have to do any further PostgreSQL configuration.

To allow your PostgreSQL databases to be accessed from the network via TCP/IP, you’ll need to make a simple change to a configuration file. Open the following file with nano:

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

Find the following line:

1
listen_addresses = 'localhost'

Update it to:

1
listen_addresses='*'

Close and save the file with ctrl+x. Finally, restart the running PostgreSQL service.

1
sudo systemctl restart postgresql

Done.

Conclusion

Hopefully this was a simple walkthrough to show you how to setup the latest version of the PostgreSQL database on your Raspberry Pi.