January 26, 2022
How to install PostgreSQL 14 on Ubuntu 20.04

How To Install PostgreSQL 14 on Ubuntu 20.04 LTS

On this tutorial we will learn how to install PostgreSQL 14.1 (the last stable version when this article was written) on Ubuntu 20.04 LTS operating system.

Introduction

PostgreSQL or also known as Postgres, is one of the famous free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. Currently, PostgreSQL version 14.1 is the latest stable version which was released on 11 November 2021. On this article we will learn how to install PostgreSQL version 14.1 on Ubuntu 20.04 LTS.

PostgreSQL 14 New Features

As stated on PostgreSQL 14 release note, if version 14 is major release from previous release (version 13) and version 14.1 contains a variety of fixes from PostgreSQL version 14.0.

PostgreSQL 14 contains many new features and enhancements, including:

  • Stored procedures can now return data via OUT parameters.
  • The SQL-standard SEARCH and CYCLE options for common table expressions have been implemented.
  • Subscripting can now be applied to any data type for which it is a useful notation, not only arrays. In this release, the jsonb and hstore types have gained subscripting operators.
  • Range types have been extended by adding multiranges, allowing representation of noncontiguous data ranges.
  • Numerous performance improvements have been made for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.
  • B-tree index updates are managed more efficiently, reducing index bloat.
  • VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.
  • Extended statistics can now be collected on expressions, allowing better planning results for complex queries.
  • libpq now has the ability to pipeline multiple queries, which can boost throughput over high-latency connections.

PostgreSQL 14.1 Installation on Ubuntu 20.04

The source of PostgreSQL is located on PostgreSQL official website, and we will use PostgreSQL 14.1 source from this site.

Prerequisites

Before we are starting PostgreSQL 14 installation, there are will be prerequisites to be fulfill, as mention below :

  • Ubuntu 20.04 LTS System with sufficient disk space
  • root or non-root user with administrative privileges

The PostgreSQL 14 installaton will be consist of several steps, as described on the following steps.

1. Adding PostgreSQL 14.1 Repository To Ubuntu 20.04 System

On this step, we will create the a repository configuration file, by typing command line :

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

hen import the PostgreSQL 14.1 repository signing key, by typing command line :

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then update our Ubuntu package lists, by typing command line :

$ sudo apt -y update

The output of series command line above are as shown below :

rapik@worker2:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
rapik@worker2:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
rapik@worker2:~$ sudo apt -y update
Hit:1 https://packages.microsoft.com/repos/edge stable InRelease
Hit:2 http://ppa.launchpad.net/alexlarsson/flatpak/ubuntu focal InRelease                                                     
Hit:3 http://deb.anydesk.com all InRelease                                                                                    
Hit:4 http://security.ubuntu.com/ubuntu focal-security InRelease                                                              
Hit:5 http://us.archive.ubuntu.com/ubuntu focal InRelease                                                                     
Err:3 http://deb.anydesk.com all InRelease                                                                                    
  The following signatures were invalid: EXPKEYSIG 18DF3741CDFFDE29 philandro Software GmbH <info@philandro.com>
Hit:6 http://ppa.launchpad.net/team-xbmc/ppa/ubuntu focal InRelease                                                           
Hit:7 http://download.opensuse.org/repositories/devel:/kubic:/libcontainers:/stable/xUbuntu_20.04  InRelease                  
Hit:8 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease                                                             
Get:9 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [86.6 kB]                                                  
Hit:10 http://ppa.launchpad.net/ubuntuhandbook1/gimp/ubuntu focal InRelease                          
Hit:11 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease                                 
Hit:12 https://shop.softmaker.com/repo/apt stable InRelease                     
Get:13 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [224 kB]
Fetched 311 kB in 3s (97.3 kB/s)  
Reading package lists... Done
Building dependency tree       
Reading state information... Done

2. Installing PostgreSQL 14 On Ubuntu 20.04

After all are set, the we will install the latest PostgreSQL 14.1, by typing command line :

$ sudo apt -y install postgresql-14

The output will be as following :

