07.06.2023

How to Install PostgreSQL on Ubuntu 20.04

PostgreSQL is a dynamically developing relational database management system. In this tutorial, we will walk through the process of installing it and the main operations with it.

In the Serverspace you can create a server with already installed app "PostgreSQL".

Step 1 – Installing PostgreSQL packages

The postgresql package is the main one. You also need to install a package with additional functionality and utilities postgresql-contrib.

sudo apt install postgresql postgresql-contrib

Step 2 - Authorization and roles in PostgreSQL

The concept of roles in the PostgreSQL DBMS is used for authentication and authorization. Without going into details, roles are very similar to users and groups in Linux.

After adding a role to the DBMS corresponding to the Linux username, this user will be able to log in to the PostgreSQL system as this role. During installation, the postgres user was add to the system. Let’s log in using it.

sudo -i -u postgres

To access the PostgreSQL command line, use the command:

psql

You can combine these two commands and run the PostgreSQL command line immediately.

sudo -u postgres psql

You can use this command line to manage the DBMS.

Step 3 - Creating a new role

After logging in with your postgres account, you can create a new role:

createuser --interactive

Enter the user name and specify whether the user needs to be a super user.

Step 4 – Creating a new database

The DBMS assumes that there is a database with the same name for each role. And after authorization, PostgreSQL will try to connect to it. Therefore, you will not be able to log in to the DBMS command line immediately after creating the user.

psql: error: could not connect to server: FATAL: database "username" does not exist

Let's fix this and create a new database with the name of the new role.

createdb username

Step 5 - Authorization in the PostgreSQL command line under the new role

To access the PostgreSQL command line, you need a Linux account with the same name as the role and database name. If there is no such user, you can create one using the command:

sudo adduser username

Then, in interactive mode, you need to fill in the username, password, and several additional fields.
Log in with your new account and access the PostgreSQL command line

sudo -i -u username
psql

Use this command to connect to another database. Here, postgres is the name of the target database.

psql -d postgres

To display information about an established connection, use the command:

\conninfo
You are connected to database "username" as user "username" via socket in "/var/run/postgresql" at port "5432".