Setting up a PostgreSQL Database on a Raspberry Pi

In this project, we will show you how to set up a PostgreSQL Database on a Raspberry Pi.

PostgreSQL is a free and open-source relational database system that implements the SQL language.

If you are familiar with MySQL or MariaDB, you will be comfortable using PostgreSQL on your Raspberry Pi.

The reason for this is that all of these servers make use of SQL to manage their databases.

There are a few reasons why you may choose to use PostgreSQL over MySQL. One of those being PostgreSQL’s feature set and its compliance with implementing the SQL language.

The following sections will show you how to install PostgreSQL to your Raspberry Pi and create your very first database and table.

Equipment List

Below is a list of the equipment we used when installing PostgreSQL to the Raspberry Pi.

Optional

Raspberry Pi Case ( Amazon )
HDMI Cable ( Amazon )
Monitor ( Amazon )
USB Keyboard ( Amazon )
USB Mouse ( Amazon )

Setting up PostgreSQL to the Raspberry Pi

Installing PostgreSQL is a simple process thanks to it being available through the Raspberry Pi OS package repository.

The following steps will get you to install Postgres and configure the software so you can interact with the database using the pi user.

Installing the PostgreSQL Server

Our first step is to install the PostgreSQL server software to your Raspberry Pi.

This installation process is fairly straightforward and only takes two steps.

1. We need to start by updating our Raspberry Pi’s operating system.

Updating ensures we have a fresh base to install the PostgreSQL software to.

Run the following two commands on your Pi to update the package list and the installed packages.

sudo apt update
sudo apt upgrade

2. Our next step is to install the PostgreSQL package to our Raspberry Pi by using the command below.

Alongside the Postgres server, this command will also install additional bits of software such as the command-line interface.

sudo apt install postgresql

Configuring PostgreSQL’s CLI to Use Your User

At the moment, if you try to use the PostgreSQL CLI using your user, you will get an error saying you don’t have a role setup for it.

Don’t worry though, as we can change to the postgres user to create a new role.

1. Your first step is to change to the postgres user.

You can do this by running the following command on your Raspberry Pi.

sudo su postgres

2. Since we have changed users, PostgreSQL expects you to be in that user’s home directory.

You can change to your home directory using the cd command followed by the tilde symbol (~).

cd ~

3. Once you have changed to the postgres user, we can now create a new role.

Run the command below on your device to begin the process of creating a role for your user. Ensure that you replace “<USERNAME>” with the name of your user.

createuser <USERNAME> -P --interactive

During this process, you will be prompted to answer a few details.

4. The first prompt will ask you to enter a password for this new role.

Setting a password adds an extra layer of security when connecting to your Raspberry Pi’s PostgreSQL server.

Enter password for new role:
Enter it again:

5. You will now need to decide if you want this new user to be a superuser.

If you want to use your user to create databases and other stuff, answer Y to this question.

Shall the new role be a superuser? (y/n) y

6. Our next step is to create a database that has the same name as our user.

When you try to use the command-line interface, it will automatically try and connect to a database with the same name as your username.

Load up the command line interface by running the following command.

psql

7. Now, create a database with your username by typing in the following. Again, ensure that you replace “<USERNAME>” with the name of your user.

This database is mainly here to make it easier to start using the CLI tool easily.

CREATE DATABASE <USERNAME>;

8. You can quit out of the CLI tool by typing the following into PostgreSQL.

exit

9. With the role created for your user, we can now change back to it.

To get back to your user, all you need to do is enter the command below.

exit

10. Finally, you can swap back to your current user’s home directory by running the command below.

cd ~

Creating your first PostgreSQL Database on the Raspberry Pi

To test that PostgreSQL is now up and running on our Raspberry Pi, we will now create a new database on it.

These steps will show you how to use the Postgres CLI to create a database and add a table to it.

1. Let us start this process by loading into the Postgres CLI.

Run the following command to switch into the command line interface.

psql

2. Within the PostgreSQL command line, we can now create and manipulate databases.

