How To Install PostgreSQL 14 on Ubuntu 20.04

In this short tutorial we will learn how to install PostgreSQL 14.1 on Ubuntu 20.04 LTS Server.

Introduction

Currently, the last stable PostgreSQL is version 14.1 which as released on 11 November 2021. PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. On this tutorial we will learn how to install PostgreSQL version 14 on Ubuntu 20.04 LTS operating system.

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 Installation On Ubuntu 20.04 LTS

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
  • firewall configured with firewalld

PostgreSQL Installation Steps

The PostgreSQL installaton will be consist of several steps :

  1. Adding PostgreSQL 14 Repository To Ubuntu 20.04
  2. Install PostgreSQL 14 on Ubuntu 20.04
  3. Set PostgreSQL admin user’s password
  4. Enabling Remote Access

1. Adding PostgreSQL Repository To Ubuntu 20.04

PostgreSQL (Postgres) is provided in the default Ubuntu repositories. To check the version provides, use this command:

$ sudo apt-cache search postgresql | grep postgresql

Output :

rapik@worker2:~$ sudo apt-cache search postgresql | grep postgresql
[sudo] password for rapik: 
postgresql - object-relational SQL database (supported version)
postgresql-12 - object-relational SQL database, version 12 server
postgresql-client - front-end programs for PostgreSQL (supported version)
postgresql-client-12 - front-end programs for PostgreSQL 12
postgresql-client-common - manager for multiple PostgreSQL client versions
postgresql-common - PostgreSQL database-cluster manager
postgresql-contrib - additional facilities for PostgreSQL (supported version)
postgresql-doc - documentation for the PostgreSQL database management system
postgresql-doc-12 - documentation for the PostgreSQL database management system
akonadi-backend-postgresql - PostgreSQL storage backend for Akonadi
autopostgresqlbackup - Automated tool to make periodic backups of PostgreSQL databases

...

postgresql-all - metapackage depending on all PostgreSQL server packages
postgresql-autodoc - Utility to create a PostgreSQL database schema overview in HTML, DOT and XML
postgresql-comparator - efficient PostgreSQL table content comparison and synchronization
postgresql-filedump - Dump PostgreSQL files in human-readable form
postgresql-pgsphere - Spherical data types for PostgreSQL
postgresql-plperl-12 - PL/Perl procedural language for PostgreSQL 12
postgresql-plpython3-12 - PL/Python 3 procedural language for PostgreSQL 12
postgresql-pltcl-12 - PL/Tcl procedural language for PostgreSQL 12
postgresql-q3c - PostgreSQL extension used for indexing the sky
postgresql-server-dev-12 - development files for PostgreSQL 12 server-side programming
postgresql-server-dev-all - extension build tool for multiple PostgreSQL versions
puppet-module-puppetlabs-postgresql - Puppet module for PostgreSQL database
pure-ftpd-postgresql - Secure and efficient FTP server with PostgreSQL user authentication
python3-multicorn - multicorn utility module for postgresql-X.Y-python3-multicorn
python3-postgresql - pgsql driver, cluster management tools, and client tools
python3-testing.postgresql - Python testing.* framework - PostgreSQL helpers (Python 3)
r-cran-rpostgresql - GNU R package providing database interface and driver for PostgreSQL
rt4-db-postgresql - PostgreSQL database backend for request-tracker4
sisu-postgresql - SiSU dependencies for use with PostgreSQL database
tarantool-lts-postgresql-module - Tarantool in-memory database - PostgreSQL connector
tntdb-postgresql4v5 - PostgreSQL backend for tntdb database access library

From information above, PostgreSQL 14 is not included yet on the system. Then we will add PostgreSQL 14 by submitting 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'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Output :

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

2. Install PostgreSQL 14 on Ubuntu 20.04

On this stage we will install PostgreSQL on Ubuntu 20.04. For this purpose we will do following command lines.

$ sudo apt -y update
$ sudo apt -y install postgresql-14

