Install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish)

It is so fascinating what the PostgreSQL community has done over the years to improve performance for heavy transactional workloads and great support for distributed data. PostgresSQL database has been around for many years, and it has earned trust of many big companies who use it to power applications handling millions of transactions per day. As of the time we’re updating this article, the most recent and stable release is PostgreSQL 14.

PostgresSQL database has a good reputation for its reliability, robustness, and performance with over 30 years of active development. It is also an SQL compliant database management system used as a primary data storage for web, mobile applications, banking applications, analytics, and many other applications.

PostgreSQL 14 release comes with a lot of new features and performance improvements fixes and patches from its predecessor. Some of the notable features in PostgreSQL 14 release are:

• Decoding of prepared transactions.
• Support for logical replication of in-progress transactions.
• Logical replication can now transfer data in binary format.
• Performance improvement of logical decoding of transactions containing DDLs.
• Multiple transactions are now allowed during table synchronization in logical replication.
• The ALTER SUBSCRIPTION statement now makes it easier to add/remove publications, with the addition of ADD PUBLICATION and DROP PUBLICATION options.
• Procedures can have OUT parameters.
• The CREATE FUNCTION and CREATE PROCEDURE statements now support SQL language.
• TRUNCATE can operate on foreign tables using the postgres_fdw module.
• Support for multirange data types was added.
• pg_amcheck command-line utility was added to simplify running contrib/amcheck operations on many relations.
• Some GiST indexes can now be built by pre-sorting the data.
• BRIN indexes can now record multiple min/max values per range.
• BRIN indexes can now use bloom filters.

Install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish)

Follow this article to install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish). Our guide will also capture configurations of the database, creation of user, setting superuser password and connecting to the database from a remote client.

Step 1: Install dependencies

On your Ubuntu 22.04 (Jammy Jellyfish), perform a system update and upgrade

sudo apt update && sudo apt -y full-upgrade

When kernel updates are committed a system reboot will be required:

[ -f /var/run/reboot-required ] && sudo reboot -f

After the reboot install key packages required to configure PostgreSQL repository.

Install the necessary packages:

sudo apt install vim curl wget gpg gnupg2 software-properties-common apt-transport-https lsb-release ca-certificates

Step 2: Add PostgreSQL repository

The version of PostgreSQL that exists in the default OS package repositories is not up-to-date for the default version.

apt policy postgresql

We recommend you add the repository and install PostgreSQL 14 on Ubuntu 22.04 from it. Let’s import GPG key used in signing packages.

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

Add the PostgreSQL repository on your Ubuntu 22.04 system using the commands below.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Inform the system about the newly added repository.

sudo apt update

Repo metadata sync should be successful for newly added repository:

Hit:1 http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease

Step 3: Install PostgreSQL 14

Now that we have added the repository let’s install PostgreSQL 14 with the commands below.

sudo apt install postgresql-14

PostgreSQL service should be started automatically and can be verified as below.

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 14-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Sat 2022-06-11 11:18:23 EAT; 1min 2s ago
    Process: 4139 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 14-main start (code=exited, status=0/SUCCESS)
   Main PID: 4144 (postgres)
      Tasks: 7 (limit: 9460)
     Memory: 18.0M
        CPU: 165ms
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─4144 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
             ├─4146 "postgres: 14/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
             ├─4147 "postgres: 14/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
             ├─4148 "postgres: 14/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
             ├─4149 "postgres: 14/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
             ├─4150 "postgres: 14/main: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
             └─4151 "postgres: 14/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >

Jun 11 11:18:21 ubuntu22 systemd[1]: Starting PostgreSQL Cluster 14-main...
Jun 11 11:18:23 ubuntu22 systemd[1]: Started PostgreSQL Cluster 14-main.
lines 1-19/19 (END)

Install version of PostgreSQL can be checked with the commands below.

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

Sample Output:

                                                             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)

Step 4: Connect to PostgreSQL 14 database

There are two ways you can access PostgreSQL management console

1. Run postgres command

$ sudo -u postgres psql
psql (14.3 (Ubuntu 14.3-1.pgdg22.04+1))
Type "help" for help.

postgres=#

Option 2.

For this option, you first have to switch to the Postgres user created after PostgreSQL 14 installation.

sudo -i -u postgres

While here, connect to the instance.

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

postgres=#

Now with any of the above methods used to connect to the instance, you can still manage your database.

Step 5: Configure remote access (optional)

PostgreSQL has support for multiple authentication methods for client authentication through a concept called roles. The default authentication method is ident authentication which associates Postgres role with a Unix system account. All supported authentication methods supported are:

• Ident – supported via TCP/IP connections only. It obtains the clients system username with an optional username mapping.
• Password – a role connects using a password.
• Peer – similar to ident but only supported on local connections.
• Trust – allows a role to connect as long as conditions defined in the pg_hba.conf are fulfilled.

For our database server instance to be accessed remotely, we should make changes in the file /etc/postgresql/14/main/pg_hba.conf.

Allow password authentication on your PostgreSQL server by running the commands below.

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/14/main/pg_hba.conf

Next is to change the identification method from peer to trust as below.

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/14/main/pg_hba.conf

To allow the instance to be accessed from everywhere, edit the command as below:

sudo vim /etc/postgresql/14/main/pg_hba.conf

In the file, add the lines below.

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0                md5

# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

Now ensure that the service is listening on * by editing the conf file at /etc/postgresql/14/main/postgresql.conf as below.

sudo vim /etc/postgresql/14/main/postgresql.conf

In the file, uncomment and edit the line as below.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------------------
.......
listen_addresses='*'

Now restart and enable PostgreSQL for the changes to take effect.

sudo systemctl restart postgresql
sudo systemctl enable postgresql

Step 6: Managing users in PostgreSQL 14

Connect to your PostgreSQL instance using psql command:

sudo -u postgres psql

We shall create a new superuser called adminwith the password Passw0rd.

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';

Verify the user has been created with the required privileges.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# 

From the Postgres shell let’s create a user with the name demo1 and assign it access to demodb

create database demodb;
create user demo1 with encrypted password 'Passw0rd';
grant all privileges on database demodb to demo1;

Step 7: Connecting to remote PostgreSQL

Check if your PostgreSQL service is listening on port 5432.

$ ss -tunelp | grep 5432
tcp    LISTEN   0        244               0.0.0.0:5432           0.0.0.0:*      uid:123 ino:292971 sk:a <->                                                    
tcp    LISTEN   0        244                  [::]:5432              [::]:*      uid:123 ino:292972 sk:d v6only:1 <-> 

If you have UFW firewall then allow the port for remote connectivity.

sudo ufw allow 5432/tcp

Syntax for connecting with psql command from a remote client machine is;

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

Consider an example below as demonstration on how a remote connection can be initiated.

psql 'postgres://admin:[email protected]:5432/postgres?sslmode=disable'

Recommended books:

• Best Books To Learn PostgreSQL Databases

Wrapping up

To this point we have been able to install and configure PostgreSQL 14 on Ubuntu 22.04 Linux machine. We hope this guide was of great help to you. If you encounter any issues kindly let us know in the comments section. Cheers!

Similar articles available on our website:

• Install PostgreSQL 14 on Debian
• Install PostgreSQL 14 CentOS 7 | RHEL 7
• Install PostgreSQL 14 on Fedora
• Best Books To Learn PostgreSQL Database