As we mentioned earlier, Postgres uses the SQL programming language. If you have ever used SQL before, you will feel right out home.

For our first task, let us create a new database on our Raspberry Pi. For this example, we will call this database “exampledb“.

CREATE DATABASE exampledb;

3. Next, you will need to change to this new database.

By default, when you launch the Postgres CLI, you will interact with the database with the same name as your user.

To change to our new DB, we need to use the “\connect” command followed by our DB’s name.

\connect exampledb;

Once the CLI has connected to your new database, you should get a message as we have below.

You are now connected to database "exampledb" as user "pi".

4. Our next step to show PostgreSQL is running on the Raspberry Pi is to create a table within our database.

We are going to make this a simple table called “authors” that has two columns. One column will be called “name“, and the other will be called “website“.

We will set the data type for both of these columns to “text“.

CREATE TABLE authors (name text, website text);

4. Let us now add a row of data to this brand new table.

Having support for SQL makes adding this data a simple process. For our row, we will add an author with the name “Emmet” and the website “pimylifeup.com“

INSERT INTO authors VALUES ('Emmet', 'pimylifeup.com');

5. Finally, our last task is to get PostgreSQL to output the data stored in this table.

To do this, we can use the following simple select statement.

SELECT * FROM authors;

From this command, you should see all of the data that is stored within the specified table.

As we have only added a single row to this table, that is all you should see outputted into the command line.

 name  |    website
-------+----------------
 Emmet | pimylifeup.com
(1 row)

Conclusion

You should now have a good understanding of how you can install and set up a PostgreSQL database on your Raspberry Pi.

PostgreSQL is a solid alternative to MySQL and MariaDB. It has a plethora of features and has one of the most complete implementations of SQL.

If you have had any issues with getting the Postgres server running on your device, please leave a comment below.

Be sure to check out some of our other Raspberry Pi projects to see what else the device is capable of.

7 Comments

Francesco on July 17, 2024 at 12:39 am

Hi, I am following your guide but when I type:
“sudo su postgres
postgres@domusbb:/home/pi$ createuser pi -P –interactive”

I have this message:
“could not change directory to “/home/pi”: Permission denied
Enter password for new role:”

Is it important or not?
Can anyone help me to solve this issue?

I need to know it because later I will try to use postgresql with Django on my Raspberry 2 W
Thanks

1. Emmet on July 17, 2024 at 11:09 am
  
  Editor
  
  Hi Francesco,
  
  This tutorial made some assumptions that you would have a user called “pi” on your system (This is due to the old versions of Raspberry Pi OS having that is the default user).
  
  I have now updated the instructions so that it tells you to insert the name of your user.
  
  Please let me know if you continue to run into issues.
  
  Kind regards,
  Emmet
  
2. Francesco on July 20, 2024 at 8:33 pm
  
  Hi Emmet,
  thank you for your help.
  I am logged as “pi” on my Raspberry 2 W but this issue is still there.
  Francesco
  
  Sorry but I am not able to attach this message to your reply.
  
3. Emmet on July 20, 2024 at 9:54 pm
  
  Editor
  
  Hi Francesco,
  
  Quickly looked into this properly, and it seems like it is just caused by you being in your original user’s home directory despite changing to the “postgres” user.
  
  All we need to do to avoid this error is to swap to the “postgres” home directory by using the cd command followed by the tilde symbol (~).
  
  I have updated the tutorial to avoid this error.
  
  Please let me know if you continue to run into any issues with getting PostgreSQL working on your Raspberry Pi.
  
  Kind regards,
  Emmet
  
4. Francesco on August 1, 2024 at 8:02 pm
  
  Now it works!
  Thank you
  
Dave on May 18, 2023 at 3:32 pm

Very helpful. thank you for putting this together!

Vincent on September 20, 2021 at 9:43 am

Great guide, love it.

If anyone else is having an issue with the SQL statement, don’t forget the semicolon at the head like I did, spent a lot of time trying to realize when it was that all along.