How To Install PostgreSQL 9.6 on Ubuntu 20.04

Introduction

In this tutorial, we learn how to install PostgreSQL 9.6 on Ubuntu 20.04 (Focal Fossa).

PostgreSQL, or usually called Postgres, is an open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance. PostgreSQL is ACID-compliant and transactional. It is developed by PostgreSQL Global Development Group (PGDG) that consists of many companies and individual contributors. PostgreSQL released under the terms of PostgreSQL license.

PostgreSQL 9.6 was released on 29 September 2016. Major enhancements in PostgreSQL 9.6 includes:

• Scale up with parallel query
• Scale Out with Synchronous Replication and postgres_fdw
• Better Text Search with Phrases
• Smoother, Faster, and Easier to Use
• New system views and functions: pg_stat_wal_receiver, pg_visibility, pg_config, pg_blocking_pids, pg_notification_queue_usage
• Command progress reporting support
• Cascade support for installing extensions
• pg_basebackup concurrency and replication slot support
• Wait Event support
• View editing and crosstabs in psql
• User-defined expiration for old snapshots
• Index-only scans for partial indexes

⚠️ PostgreSQL 9.6 will reach end of life (EOL) on November 11, 2021.

You might want to use newer version of PostgreSQL like PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, or PostgreSQL 13.

Prerequisites

This tutorial assume you already satisfy the following requirements:

• A machine with fresh install of Ubuntu Server 20.04. It can be A virtual machine locally using Virtualbox, VMWare workstation or VMWare Fusion, KVM, Xen, or a virtual machine in the cloud like DigitalOcean or Linode.
• A non-root user with sudo privileges (recommended) or a root user access. If you’re using root user remove sudo from each command on this tutorial.

Step 1 — Update Base Systems

Since this is a fresh install of Ubuntu Server 20.04, before we install PostgreSQL 9.6, let’s update our system to the latest update.

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

After we upgrade our base system, now it’s time to install PostgreSQL 9.6. Since Ubuntu 20.04 doesn’t have PostgreSQL 9.6 in its repository, we need to add oficial PostgreSQL repository so we can install PostgreSQL 9.6.

Step 2 — Import Repository Signing Key

The PostgreSQL team is using GPG to sign downloadable packages from the PostgreSQL website. We add PostgreSQL public GPG key so apt can verify that packages downloaded from PostgreSQL repository are not tampered or corrupt.

Add PostgreSQL GPG public key using the command below.

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

You should get OK output for command above. If you got the different output, you need to fix the error first before continue to the next step.

Step 3 — Add PostgreSQL 9.6 repository

After adding PostgreSQL release keys, we create a new repository configuration for PostgreSQL using the command below.

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

Refresh apt metadata using the command below.

sudo apt-get update

Step 4 — Install PostgreSQL 9.6

We’re ready to install PostgreSQL 9.6. Use the command below to install PostgreSQL 9.6 on Ubuntu 20.04.

sudo apt-get install postgresql-9.6

Only 4 steps needed to install PostgreSQL 9.6 on Ubuntu 20.04. We learn how to check and verify and managing PostgreSQL 9.6 installation in the next few sections.

Verify PostgreSQL 9.6 Installation on Ubuntu 20.04

We can check PostgreSQL 9.6 packages that already installed using the command below.

dpkg -l | grep postgresql

Check PostgreSQL 9.6 Listening Ports

By default, PostgreSQL listens on port 5432. We can use netstat to check whether there is a process listening on port 5432.

sudo netstat -naptu | grep 5432

Another method is to check whether there is a process named postgres listening on a port.

sudo netstat -naptu | grep postgres

Alternative to netstat, We can use ss command below to check whether there is a process listen on PostgreSQL default port 5432.

sudo ss -atnp | grep 5432

We can also use ss to check whether there is a process named postgres listening on a port.

sudo ss -atp | grep postgres

The last output above shows there is also a connection to ephemeral UDP port from local. According to this thread on PostgreSQL mailing lists, this is PostgreSQL stats collector that use UDP to send and receive stats data locally.

Checking PostgreSQL Process

We can ue ps command to check what processes currently running with name contain postgres.

ps -aux | grep postgres

Check PostgreSQL 9.6 resource usage like CPU and memory usingtop command.

top -u postgres

Managing PostgreSQL 9.6 Services on Ubuntu 20.04

Check postgres service status using command below.

sudo service postgresql status

We can also use systemctl command to check postgres service status.

sudo systemctl status postgresql

To start postgres service, run command below.

sudo service postgresql start

To stop postgres service, run command below.

sudo service postgresql stop

To restart postgres service, run command below.

sudo service postgresql restart

Connect to PostgreSQL 9.6

When we install PostgreSQL, a user named postgres is created. We can connect to PostgreSQL using psql command.

sudo -u postgres psql

You should see psql prompt like below.

psql (9.6.22)
Type "help" for help.

postgres=#

To exit from psql prompt, type \q and press enter.

\q

Conclusion

In this tutorial, we’ve learned how to install PostgreSQL 9.6 on Ubuntu 20.04. We also learned how to verify the installation, managing the service, and connect to PostgreSQL using psql.