rapik@worker2:~$ sudo apt -y install postgresql-14
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  gimp-data kodi-inputstream-adaptive kodi-inputstream-rtmp kodi-peripheral-joystick libamd2 libaom2 libass9 libbabl-0.1-0
  libcamd2 libccolamd2 libcdio19 libcec4 libcholmod3 libcrossguid0 libdav1d5 libde265-0 libfstrcmp0 libgegl-0.4-0
  libgegl-common libgimp2.0 libheif1 libiso9660-11 libmariadb3 libmetis5 libmicrohttpd12 libmng2 libmypaint-1.5-1
  libmypaint-common libp8-platform2 libsdl2-2.0-0 libshairplay0 libtinyxml2.6.2v5 libudfread0 libumfpack5 libva-wayland2
  libwayland-client++0 libwayland-cursor++0 libwayland-egl++0 libx265-192 mariadb-common python3-bluez python3-pycryptodome
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libpq-dev libpq5 libssl-dev libssl1.1 libtypes-serialiser-perl
  pgdg-keyring postgresql-client-14 postgresql-client-common postgresql-common sysstat
Suggested packages:
  postgresql-doc-14 libssl-doc isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libssl-dev libtypes-serialiser-perl pgdg-keyring postgresql-14
  postgresql-client-14 postgresql-client-common postgresql-common sysstat
The following packages will be upgraded:
  libpq-dev libpq5 libssl1.1
3 upgraded, 11 newly installed, 0 to remove and 335 not upgraded.
Installing PostgreSQL on Ubuntu 20.04 LTS
Installing PostgreSQL on Ubuntu 20.04 LTS

By default, after installation the PostgreSQL service has been enabled and running. Then we will verify it by typing command line :

$ sudo systemctl status postgresql

The output will be as shown below :

rapik@worker2:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sat 2022-01-01 06:55:53 PST; 1min 9s ago
   Main PID: 31795 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 8782)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Jan 01 06:55:53 worker2.otodiginet.com systemd[1]: Starting PostgreSQL RDBMS...
Jan 01 06:55:53 worker2.otodiginet.com systemd[1]: Finished PostgreSQL RDBMS.

3. Verifying PostgreSQL Version

After installation was completed done, then we will verify it by querying its version. We will use the following command lines.

PostgreSQL Client version

To verify the PostgreSQL version for the client we will submit command line from shell.

$ /usr/lib/postgresql/14/bin/psql --version

The output will be as follow :

rapik@worker2:~$ /usr/lib/postgresql/14/bin/psql --version
psql (PostgreSQL) 14.1 (Ubuntu 14.1-2.pgdg20.04+1)

PostgreSQL server version

To verify PostgreSQL version on the server side, we will use the query via console to our new database.

rapik@worker2:~$ sudo -u postgres psql -c "SELECT version();"
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

rapik@worker2:~$ sudo -u postgres psql -c "SHOW server_version;"
          server_version          
----------------------------------
 14.1 (Ubuntu 14.1-2.pgdg20.04+1)
(1 row)

Or we just log in first to database and do the query inside it.

rapik@worker2:~$ sudo -i -u postgres
postgres@worker2:~$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# SELECT version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

postgres=# SHOW server_version;
          server_version          
----------------------------------
 14.1 (Ubuntu 14.1-2.pgdg20.04+1)
(1 row)
PostgreSQL version
PostgreSQL version

4. Setting PostgreSQL admin user’s password

The user postgres user is created automatically durig the installation. For administrative purpose, we have to set the admin user’s password. For this purpose, we have to login to database and setting the password. On this section, we will list the database and existing user on the PostgreSQL database and updating the postgres password.

postgres=# alter user postgres with password 'Otodiginet2022';
ALTER ROLE

postgres=# \l
                                    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
(4 rows)

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

5. Configuring Remote Connection

When we install PostgreSQL database engine on Ubuntu 20.04, it is only accepts connections from the localhost, while the connection from remote is still disable. To enable remote connections to our fresh PostgreSQL 14 database server we have to edit a configuration files for it, named as : postgresql.conf and pg_hba.conf which is located on the /etc/postgresql/13/main/ directory.

Updating /etc/postgresql/14/main/postgresql.conf file, to allow all IP Address, or we will put it with subnet or even IP Address to listens address of our PostgreSQL database host.

rapik@worker2:~$ sudo vi /etc/postgresql/14/main/postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                 # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)

Updating /etc/postgresql/14/main/pg_hba.conf to accept remote connections from allowed hosts, we will add the folling ip address.

rapik@worker2:~$ sudo vi /etc/postgresql/14/main/pg_hba.conf

# Accept from anywhere
host    all             all             0.0.0.0/0               md5

Conclusion

Finally, we have shown you, how to install PostgreSQL 14.1 on Ubuntu 20.04 LTS operating system. I hope this article will be useful for anyone who is finding PostgreSQL installation guidance. More detailed information about PostgreSQL 14 can be found on the official PostgreSQL website.

Share this article via :

One thought on “How To Install PostgreSQL 14 on Ubuntu 20.04 LTS

Leave a Reply

Your email address will not be published. Required fields are marked *