In this tutorial, we are going to show you how to install PostgreSQL 9.6 on Ubuntu 20.04. We will explain what PostgreSQL stands for and the steps about its installation, configuration, and use.

Postgres or PostgreSQL is an object-relational database management system that stores and retrieves information. This system is suitable for storing a big amount of data, it is open source and is very powerful and reliable.

The installation will take no more than 5 minutes. Let’s Start!


Prerequisites

  • Fresh install of Ubuntu 20.04
  • User privileges: root or non-root user with sudo privileges

Step 1. Update the System

Since this is a fresh install of Ubuntu 20.04 we need to update the system to its latest version and be sure the software is up to date.

sudo apt-get update -y && sudo apt-get upgrade -y

After executing the command it will take some time for the system to get the latest information.

Step 2. Import PostgreSQL 9.6 GPG public key

The signing GPG key can be added with the following command:

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

Step 3. Add PostgreSQL 9.6 repository

By default, Ubuntu 20.04 does not have the PostgreSQL repo and we need to add it manually so we can install it later.

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null

Once the repository is added update the system.

sudo apt-get update -y

Step 4. Install PostgreSQL 9.6

Now the GPG key and the repo are added and we are ready to install PostgreSQL 9.6 on our Ubuntu 20.04

sudo apt-get install postgresql-9.6

The command above will completely install the PostgreSQL database server and other required database modules and tools.

Step 5. Check and verify the Installation

First of all, you need to know that PostgreSQL is working on port 5432.

To check this you can execute the command below:

sudo netstat -tunlp | grep 5432

The output should be:

root@vps:~# sudo netstat -tunlp | grep 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      31266/postgres

We can see that the Postgres service is running on port 5432 successfully.

If you want to check and verify that the correct version is installed, please execute:

sudo psql --version

The output should be:

root@vps:~# sudo psql --version

psql (PostgreSQL) 9.6.22

Once everything is installed and prepared we can go to the next step, and that step is how to manage the PostgreSQL service.

Step 6. Manage PostgreSQL 9.6 service on Ubuntu 20.04

Here are some basic commands on how to manage the PostgreSQL services like status, starting, stopping, and restarting.

To check the status of the PostgreSQL service:

sudo service postgresql status

The output should be similar to the output below:

#sudo service postgresql status

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2021-08-08 12:59:50 UTC; 1min 5s ago
    Process: 793 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 793 (code=exited, status=0/SUCCESS)

 

Aug 08 12:59:50 vps systemd[1]: Starting PostgreSQL RDBMS...

Aug 08 12:59:50 vps systemd[1]: Finished PostgreSQL RDBMS.

To stop the PostgreSQL service.

sudo service postgresql stop

There will be no output but if you check the status of the service there will be minor changes from the previous output.

sudo service postgresql status
#sudo service postgresql status ● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Sun 2021-08-08 13:03:18 UTC; 24s ago
    Process: 793 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 793 (code=exited, status=0/SUCCESS)

Aug 08 12:59:50 vps systemd[1]: Starting PostgreSQL RDBMS...

Aug 08 12:59:50 vps systemd[1]: Finished PostgreSQL RDBMS.

Aug 08 13:03:18 test.vps systemd[1]: postgresql.service: Succeeded.

Aug 08 13:03:18 test.vps systemd[1]: Stopped PostgreSQL RDBMS.

To start the PostgreSQL service:

sudo service postgresql start

To restart the PostgreSQL service:

sudo service postgresql restart

If you want the PostgreSQL service to run on system boot you need to enable it and it can be done with the following command:

sudo service postgresql enable

Step 7. Configuration of PostgreSQL 9.6 and console

If you want to make any changes in the configuration file of Postgres services like accessibility, authentication, connectivity, or resource usage then you need to open the “/etc/postgresql/9.6/main/postgresql.conf” with some editor and make the necessary changes.

To access the console of the PostgreSQL service you need to log in as “postgres” user and execute the following commands:

sudo su - postgres

psql

The output should be:

#psql

psql (9.6.22)

Type "help" for help.

postgres=#

Now, you can list the default databases with the “\l” command and the output will be similar to the output below:

 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

 

To quit from the PostgreSQL shell just type “\q“.

Done. You successfully installed and managed the PostgreSQL 9.6 service on Ubuntu 20.04.