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 "Postgres".
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-contribStep 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 postgresTo access the PostgreSQL command line, use the command:
psqlYou can combine these two commands and run the PostgreSQL command line immediately.
sudo -u postgres psqlYou 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 --interactiveEnter 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 existLet's fix this and create a new database with the name of the new role.
createdb usernameStep 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 usernameThen, 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
psqlUse this command to connect to another database. Here, postgres is the name of the target database.
psql -d postgresTo 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".
700
300
700
300
700
300
700
300
