How To Install PostgreSQL on Ubuntu 20.04

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Install PostgreSQL on Ubuntu

Install PostgreSQL on Ubuntu by using following command.

sudo apt update

sudo apt install postgresql postgresql-contrib

Output:

root@vps:~# apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libllvm10 libpq5 libsensors-config libsensors5 postgresql-12
postgresql-client-12 postgresql-client-common postgresql-common ssl-cert
sysstat
Suggested packages:
lm-sensors postgresql-doc postgresql-doc-12 libjson-perl openssl-blacklist
 isag
The following NEW packages will be installed:
libllvm10 libpq5 libsensors-config libsensors5 postgresql postgresql-12

PostgreSQL Roles and Databases

We'll switch to postgres account for this.

sudo -i -u postgres

you can access a PostgreSQL prompt using the psql utility.

psql

Ouput:

postgres@vps:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

To exit out of the PostgreSQL shell type.

\q

To Create PostgreSQL Role

sudo -u postgres createuser --interactive

Output:

Enter name of role to add: john
Shall the new role be a superuser? (y/n) y

To create PostgreSQL database

sudo -u postgres createdb john

To open a Postgres Prompt with the New Role.

sudo adduser john

To switch over and connect to the database.

sudo -u john psql

Output:

root@vps:~# sudo -u john psql
could not change directory to "/root": Permission denied
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

john=# 

Once you logged in as john and check your current connection information.

\conninfo

Output:

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