How To Install PostgreSQL 13 On Ubuntu 20.04 LTS

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

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

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

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.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

Package: postgresql
Version: 12+214
Priority: optional
Section: database
Source: postgresql-common (214)
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: 4,004 B
APT-Sources: http://us.archive.ubuntu.com/ubuntu focal/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

2. 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
Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [86.7 kB]                                                                  
Get:2 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]                                                                     
Hit:3 http://ppa.launchpad.net/ondrej/php/ubuntu focal InRelease                                                                              
Hit:5 http://us.archive.ubuntu.com/ubuntu focal InRelease                                                                                     
Hit:6 http://ppa.launchpad.net/uroni/urbackup/ubuntu focal InRelease                                                                          
Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]                                                                    
Get:8 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [214 kB]                                                         
Get:9 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [27.6 kB]                                                   
Get:10 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [61.3 kB]                                              
Get:11 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [2,464 B]                                            
Get:12 https://dlm.mariadb.com/repo/mariadb-server/10.6/repo/ubuntu focal InRelease [6,264 B]                                                 
Get:14 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [101 kB]                                              
Hit:4 https://downloads.mariadb.com/Tools/ubuntu focal InRelease
Hit:13 https://downloads.mariadb.com/MaxScale/6.1.1/apt focal InRelease
Get:15 http://us.archive.ubuntu.com/ubuntu focal-updates/main i386 Packages [536 kB]                                                          
Get:16 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [1,214 kB]                                                       
Get:17 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [283 kB]                                                  
Get:18 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [855 kB]                                                     
Get:19 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [352 kB]                                              
Get:20 http://us.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [944 B]                                             
Get:21 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [10.4 kB]                                           
Fetched 3,978 kB in 28s (143 kB/s)                                                                                                            
Reading package lists... Done

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.
After this operation, 59.4 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq5 amd64 13.4-1.pgdg20.04+1 [179 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libcommon-sense-perl amd64 3.74-2build6 [20.1 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-perl all 4.02000-2 [80.9 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 226.pgdg20.04+1 [90.6 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-13 amd64 13.4-1.pgdg20.04+1 [1,507 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 sysstat amd64 12.2.0-2ubuntu0.1 [448 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-common all 226.pgdg20.04+1 [246 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-13 amd64 13.4-1.pgdg20.04+1 [15.1 MB]
Get:12 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql all 13+226.pgdg20.04+1 [65.2 kB]
Fetched 17.8 MB in 25s (723 kB/s)
Preconfiguring packages 
…
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

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) ...

3. 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.

4. 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)

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.