How to Install PostgreSQL on Ubuntu 22.04

Introduction

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

PostgreSQL is a powerful and popular open-source relational database management system (RDBMS). It provides a robust platform for storing and managing structured data, making it ideal for businesses and developers. With its advanced features like transactions, indexing, and query optimization, PostgreSQL ensures efficient data handling and retrieval.

Its extensibility allows integration with various programming languages and frameworks. PostgreSQL's scalability and reliability make it a preferred choice for applications requiring secure and scalable data storage.

In this tutorial, you will install PostgreSQL on Ubuntu 22.04. We will also address some of the FAQs on how to install PostgreSQL on Ubuntu 22.04.

Advantages of PostgreSQL

1. Open-source: PostgreSQL is freely available, allowing users to access and modify the source code according to their needs.
2. Robustness: Its advanced features like ACID compliance, data integrity, and crash recovery ensure reliable and secure data management.
3. Extensibility: PostgreSQL supports a wide range of data types, extensions, and customizations, enabling seamless integration with different tools and frameworks.
4. Performance: With its advanced indexing techniques and query optimization, PostgreSQL delivers excellent performance even with large and complex datasets.
5. Scalability: PostgreSQL handles high volumes of data and concurrent connections efficiently, making it suitable for scaling applications as they grow.

Prerequisites to Install PostgreSQL on Ubuntu 22.04

• Ubuntu 22.04 server
• Non-root user with sudo privileges
• 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) To exit 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 - Create 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 - Create 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 - Open 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 - Enable 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/14/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 22.04

Conclusion

We hope this detailed tutorial helped you to install PostgreSQL on Ubuntu 22.04.

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