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".