How to Install PostgreSQL on Ubuntu 20.04

What is PostgreSQL?

PostgreSQL, also goes by the name “Postgres”, is an open-source, object-oriented relational Database Management System. It is used by many applications to store data. This database supports many cutting-edge features like reliable transactions and concurrency.

MacOS server uses PostgreSQL as its default database. It is also available for Linux, Windows, FreeBSD, and OpenBSD.

Features:

• Allows data to be stored in various formats.
• Data Integrity and Security
• Supports features like Indexing, Multi-Version Concurrency Control, sophisticated Query Planner, Table Partitioning, Just-in-Time Compilation of Expressions, and more.
• Supports various functionalities like Write-Ahead Logging, Replication, Point-in-Time Recovery, Active Standbys, Table Spaces and more.
• You can extend it to any application you use.

Guide:

In this guide, you will see how you can easily install PostgreSQL on Ubuntu 20.04 with the help of a few simple commands. We will also explore different commands to create new roles/users and databases.

Let’s do this!

PostgreSQL Installation

Step 1: Update the packages

Before initiating the PostgreSQL installation, let’s update the packages that are already installed on our system. Do this by running the command mentioned below:

sudo apt update

Step 2: Install PostgreSQL

Now, we will install the PostgreSQL package along with the contrib package which is needed to add some additional utilities and functionality. Use the following command to do that:

sudo apt install postgresql postgresql-contrib

PostgreSQL Roles and Databases

Now that we have successfully installed PostgreSQL, let’s get ourselves a little familiar with the PostgreSQL roles and databases.

Roles are meant to handle authentication and authorization of the PostgreSQL databases.

When we install PostgreSQL, by default a Postgres user is created. To switch to this user, run this command:

sudo -i -u postgres

In the next step, we will access the Postgres prompt. This prompt will let you communicate with the database management system.

To do that, run the following command:

psql

You can exit the prompt using the command below.

q

After running the previous command, you will be back in the postgres Linux command prompt. To go back to your regular system, run the exit command.

exit

One more way to enter the postgres prompt is by running the psql command directly with sudo as shown below:

sudo -u postgres psql

To quit the interactive Postgres session, you can run the command below as we previously did.

q

New Role Creation

Once you are logged in from your postgres account, you can make a new role by running the following command:

createuser --interactive

You can also switch directly from your normal account using sudo as shown below:

sudo -u postgres createuser --interactive

Both the methods will ask you to enter the name of the role. It will also ask you to confirm if the new role can be a superuser or not.

Database Creation

When you create a user in postgreSQL, its authentication system thinks that the role that you have used to log in, will have a database associated with it with the same name that it can access. Didn’t understand what I just said? Let me make it a little clear for you. This means, that the user “Tom” that we previously created, will try to connect to a database with the same name as the role.

You can create a database having the same name as the role, using the following command:

createdb Tom

Another way to make a database is by using sudo directly from your normal account by running this:

sudo -u postgres createdb Tom

Open Postgres Prompt with a new role

To log in with ident-based authentication, you need to have a Linux user with the same name as your Postgres role and database. If you don’t have one, don’t worry, you can create it using the adduser command. You will have to do this with your non-root account using sudo privileges.

sudo adduser Tom

After that, you can connect to the database by running these two commands:

sudo -i -u Tom

psql

Or instead, you can use a simple one-line command:

sudo -u Tom psql

Both ways, you will get access.

To interact with a different database, run the following command by mentioning the database as shown below:

psql -d postgres

To check the status of your current connection, run the conninfo command. It will provide you information of your database connection.

conninfo

This is what the output will look like:

That is all, folks.

In this document, you saw how to install PostgreSQL on your Ubuntu 20.04 LTS machine. With that, you also got to see how you can enter the postgres prompt. We also explored how to create new roles and databases with the help of a few simple commands.

You can also see how to install PostgreSQL on

Linux Mint https://linuxways.net/mint/how-to-install-postgresql-on-linux-mint-20/

Karim Buzdar

Karim Buzdar holds a degree in telecommunication engineering and holds several sysadmin certifications including CCNA RS, SCP, and ACE. As an IT engineer and technical author, he writes for various websites.