Introduction

PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its stability, performance, and data integrity. It offers a robust SQL implementation suitable for mission-critical applications, including features like nested transactions, multiversion concurrency control (MVCC), table inheritance, asynchronous replication, and referential integrity of foreign keys.

Prerequisites

Before you begin, ensure the following requirements are met:

• A server running Ubuntu 22.04.
• Root or sudo privileges on the server.

Add PostgreSQL Repository

The latest PostgreSQL versions may not be available in the default Ubuntu 22.04 repositories. Adding the official PostgreSQL repository ensures you have access to the newest versions.

First, install the necessary dependencies:

apt-get install gnupg2 curl wget -y

Next, add the PostgreSQL repository and GPG key:

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Update the package list from the repositories:

apt update -y

Install PostgreSQL 14

With the repository added, you can now install PostgreSQL 14:

apt install postgresql-14 -y

Check the status of the PostgreSQL service:

systemctl status postgresql

The output should indicate that the service is active and running, similar to:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2022-05-28 10:29:12 UTC; 11s ago
    Process: 3107 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 3107 (code=exited, status=0/SUCCESS)
        CPU: 1ms
    May 28 10:29:12 ubuntu2204 systemd[1]: Starting PostgreSQL RDBMS...
    May 28 10:29:12 ubuntu2204 systemd[1]: Finished PostgreSQL RDBMS.

Confirm the installed PostgreSQL version:

sudo -u postgres psql -c "SELECT version();"

You should see an output like this:

                                                 version                                                 
----------------------------------------------------------------------------------------------------------
 PostgreSQL 14.3 (Ubuntu 14.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

Configure PostgreSQL

By default, PostgreSQL is configured to only accept local connections. To enable remote connections, you need to modify the configuration files.

Edit the pg_hba.conf file to configure client authentication:

nano /etc/postgresql/14/main/pg_hba.conf

Find and modify the following lines to allow connections from any IP address (use with caution in production environments):

local   all             all                                     trust
host    all             all             0.0.0.0/0                md5

Edit the main PostgreSQL configuration file (postgresql.conf) to change the listen addresses:

nano /etc/postgresql/14/main/postgresql.conf

Uncomment and update the following line to listen on all interfaces:

listen_addresses = '*'

Restart the PostgreSQL service to apply the configuration changes:

systemctl restart postgresql

Create a Superuser

Create an administrative user, or superuser, for managing other users and databases. Log in to the PostgreSQL shell as the postgres user:

sudo -u postgres psql

Create a new superuser with a password:

CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password';

Verify the newly created user:

\du

The output should list the user and its attributes:

                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB                          | {}

Create a Database and User

To create a new database, run the following command:

create database testdb;

Create a new user:

create user testuser with encrypted password 'password';

Grant the new user privileges on the database:

grant all privileges on database testdb to testuser;

List all databases to confirm the creation:

\l

The created database should appear in the list:

                                      List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | testuser=CTc/postgres
(4 rows)

Connect PostgreSQL Remotely

With PostgreSQL configured to accept remote connections, you can now connect from other machines. To connect remotely, use the following command (replace the placeholders with your actual credentials and the server’s IP address):

psql 'postgres://testuser:password@postgres-ip-address:5432/testdb?sslmode=disable'

Conclusion

Congratulations! You have successfully installed and configured PostgreSQL on Ubuntu 22.04. PostgreSQL can now be utilized as a robust and reliable backend for your mission-critical needs. If you encounter any difficulties or have further questions, please don’t hesitate to reach out for assistance.