How To Install PostgreSQL On Ubuntu 20.04

PostgreSQL (aka Postgres) is an open-source object-relational database management system (ORDBMS), developed by PostgreSQL Global Development Group, a group of individual volunteers and corporate companies.

PostgreSQL is available for Linux, Microsoft Windows, FreeBSD, OpenBSD, Solaris, and Mac OS.

Here, we will see how to install PostgreSQL on Ubuntu 20.04.

Add PostgreSQL repository

PostgreSQL is available from both Ubuntu (sudo apt update && sudo apt install -y postgresql) and PostgreSQL’s repository.

PostgreSQL’s repository packages are fresher than those available in the Ubuntu repository. So, here we will install PostgreSQL from the PostgreSQL’s repository.

Import the PostgreSQL’s signing key.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -

Add the PostgreSQL repository information to postgresql.list file.

echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list

Install PostgreSQL

Execute the below command to update the repository index.

sudo apt update

Install the PostgreSQL package using the apt command.

### PostgreSQL 12 ###

sudo apt install -y postgresql-12

### PostgreSQL 11 ###

sudo apt install -y postgresql-11

By default, the PostgreSQL service is started automatically after the installation.

To check the status of PostgreSQL service, run:

Replace status with start, stop, restart to star, stop, and restart PostgreSQL service respectively.

sudo systemctl status postgresql

Output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2020-05-05 06:02:21 EDT; 4min 25s ago
   Main PID: 3603 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2319)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

May 05 06:02:21 ubun2004 systemd[1]: Starting PostgreSQL RDBMS...
May 05 06:02:21 ubun2004 systemd[1]: Finished PostgreSQL RDBMS.

Configure PostgreSQL Server

PostgreSQL server listens on localhost (127.0.0.1) by default. Sometimes, we need to configure PostgreSQL to listen on all adaptors or system IP address to allow applications hosted on external machines to connect to the database

### PostgreSQL 12 ###

sudo nano /etc/postgresql/12/main/postgresql.conf

### PostgreSQL 11 ###

sudo nano /etc/postgresql/11/main/postgresql.conf

Set the listen_addresses to * or <IPAddress>.

listen_addresses = '*'

Restart the PostgreSQL service.

sudo systemctl restart postgresql

Confirm that the PostgreSQL listening on port 5432 using the netstat command.

sudo netstat -antup | grep 5432

Output:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      6172/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      6172/postgres

READ: netstat command not found on Ubuntu 20.04 – Quick Fix

Access PostgreSQL

To manage the database, you need to log in as the postgres user.

sudo su -l postgres

Use the psql command to enter into the interactive shell for working with the PostgreSQL database.

psql

Output:

[email protected]:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=#

Secure PostgreSQL database

We will now set a password for postgres (Unix User) as well as postgres (DB administrator).

Set password for Unix user (postgres)

sudo passwd postgres

Set password for DB administrator (postgres)

sudo su - postgres

psql

On psql shell, run below command to change the database administrator (postgres) password.

postgres=# \password

OR

postgres=# \password postgres

Output:

postgres=# \password postgres
Enter new password:
Enter it again:

PostgreSQL DB Commands

Create Database

To create a database:

postgres=# CREATE DATABASE test;

Create DB user

To creating a user:

postgres=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'password';

List Database users

Check whether the user has been created or not.

postgres=# \du

Output:

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

List Database

To list databases:

postgres=# \l

Output:

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
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

Login to Database

Log in with the user “raj”.

$ psql -h localhost -d test -U raj

Output:

psql (12.2 (Ubuntu 12.2-4))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

test=#

Delete User

To remove a user:

postgres=# DROP USER raj;

Delete Database

To remove a database:

postgres=# DROP DATABASE test;

Conclusion

That’s All. You have successfully installed PostgreSQL on Ubuntu 20.04.