How to Install PostgreSQL on Ubuntu 18.04

Introduction

Before we begin talking about how to install PostgreSQL on Ubuntu 18.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 18.04. We will also address some of the FAQs related to the PostgreSQL installation.

Advantages

1. Robust and Reliable: PostgreSQL is known for its stability and reliability. It offers ACID (Atomicity, Consistency, Isolation, Durability) compliance, ensuring data integrity even in the face of hardware or software failures.
2. Scalability: PostgreSQL is designed to handle high levels of concurrent transactions and can scale with your growing data needs. It supports parallel execution, partitioning, and replication, enabling you to handle increasing workloads.
3. Advanced Features: PostgreSQL offers a wide range of advanced features, including complex queries, full-text search, JSON and array support, geospatial capabilities, and support for stored procedures and custom extensions.
4. Extensibility: PostgreSQL allows you to create custom data types, operators, and functions to cater to specific requirements. It also supports a wide range of extensions that enhance its functionality and integration with other systems.
5. Community and Support: PostgreSQL benefits from a large and active open-source community. This community ensures regular updates, security patches, and provides resources for documentation, forums, and mailing lists to assist with any issues you may encounter.

Prerequisites

1) Ubuntu 18.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

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 signin 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 the 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) Iinstead 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 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 same name as your Postgres role and database to login 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 priviliges. 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 an 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 non-default database or with 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 the remote access to your PostgreSQL server.

sudo vim /etc/postgresql/10/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 in 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 18.04

Conclusion

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

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