How to Install PostgreSQL on Ubuntu 20.04 LTS [Easy Steps]

Table of Contents

How to Install PostgreSQL on Ubuntu 20.04 LTS

Also Read: 8 Useful apt-add-repository command examples in Linux [Cheat Sheet]

Step 1: Prerequisites

a) You should have a running Ubuntu 20.04 LTS Server.

b) You should have sudo or root access to run privileged commands.

c) You should have apt or apt-get utility installed in Your Server.

d) You should also have wget, apt-key and mlocate utility available in your Server.

Step 2: Add Repository

As PostgreSQL packages are not available through Ubuntu repositories, we need to add the required repository configuration in /etc/apt/sources.list.d/pgdg.list from where we can download the PostgreSQL package by using below command.

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

Step 3: Import Repository Signing Key

Next, you need to import the repository signing key by downloading it through wget utility and adding it using apt-key command as shown below.

root@localhost:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK

Step 4: Update Your Server

Once repository is added, you can now update the package cache with list of packages from repository by using apt-get update command as shown below.

root@localhost:~# apt-get update
Hit:1 https://apt.releases.hashicorp.com focal InRelease
Hit:2 http://in.archive.ubuntu.com/ubuntu focal InRelease
Hit:3 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease
Hit:4 https://packages.microsoft.com/repos/edge stable InRelease
Hit:5 http://in.archive.ubuntu.com/ubuntu focal-backports InRelease
Hit:6 http://ppa.launchpad.net/ansible/ansible/ubuntu focal InRelease
Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [86.6 kB]
Get:8 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Ign:9 http://ppa.launchpad.net/oguzhaninan/stacer/ubuntu focal InRelease
Get:10 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [220 kB]
Get:11 http://ppa.launchpad.net/micahflee/ppa/ubuntu focal InRelease [17.5 kB]
Err:11 http://ppa.launchpad.net/micahflee/ppa/ubuntu focal InRelease

Step 5: Install PostgreSQL

You can install the latest version of postgresql by running apt-get -y install postgresql command or you can install the specific version by running apt-get -y install postgresql-14 command.

root@localhost:~# 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:
libegl1-mesa libxcb-xinerama0 libxcb-xtest0
Use '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-14 postgresql-client-14
postgresql-client-common postgresql-common
Suggested packages:
postgresql-doc postgresql-doc-14
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl pgdg-keyring postgresql postgresql-14 postgresql-client-14
The following packages will be upgraded:
libpq5 postgresql-client-common postgresql-common
3 upgraded, 8 newly installed, 0 to remove and 3 not upgraded.
Need to get 18.1 MB of archives.
After this operation, 59.4 MB of additional disk space will be used.

Step 6: Check PostgreSQL Service

By default after installation, postgresql service will be started automatically. You can check the status by running /etc/init.d/postgresql status command. But in case it is not in active state, you can start or restart the service by using /etc/init.d/postgresql start or /etc/init.d/postgresql restart command.

root@localhost:~# /etc/init.d/postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sun 2021-10-24 01:06:49 IST; 11min ago
Main PID: 9704 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 2312)
Memory: 0B
CGroup: /system.slice/postgresql.service

Oct 24 01:06:49 localhost systemd[1]: Starting PostgreSQL RDBMS...
Oct 24 01:06:49 localhost systemd[1]: Finished PostgreSQL RDBMS.

Step 7: Check Postgres Version

There is a chance that postgres utility will not be available in the location where System usually look for all the binaries. So you might need to find the utility by using locate postgres command. In my system, it is installed under /usr/lib/postgres/14/bin directory. This path might be different for you. Once you located your package you can now run the utility by either specifying the entire path or adding this path in $PATH variable and then just use postgres command. For now I am using entire path to check the version of PostgreSQL Server currently installed by running /usr/lib/postgresql/14/bin/postgres -V command as shown below.

root@localhost:~# /usr/lib/postgresql/14/bin/postgres -V
postgres (PostgreSQL) 14.0 (Ubuntu 14.0-1.pgdg20.04+1)

Step 8: Switch to psql prompt

PostgreSQL prohibits running any psql command through root user so in case you are currently logged in with root account, you need to switch it to a non-privileged account. You can switch to postgres user by running su - postgres command and then run psql command to start the postgres prompt as shown below.

root@localhost:~# su - postgres
postgres@localhost:~$ psql
psql (14.0 (Ubuntu 14.0-1.pgdg20.04+1), server 12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.
postgres=#

Step 9: Show List of Databases

You can check the current list of databases by using \l command in the PostgreSQL prompt as shown below.

postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | en_IN | en_IN |
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

Step 10: Check Your Database Connection

You can also check the current database connection details by using \conninfo command in the PostgreSQL prompt.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"

Step 11: Create a Database

When it comes to database creation, you need to use create database <db_name> syntax. In this example, we are creating a sample database by using CREATE DATABASE sample command and then checking the list by using \l command.

postgres=# CREATE DATABASE sample;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | en_IN | en_IN |
sample | postgres | UTF8 | en_IN | en_IN |
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

Step 12: Uninstall PostgreSQL Database

Once you are done with the Database and you don't need it any longer then you can uninstall PostgreSQL Database by using apt-get -y remove postgresql command as shown below.

root@localhost:~# apt-get -y remove postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libegl1-mesa libxcb-xinerama0 libxcb-xtest0
Use 'apt autoremove' to remove them.
The following packages will be REMOVED:
postgresql
0 upgraded, 0 newly installed, 1 to remove and 3 not upgraded.
After this operation, 70.7 kB disk space will be freed.
(Reading database ... 203677 files and directories currently installed.)
Removing postgresql (14+231.pgdg20.04+1) ...

Cyberithub