Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to install Postgres database on a raspberry pie. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Install and configure the popular open source database Postgres on your next raspberry pie project and use it.

A database is a good way to save the growing data of your project or application. You can write data to the database in a session and find it the next time you need to find it. A well-designed database can efficiently find data in a huge dataset, just tell it what you are looking for, regardless of how it is found. Installing a database for a basic CRUD (create, record, update, delete) application is very simple, it is a very general schema, and it is also suitable for many projects.

Why is PostgreSQL generally referred to as Postgres? It is considered to be an open source database for function and performance. If you have used MySQL, they are very similar. However, if you want to use its more advanced features, you will find it easier to optimize Postgres. It is easy to install, easy to use, convenient and safe, and runs very well on raspberry pie 3.

This tutorial shows how to install Postgres; on a raspberry pie to create a table; write a simple query; use the pgAdmin graphical user interface on a raspberry pie, PC, or Mac; and interact with a database from Python.

After you have mastered these basics, you can have your application join multiple tables using composite queries, and what you need to consider at that time is how to use primary or foreign key optimization and * practices, and so on.

Installation

At first, you will need to install Postgres and some other packages. Open a terminal window and connect to the Internet, and 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 the database. So for the sake of simplicity in this tutorial, we will assume that you are using the default user pi. Run the createuser command to continue:

Createuser pi-P-interactive

When prompted, enter a password (and remember it), select n to make it a non-superuser, and choose y for the next two questions (LCTT: allow the creation of a database and other users, respectively).

Creating a postgres user

Now, use Postgres shell to connect to Postgres to create a test database:

$psql > create database test

Press Ctrl+D to exit from psql shell and postgres users twice and log in as pi users again. After you create a Postgres user named pi, you can access Postgres shell from here without login credentials:

$psql test

You are now connected to the "test" database. This database is currently empty and does not contain any tables. You can create a simple table in 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 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 views as familiar as spreadsheets. The psql command line tool is good for simple queries, and you'll find that many advanced users have been using it because it's fast (and because they don't need GUI), but pgAdmin is a more appropriate way for general users to learn and manipulate databases.

About other things pgAdmin can do: you can use it to connect to the database directly on the raspberry pie, or you can use it to connect to the database on the raspberry pie remotely on other computers.

If you want to visit raspberry pie, you can use apt to install it:

Sudo apt install pgadmin3

It is exactly the same as a Debian-based system such as Ubuntu; if you install it on another distribution, try the equivalent commands related to your system. Or, if you are on Windows or macOS, try downloading pgAdmin from pgAdmin.org. Note that the version available on apt is pgAdmin3, while the pgAdmin4 version of * * can be found on its website.

Connect to your database using pgAdmin on the same raspberry pie, simply open pgAdmin3 from the main menu, click the new connection icon, and then complete the registration. At this point, you will need a name (connection name, such as test), change the user to "pi", and leave the rest of the input boxes blank (or if they are not moving). Click OK and you will find a new connection in the side panel on the left.

Connect your database with pgadmin

To connect to your raspberry pie database using pgAdmin 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 to change 127.0.0.1 pg_hba 32 to 0.0.0.0 pg_hba 0 (for IPv4) and:: 1ax 128 to:: / 0 (for IPv6). Then save and exit.

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

Note that if you use an old Raspbian image or other distribution, the version number may be different.

Edit the postgresql configuration to allow remote connections

After you have done this, open pgAdmin on another computer and create a new connection. At this point, you need to provide a connection name and enter the IP address of the raspberry pie as the host (this can be found by hovering over the WiFi icon on the taskbar or typing 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 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 GUI to write your own queries. You may find this visualization 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 with 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 host IP, user name, 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 a specific query:

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

And a query that contains 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 a safe way to inject strings into the query is used here, and you don't want to be killed by little Bob's desk!

This is the end of the article on "how to install Postgres database on a raspberry pie". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.