How to Install PostgreSQL on Ubuntu 20.04

Introduction

Before we begin talking about how to install PostgreSQL on Ubuntu 20.04, let’s briefly understand - What is PostgreSQL?

PostgreSQL is a relational database management system. It provides an implementation of the SQL querying language. Moreover, it is a popular choice for many small and large projects. It gives the advantage of being standards-compliant. It even has many advanced features like reliable transactions and concurrency without the read locks.

In this tutorial, you will install PostgreSQL on Ubuntu 20.04. We will also address some of the FAQs related to the PostgreSQL installation.

Advantages of PostgreSQL

1. Advanced features: PostgreSQL offers a wide range of advanced features like native JSON support, full-text search, and geospatial capabilities.
2. High performance: It delivers excellent performance for complex queries and concurrent workloads, thanks to its efficient query optimizer and multi-version concurrency control (MVCC).
3. Robustness and reliability: With its ACID-compliant architecture, PostgreSQL ensures data consistency, durability, and fault tolerance, making it a reliable choice for critical applications.
4. Extensibility: It provides a flexible architecture that allows users to define custom data types, functions, and extensions, enabling seamless integration with other tools and libraries.
5. Community and support: PostgreSQL has an active and vibrant community that provides continuous support, regular updates, and a large number of available libraries and plugins.

Prerequisites

1) Ubuntu 20.04 server

2) Non-root user with sudo privileges

3) Basic firewall

Step 1 - Install PostgreSQL on Ubuntu

1) Ubuntu’s default repositories have Postgres packages. Therefore, you will install these by apt package manager system.

2) Since it will be your first time using apt, it will be good to refresh your local package index. After that, install the Postgres package along with -contrib package. It will add a few additional utilities and functionality.

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Step 2 - Using the PostgreSQL Roles and Databases

1) In Postgres, there is a concept known as roles which are used to handle authentication and authorization. It doesn't distinguish between users and groups instead it prefers the more flexible term "role".

Postgres uses ident authentication which means it associates Postgres roles with a matching Unix-style account. Unix/Linux username can simply sign in with the same name if a role exists with Postgres.

The installation procedure will create a user account known as Postgres. It is associated with the default Postgres role. To use Postgres, you can log into that account.

Next, we will discuss a few ways to utilize this account to access Postgres.

Switch to the Postgres Account

1) To switch over to postgres account on your server, type:

sudo -i -u postgres

2) Now access a Postgres prompt immediately by typing:

psql

Now you are logged into PostgreSQL and you can freely interact with DBMS right away.

3) T0 exit out of the PostgreSQL, type the following command:

postgres=# \q

It will bring you back to postgres Linux command prompt.

Access Postgres Prompt without Switching Accounts

1) Instead of switching the account and then interacting with Postgres, you can simply open the Postgres prompt with a single command:

sudo -u postgres psql

2) This way you will be able to log in directly into Postgres without bash shell in between.

5) To exit the interactive Postgres session, type the following command:

postgres=# \q

Step 3 - Creating a New Role

1) At present, you just have postgres role configuration within the database. To, create new roles use the createrole command. You can set the name of the new role and configure if it can have superuser permissions with the --interactive flag.

2) So, if you are logged in as postgres account, create a new user by typing:

createuser --interactive

3) If, you prefer to use sudo for each command without switching from your normal account, then type:

sudo -u postgres createuser --interactive

4) Next, the script will prompt you with a few choices and based on your responses, execute the correct Postgres commands. It will create a user as per your specifications.

Firstly, you will be asked to specify a name for the new role. You can keep any name of your choice, here we are keeping it as John.

Output

Enter name of role to add: john

Next, you need to decide on the role permission. If you ill grant superuser permission then it can bypass almost all the permission checks.

If you would like John to have superuser permission then follow the below example:

Output

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

You can create new superuser roles only if you are creating them as a role that is already a superuser.

5) To gain more control pass some additional flags. You can check the variety of options with the below command:

man createuser

Step 4 - Creating a New Database

1) Postgres authentication system makes an assumption that the name of the database will be similar to the role you have used to log in.

For example, if you are logged in with user john then it will automatically try to search and connect with the database named john.

2) If you are logged in as postgres account, type:

createdb john

3) If, you want to use sudo for each command without switching from your normal account, then type:

sudo -u postgres createdb john

Step 5 - Opening Postgres Prompt with New Role

1) You will need a Linux user with the same name as your Postgres role and database to log in with ident based authentication.

2) Create a new user with the adduser command if you don't have a matching Linux user. Do it with your non-root account with sudo privileges. It means you should not be logged in as a postgres user.

sudo adduser john

3) Switch over and connect to the database with the following command:

sudo -i -u john
psql

Or, you can also do this inline, by:

sudo -u john psql

You will be logged in automatically with the assumption that all the components are properly configured.

4) Also, if you want your user to connect to a different database, then specify the database like:

psql -d postgres

5) Once you are logged in then check the current connection information by typing:

\conninfo

Output

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

It is helpful if you are connecting to a non-default database or with a non-default user.

Step 6 - Enabling Remote Access to PostgreSQL

1) PostgreSQL server listens only on the local interface 127.0.0.1. Open the configuration file postgresql.conf and add listen_addresses = '*' in CONNECTIONS AND AUTHENTICATION section to enable remote access to your PostgreSQL server.

sudo vim /etc/postgresql/12/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

2) After that, save the file and restart the PostgreSQL service using the following command:

sudo service postgresql restart

3) Now, use the ss utility to verify the changes:

ss -nlt | grep 5432

Output

LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:*
LISTEN   0         128                    [::]:5432                [::]:*

It is clearly visible from the above output that the PostgreSQL server is listening on all interfaces.

Now, edit the pg_hba.conf file so that the server can accept remote connections.

FAQs to Install PostgreSQL on Ubuntu 20.04

Conclusion

We hope this detailed guide helped you to install PostgreSQL on Ubuntu 20.04.

If you have any queries or doubts, please leave them in the comment below. We'll be happy to address them.