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
Step 1: Prerequisites
a) You should have a running
Ubuntu 20.04 LTS Server.
b) You should have
root access to run privileged commands.
c) You should have
apt-get utility installed in Your Server.
d) You should also have
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: Starting PostgreSQL RDBMS... Oct 24 01:06:49 localhost systemd: 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
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) ...