How to Install PostgreSQL on Ubuntu 22.04

This tutorial exists for these OS versions

PostgreSQL is an open-source and one of the most popular relational database management systems. It implements the SQL query language and offers strong stability, performance, and data integrity. You can use the PostgreSQL database to run mission-critical applications. PostgreSQL has features like Nest transactions, multi-version concurrency control, table inheritance, asynchronous replication, foreign key referential integrity, and more.

This post will show you how to install PostgreSQL database on Ubuntu 22.04.

Prerequisites

A server running Ubuntu 22.04.
A root password is configured on the server.

Getting Started

First, update and upgrade all system packages to the latest version using the following command:

apt update -y
apt upgrade -y

Once your system is up-to-date, you can install other required dependencies using the following command:

apt install wget curl gnupg2 -y

After installing all the dependencies, you can proceed to install InfluxDB.

Add PostgreSQL Repository

By default, the latest version of PostgreSQL is not available in the Ubuntu 22.04 default repository. So you will need to add the PostgreSQL official repository to your system.

you can add the PostgreSQL repository and GPG key using the following command:

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null

Next, update the PostgreSQL repository using the following command:

apt update -y

Install PostgreSQL on Ubuntu 22.04

You can now install PostgreSQL 15 by running the following command:

apt install postgresql postgresql-client -y

Once the PostgreSQL has been installed, verify the status of the PostgreSQL using the following command:

systemctl status postgresql

You should get the following output:

? postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2022-12-05 14:28:18 UTC; 1min 57s ago
   Main PID: 10824 (code=exited, status=0/SUCCESS)
        CPU: 4ms

Dec 05 14:28:18 ubuntu2204 systemd[1]: Starting PostgreSQL RDBMS...
Dec 05 14:28:18 ubuntu2204 systemd[1]: Finished PostgreSQL RDBMS.

To check the PostgreSQL version, run the following command:

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

You will get the following output:

                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
(1 row)

Configure PostgreSQL for Remote Connection

By default, PostgreSQL allows connections only from the localhost. If you want to allow remote connection then you will need to edit the PostgreSQL configuration file and define the authentication method. You can do it by editing the following file:

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

Change the following lines:

local   all             all                                     trust

host    all             all             0.0.0.0/0                md5

Save and close the file when you are done. Then, edit the PostgreSQL main configuration file and change the listen port:

nano /etc/postgresql/15/main/postgresql.conf

Change the following line:

listen_addresses='*'

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

systemctl restart postgresql

Create an Administrative User

It is a good idea to create an administrative user to manage other users and databases. First, log in to the PostgreSQL shell using the following command:

sudo -u postgres psql

Once you are logged in, you will get the following shell:

psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
Type "help" for help.

postgres=#

Next, create an admin user and set a password with the following command:

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

Next, verify the created user using the following command:

\du

You should see the following output:

                                   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 in PostgreSQL

In this section, we will show you how to create a database and user in PostgreSQL.

To create a database named wpdb in PostgreSQL, run the following command:

create database wpdb;

To create a user named wpuser in PostgreSQL, run the following command:

create user wpuser with encrypted password 'password';

To grant all the privileges to the wpdb database, run the following command:

grant all privileges on database wpdb to wpuser;

You can list all databases using the following command:

\l

You should get a list of all databases in the following output:

                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 wpdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres         +
           |          |          |             |             |            |                 | postgres=CTc/postgres+
           |          |          |             |             |            |                 | wpuser=CTc/postgres
(4 rows)

How to Connect PostgreSQL from the Remote Machine

At this point, PostgreSQL is installed and configured to allow connections from the remote hosts. To verify the PostgreSQL remote connection, run the following command:

psql 'postgres://wpuser:password@postgres-ip-address:5432/wpdb?sslmode=disable'

Conclusion

Congratulations! you have successfully installed and configured the PostgreSQL server on Ubuntu 22.04. You can now use PostgreSQL in a production environment to achieve high performance and data integrity. Feel free to ask me if you have any questions.

Log in or Sign up