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.
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.
To access the PostgreSQL command line, use the command:
You can combine these two commands and run the PostgreSQL command line immediately.
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:
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.
Let's fix this and create a new database with the name of the new role.
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:
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
psql
Use this command to connect to another database. Here, postgres is the name of the target database.
To display information about an established connection, use the command:
You are connected to database "username" as user "username" via socket in "/var/run/postgresql" at port "5432".