Table of Contents
In this article, I will take you through the steps to install PostgreSQL on Ubuntu 20.04 LTS but before that let me ask you a question. Do you know which is the world's most advanced open source relational database ? If your answer is PostgreSQL then you are absolutely correct. It is one of the most advanced, powerful and open-source object-relational database system with high reliability, robustness, and performance features. It provides variety of features that help developers and administrators deploy their data-backed applications. We will see the steps to Install PostgreSQL Server in below section. More on official documentation.
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) ...