Introduction

This guide will walk you through the installation and configuration of a PostgreSQL database server on Ubuntu 22.04.

What is PostgreSQL?

PostgreSQL is a free, open-source, and most advanced object-relational database system. It is a highly scalable, robust, and reliable database server that can handle a large number of concurrent users without any performance issues.

Prerequisites

• A server running Ubuntu 22.04.
• A non-root user with sudo privileges.

Installation

Before starting, it is recommended to update your system packages to the latest version. You can update them with the following command:

sudo apt update

Once your system is up-to-date, you can proceed to install PostgreSQL on your system.

Step 1 - Install PostgreSQL Server

By default, PostgreSQL is available in the Ubuntu 22.04 default repository. You can install it with the following command:

sudo apt install postgresql postgresql-contrib

Once the installation is completed, you can verify the PostgreSQL version with the following command:

psql --version

Step 2 - Create a Database

By default, PostgreSQL creates a new user called postgres with postgres as a password. You can log in to the PostgreSQL shell with the following command:

sudo -u postgres psql

Enter your root password when prompted, then create a new database with the following command:

CREATE DATABASE mydb;

Step 3 - Create a Database User

Next, you will need to create a database user that will be used to access the database. You can create it with the following command:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'password';

Next, grant all privileges to the myuser with the following command:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Next, exit from the PostgreSQL shell with the following command:

\q

Step 4 - Enable Remote Access

By default, PostgreSQL only allows connections from the localhost. If you want to access the PostgreSQL server remotely, you will need to configure it to listen on all interfaces.

You can do it by editing the postgresql.conf file:

sudo nano /etc/postgresql/13/main/postgresql.conf

Change the following line:

listen_addresses = 'localhost'

To:

listen_addresses = '*'

Save and close the file, then restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

Step 5 - Verify PostgreSQL Database Server

At this point, PostgreSQL has been installed and configured on your Ubuntu 22.04 server. It's time to verify the PostgreSQL database server.

You can do it with the following command:

psql -h localhost -U myuser -d mydb

Enter your password when prompted, then you should see the PostgreSQL shell in the following output:

psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
Type "help" for help.
 
mydb=>

You can show all databases with the following command:

\l

You should see the following output:

mydb=# \l
                                  List of databases
   Name   |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+-----------------------
mydb      | myuser   | UTF8     | en_US.utf8 | en_US.utf8 |
postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
          |          |          |            |            | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
          |          |          |            |            | postgres=CTc/postgres
(4 rows)

Conclusion

Congratulations! you have successfully installed and configured PostgreSQL on Ubuntu 22.04. You can now easily deploy your own PostgreSQL database server on Ubuntu 22.04.

Resources

• PostgreSQL Documentation