Install PostgreSQL on Ubuntu 20.04 - Step by step guide ?

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.

Here at LinuxAPT, we shall look into how you can easily install PostgreSQL on Ubuntu 20.04 with the help of a few simple commands. 

You can also see how to install PostgreSQL on Linux Mint.

Main features of PostgreSQL includes:

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

Steps to install PostgreSQL on Ubuntu Linux system

1. Perform System Update

To begin, ensure that the packages of the system is up to date with the below command:

$ sudo apt update

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

3. Configure 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

Next, 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 below command:

\q

You can go 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:

$ sudo -u postgres psql

To quit the interactive Postgres session, you can run the command:

\q

4. Create New Role

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:

$ sudo -u postgres createuser --interactive

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

5. Create Database for PostgreSQL

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

An alternative method to make a database is by using sudo directly from your normal account by running this:

$ sudo -u postgres createdb John

6. 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 John

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

$ sudo -i -u John
$ psql

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

$ sudo -u John psql

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

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

[Need help in fixing PostgreSQL issues in Ubuntu Linux system ? We can help you. ]