Output :

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.
Need to get 21.5 MB of archives.
After this operation, 69.7 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq-dev amd64 14.1-2.pgdg20.04+1 [137 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl1.1 amd64 1.1.1f-1ubuntu2.10 [1,322 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq5 amd64 14.1-2.pgdg20.04+1 [170 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-common all 232.pgdg20.04+1 [91.6 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-14 amd64 14.1-2.pgdg20.04+1 [1,602 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-14 amd64 14.1-2.pgdg20.04+1 [1,602 kB]  
Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-common all 232.pgdg20.04+1 [225 kB]            
Get:8 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-14 amd64 14.1-2.pgdg20.04+1 [15.7 MB]          
Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl1.1 amd64 1.1.1f-1ubuntu2.10 [1,322 kB]              
Get:9 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libcommon-sense-perl amd64 3.74-2build6 [20.1 kB]                  
Get:10 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-perl all 4.02000-2 [80.9 kB]                              
Get:11 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]                      
Get:12 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB]                     
Get:13 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl-dev amd64 1.1.1f-1ubuntu2.10 [1,584 kB]            
Get:14 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 sysstat amd64 12.2.0-2ubuntu0.1 [448 kB]                  
Fetched 20.6 MB in 2min 1s (169 kB/s)                                                                                         
Preconfiguring packages ...
(Reading database ... 203680 files and directories currently installed.)
Preparing to unpack .../00-libssl1.1_1.1.1f-1ubuntu2.10_amd64.deb ...
Unpacking libssl1.1:amd64 (1.1.1f-1ubuntu2.10) over (1.1.1f-1ubuntu2.8) ...
Selecting previously unselected package libcommon-sense-perl.
Preparing to unpack .../01-libcommon-sense-perl_3.74-2build6_amd64.deb ...
Unpacking libcommon-sense-perl (3.74-2build6) ...
Selecting previously unselected package libjson-perl.
Preparing to unpack .../02-libjson-perl_4.02000-2_all.deb ...
Unpacking libjson-perl (4.02000-2) ...
Selecting previously unselected package libtypes-serialiser-perl.
Preparing to unpack .../03-libtypes-serialiser-perl_1.0-1_all.deb ...
Unpacking libtypes-serialiser-perl (1.0-1) ...
Selecting previously unselected package libjson-xs-perl.
Preparing to unpack .../04-libjson-xs-perl_4.020-1build1_amd64.deb ...
Unpacking libjson-xs-perl (4.020-1build1) ...
Preparing to unpack .../05-libpq-dev_14.1-2.pgdg20.04+1_amd64.deb ...
Unpacking libpq-dev (14.1-2.pgdg20.04+1) over (12.9-0ubuntu0.20.04.1) ...
Preparing to unpack .../06-libpq5_14.1-2.pgdg20.04+1_amd64.deb ...
Unpacking libpq5:amd64 (14.1-2.pgdg20.04+1) over (12.9-0ubuntu0.20.04.1) ...
Selecting previously unselected package libssl-dev:amd64.
Preparing to unpack .../07-libssl-dev_1.1.1f-1ubuntu2.10_amd64.deb ...
Unpacking libssl-dev:amd64 (1.1.1f-1ubuntu2.10) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../08-pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../09-postgresql-client-common_232.pgdg20.04+1_all.deb ...
Unpacking postgresql-client-common (232.pgdg20.04+1) ...
Selecting previously unselected package postgresql-client-14.
Preparing to unpack .../10-postgresql-client-14_14.1-2.pgdg20.04+1_amd64.deb ...
Unpacking postgresql-client-14 (14.1-2.pgdg20.04+1) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../11-postgresql-common_232.pgdg20.04+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (232.pgdg20.04+1) ...
Selecting previously unselected package postgresql-14.
Preparing to unpack .../12-postgresql-14_14.1-2.pgdg20.04+1_amd64.deb ...
Unpacking postgresql-14 (14.1-2.pgdg20.04+1) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../13-sysstat_12.2.0-2ubuntu0.1_amd64.deb ...
Unpacking sysstat (12.2.0-2ubuntu0.1) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up libssl1.1:amd64 (1.1.1f-1ubuntu2.10) ...
Setting up libpq5:amd64 (14.1-2.pgdg20.04+1) ...
Setting up libcommon-sense-perl (3.74-2build6) ...
Setting up libssl-dev:amd64 (1.1.1f-1ubuntu2.10) ...
Setting up libtypes-serialiser-perl (1.0-1) ...
Setting up libjson-perl (4.02000-2) ...
Setting up sysstat (12.2.0-2ubuntu0.1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up postgresql-client-common (232.pgdg20.04+1) ...
Setting up libjson-xs-perl (4.020-1build1) ...
Setting up libpq-dev (14.1-2.pgdg20.04+1) ...
Setting up postgresql-client-14 (14.1-2.pgdg20.04+1) ...
update-alternatives: using /usr/share/postgresql/14/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in 
auto mode
Setting up postgresql-common (232.pgdg20.04+1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-14 (14.1-2.pgdg20.04+1) ...
Creating new PostgreSQL cluster 14/main ...
/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/14/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (po
stmaster.1.gz) in auto mode
Processing triggers for systemd (245.4-4ubuntu3.11) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9) ...

By default, PosrgreSQL service will be started after installation was completed. We wil verfiry the installation was successful by checking its service, by submitting command line :

$ sudo systemctl status postgresql

Output :

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.
rapik@worker2:~$ 

We can also verify the postgreSQL version we have installed with the command line:

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

Output :

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

or by using command line below :

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

Output :

rapik@worker2:~$ sudo -u postgres psql -c "SELECT 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)

3. Set PostgreSQL admin user’s password

In this section, we will set the superuser password. For this purpose we have to log in to PostgreSQL and do sql script for setting admin user (superuser) password.

Login to PostgreSQL database.

$ sudo -i -u postgres

Output :

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=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password123';
CREATE ROLE
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                          | {}

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

postgres=# 

4. Enabling Remote Access

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 file for it, named as : postgresql.conf. We will modify $ sudo vi /etc/postgresql/14/main/postgresql.conf file, as shown below.

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

 TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# Accept from anywhere
host    all             all             0.0.0.0/0               md5

Conclusion

in this tutorial, we have learned to install Postrgresql 14 database on ubuntu 20.04 successfully. Since PostgreSQL 14 is not yet in the default Ubuntu 20.04 repository, we have to add it manually.
Hopefully this simple tutorial is useful for those in need.

One Comment

Add a Comment

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