How to Install Postgres Database on a Raspberry Pi

Install and configure the popular open-source database Postgres on your next Raspberry Pi project and use it.

Saving the data that your project or application continuously generates is a great way to do it. You can write data to the database in one session and find it the next time you need to look it up. A well-designed database can efficiently find data within large datasets, as long as you tell it what you want to find, without having to consider how it searches. Installing a database for a basic CRUD (Create, Read, Update, Delete) application is very simple; it is a very general pattern and applies to many projects.

Why is PostgreSQL generally referred to as Postgres? It is considered to be the best open-source database in terms of features and performance. If you’ve used MySQL, they are quite similar. However, if you want to use its more advanced features, you’ll find optimizing Postgres relatively easy. It is easy to install, user-friendly, secure, and runs very well on the Raspberry Pi 3.

This tutorial introduces how to install Postgres on a Raspberry Pi; create a table; write simple queries; use pgAdmin GUI on Raspberry Pi, PC, or Mac; and interact with the database from Python.

Once you master these basics, you can make your application use composite queries to join multiple tables, at which point you need to consider how to optimize using primary keys or foreign keys and best practices, etc.

Installation

To start, you will need to install Postgres and some other packages. Open a terminal window and connect to the internet, then run the following command:

sudo apt install postgresql libpq-dev postgresql-client postgresql-client-common -y

installing postgres

When the installation is complete, switch to the Postgres user to configure the database:

sudo su postgres

Now, you can create a database user. If you create a user with the same name as your Unix user account, that user will be automatically authorized to access that database. Therefore, for simplicity in this tutorial, we will assume you used the default user pi. Run the createuser command to continue:

createuser pi -P --interactive

When prompted, enter a password (and remember it), choose n to make it a non-superuser, and for the next two questions choose y (this allows creating databases and other users).

creating a postgres user

Now, connect to Postgres using the Postgres shell to create a test database:

$ psql > create database test;

Press Ctrl+D twice to exit from the psql shell and postgres user, and log in again as the pi user. Once you have created a Postgres user named pi, you can access the Postgres shell from here without credentials:

$ psql test

You are now connected to the “test” database. This database is currently empty and contains no tables. You can create a simple table in the psql shell:

test=> create table people (name text, company text);

Now you can insert data into the table:

test=> insert into people values ('Ben Nuttall', 'Raspberry Pi Foundation'); test=> insert into people values ('Rikki Endsley', 'Red Hat');

Then try to perform a query:

test=> select * from people; name | company ---------------+------------------------- Ben Nuttall | Raspberry Pi Foundation Rikki Endsley | Red Hat (2 rows)

a postgres query

test=> select name from people where company = 'Red Hat'; name | company ---------------+--------- Rikki Endsley | Red Hat (1 row)

pgAdmin

If you want to use a graphical tool to access the database, you can use it. PgAdmin is a full-featured PostgreSQL GUI that allows you to create and manage databases and users, create and modify tables, execute queries, and browse results in a view familiar to spreadsheet users. The psql command-line tool can handle simple queries well, and you will find that many advanced users continue to use it because of its speed (and because they do not need to rely on a GUI), but for general users, learning and operating databases using pgAdmin is a more suitable way.

Other things pgAdmin can do: you can connect directly to the database on the Raspberry Pi or connect remotely to the database on the Raspberry Pi from other computers.

If you want to access the Raspberry Pi, you can install it using apt:

sudo apt install pgadmin3

It is exactly the same for Debian-based systems like Ubuntu; if you are installing on other distributions, try the equivalent command relevant to your system. Alternatively, if you are on Windows or macOS, try downloading pgAdmin from pgAdmin.org.

Note that the version available on apt is pgAdmin3, while the latest version pgAdmin4 can be found on its website.

To connect to your database using pgAdmin on the same Raspberry Pi, simply open pgAdmin3 from the main menu, click the new connection icon, and complete the registration. At this point, you will need a name (connection name, such as test), change the user to “pi”, and leave the remaining input boxes empty (or as they originally were). Click OK, and you will find a new connection in the left sidebar.

connect your database with pgadmin

To use pgAdmin to connect to your Raspberry Pi database from another computer, you first need to edit the PostgreSQL configuration to allow remote connections:

1. Edit the PostgreSQL configuration file /etc/postgresql/9.6/main/postgresql.conf, uncomment the listen_addresses line, and change its value from localhost to *. Then save and exit.

2. Edit the pg_hba configuration file /etc/postgresql/9.6/main/postgresql.conf, change 127.0.0.1/32 to 0.0.0.0/0 (for IPv4) and change ::1/128 to ::/0 (for IPv6). Then save and exit.

3. Restart the PostgreSQL service: sudo service postgresql restart.

Note that if you are using an old Raspbian image or another distribution, the version number may vary.

edit the postgresql configuration to allow remote connections

After doing this, open pgAdmin on another computer and create a new connection. At this point, you need to provide a connection name, enter the Raspberry Pi’s IP address as the host (this can be found by hovering the mouse over the WiFi icon on the taskbar, or by entering hostname -I in a terminal).

a remote connection

Whether you are connecting to a local or remote database, click to open Server Groups > Servers > test > Schemas > public > Tables, right-click on the people table, and select View Data > View top 100 Rows. You will now see the data you entered earlier.

viewing test data

You can now create and modify databases and tables, manage users, and use the GUI to write your own queries. You may find this visual approach easier to manage than the command line.

Python

To connect to your database from a Python script, you will need the Psycopg2 Python package. You can install it using pip:

sudo pip3 install psycopg2

Now open a Python editor and write some code to connect to your database:

import psycopg2 conn = psycopg2.connect('dbname=test') cur = conn.cursor() cur.execute('select * from people') results = cur.fetchall() for result in results: print(result)

Run this code to see the query results. Note that if you are connecting to a remote database, you will need to provide more credentials in the connection string, such as adding the host IP, username, and database password:

conn = psycopg2.connect('host=192.168.86.31 user=pi password=raspberry dbname=test')

You can even create a function to run specific queries:

def get_all_people(): query = """ SELECT * FROM people """ cur.execute(query) return cur.fetchall()

And a query with parameters:

def get_people_by_company(company): query = """ SELECT * FROM people WHERE company = %s """ values = (company, ) cur.execute(query, values) return cur.fetchall()

Or even a function to add records:

def add_person(name, company): query = """ INSERT INTO people VALUES (%s, %s) """ values = (name, company) cur.execute(query, values)

Note that here a safe method is used to inject strings into the query; you don’t want to be Bob’s table!

Python

Now that you know these basics, if you want to further master Postgres, check out the article on Full Stack Python.

(Cover image: Raspberry Pi Foundation)

Author Bio:

Ben Nuttall – Manager of the Raspberry Pi community. In addition to his work for the Raspberry Pi Foundation, he is also engaged in open-source software, mathematics, kayaking, GitHub, exploration, and Futurama. Follow him on Twitter @ben_nuttall.

via: https://opensource.com/article/17/10/set-postgres-database-your-raspberry-pi

Author: Ben Nuttall Translator: qhwdw Proofreader: wxy

This article is originally compiled by LCTT, honorably launched by Linux China