Installing and Running PostgreSQL DB Server on Ubuntu 22

Introduction

PostgreSQL is a widely-used, free, and open-source relational database management system. It is renowned for its stability, performance, and data integrity. This robust SQL implementation is particularly suitable for mission-critical applications and offers features like nested transactions, multiversion concurrency control, table inheritance, asynchronous replication, and referential integrity of foreign keys.

Prerequisites

Ensure you have the following before proceeding:

• A server running Ubuntu 22.04.
• A root password set up on the server.

Add PostgreSQL Repository

The latest PostgreSQL version isn’t available in the default Ubuntu 22.04 repository, so you’ll need to add the official PostgreSQL repository to your system.

First, install the necessary dependencies:

apt-get install gnupg2 curl wget -y

Then, 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 PostgreSQL repository:

apt update -y

Install PostgreSQL 14

Now, install PostgreSQL 14:

apt install postgresql-14 -y

Check PostgreSQL’s status:

systemctl status postgresql

You should see an output similar to this:

? 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.

Verify the PostgreSQL version:

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

The output should be:

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

PostgreSQL defaults to only allowing local connections. To permit remote connections, modify the configuration files.

Edit the authentication method in the pg_hba.conf file:

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

Change these lines:

local all all trust
host all all 0.0.0.0/0 md5

Next, edit the main PostgreSQL configuration file to change the listen addresses:

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

Update the following line:

listen_addresses='*'

Restart the PostgreSQL service to apply changes:

systemctl restart postgresql

Create a Superuser

Create an administrative user for managing other users and databases. Log in to the PostgreSQL shell:

sudo -u postgres psql

Create a superuser with a password:

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

Verify the user:

\du

The output should be:

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:

create database testdb;

Create a new user:

create user testuser with encrypted password 'password';

Grant privileges to the new user:

grant all privileges on database testdb to testuser;

List all databases:

\l

You should see something like this:

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

PostgreSQL is now set up to accept remote connections. To connect remotely:

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 used as a reliable backend for your mission-critical applications. If you have any questions, feel free to contact us.