How To Install PostgreSQL 13 on Ubuntu 20.04 LTS

PostgreSQL, a powerful open-source relational database system, is widely used for its reliability, robustness, and performance in managing large datasets. Version 13 brings even more enhancements to indexing, partitioning, and query performance. In this guide, we’ll walk you through the steps to install PostgreSQL 13 on Ubuntu 20.04 LTS, setting up a stable and efficient database environment tailored to your needs.

Introduction

Postgresql database has been widely used in various critical applications, ranging from e-commerce, finance, energy, education and many more. 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 13.4 on Ubuntu 20.04 LTS operating system.

PostgreSQL 13 Installation On Ubuntu 20.04

On this tutorial, we will use PostgreSQL 13 source to be installed on Ubuntu 20.04 LTS server. For this purpose we need several dependencies package. The installation will be arranged as following steps :

1. Prerequisites
2. Adding PostgreSQL Repository To Ubuntu 20.04
3. Installing PostgreSQL 13 on Ubuntu 20.04
4. Initialize and start database service
5. Set PostgreSQL admin user’s password
6. Enabling Remote Access

Step 1 : Prerequisite

Before we are going to the install PostgreSQL database engine on Ubuntu 20.04, we have to prepare the environment first as mentioned below :

• Ubuntu 20.04 System with updated repository
• a user with sudo privilege
• Sufficient disk space and good internet access

Step 2 : Adding PostgreSQL Repository To Ubuntu 20.04

The PostgreSQL repository will be integrated with our normal systems and patch management and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL. On this step, we will import the repository signing GPG key to our system, by typing :

mpik@app01:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
mpik@app01:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK

We will verify the PostgreSQL packages by submitting command line below :

$ apt show postgresql -a

The output is as shown :

mpik@app01:~$ apt show postgresql -a
Package: postgresql
Version: 12+214ubuntu0.1
Priority: optional
Section: database
Source: postgresql-common (214ubuntu0.1)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 67.6 kB
Depends: postgresql-12
Suggests: postgresql-doc
Task: postgresql-server
Download-Size: 3,924 B
APT-Sources: http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.

Step 3 : Installing PostgreSQL 13 on Ubuntu 20.04

Before we initiate the installation process, we will update our local repository by submitting command line :

$ sudo apt-get update

The output will be shown below :

mpik@app01:~$ sudo apt-get update

After we got the repository, then we will install PostgreSQL database server by typing command line :

$ sudo apt-get -y install postgresql 

The output will be shown below :

mpik@app01:~$ sudo apt-get -y install postgresql
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
libevent-core-2.1-7 libevent-pthreads-2.1-7 libmecab2 libzip5 linux-headers-5.4.0-26 linux-headers-5.4.0-26-generic
linux-image-5.4.0-26-generic linux-modules-5.4.0-26-generic linux-modules-extra-5.4.0-26-generic mecab-ipadic mecab-ipadic-utf8 mecab-utils
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libtypes-serialiser-perl pgdg-keyring postgresql-13 postgresql-client-13
postgresql-client-common postgresql-common sysstat
Suggested packages:
postgresql-doc postgresql-doc-13 isag
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libtypes-serialiser-perl pgdg-keyring postgresql postgresql-13
postgresql-client-13 postgresql-client-common postgresql-common sysstat
0 upgraded, 12 newly installed, 0 to remove and 402 not upgraded.
Need to get 17.8 MB of archives.
...
Success. You can now start the database server using:

    pg_ctlcluster 13 main start

Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (13+226.pgdg20.04+1) ...
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) ...

Step 4 : Initialize and Starting PostgreSQL

By default, the PostgreSQL database service is up while installation, to verify it we will type the command line :

$ sudo systemctl status postgresql

The output will be shown below :

mpik@app01:~$ sudo systemctl status postgresql
[sudo] password for mpik: 
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2021-09-17 21:41:34 PDT; 6min ago
   Main PID: 6430 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 9436)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Sep 17 21:41:34 app01.otodiginet.com systemd[1]: Starting PostgreSQL RDBMS...
Sep 17 21:41:34 app01.otodiginet.com systemd[1]: Finished PostgreSQL RDBMS.

On this step, we have PostgreSQL is running.

Step 5 : Set PostgreSQL admin user’s password

On this step, we will set the PostgreSQL admin user’s password. This step need us to log in to the database and by using sql statement we will update the admin password. We will present it below :

mpik@app01:~$ sudo su postgres
[sudo] password for mpik: 
postgres@app01:/home/mpik$ psql
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))
Type "help" for help.

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
(3 rows)

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

After loging in, we modifty password by using SQL statement. On this case, we will update user postgres’ password to be Otodiginet2021.

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

 

Showing PostgreSQL version

To show PostgreSQL version, we will use the SQL command line as shown below :

postgres=# SELECT version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 (Ubuntu 13.4-1.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          
----------------------------------
 13.4 (Ubuntu 13.4-1.pgdg20.04+1)
(1 row)

postgres=# \! psql -V
psql (PostgreSQL) 13.4 (Ubuntu 13.4-1.pgdg20.04+1)

Step 6 : Enabling Remote Connection

By default, PostgreSQL only accepts connections from the localhost, while the connection from remote is still disable. To enable remote connection, we just update the /etc/postgresql/13/main/postgresql.conf file and /etc/postgresql/13/main/pg_hba.conf file for accepting remote connections.

After these files are updated then we just restart PostgreSQL service by typing command line :

$ sudo systemctl restart postgresql

Conclusion

On this short guidance, we have tried to install PostgreSQL 13 on Ubuntu 20.04 LTS operating system successfully.