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
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
sudoprivileges (recommended) or a root user access. If you’re using
sudofrom 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
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 using
top -u postgres
Managing PostgreSQL 9.6 Services on Ubuntu 20.04
postgres service status using command below.
sudo service postgresql status
We can also use
systemctl command to check PostgreSQL service status.
sudo systemctl status postgresql
Using both commands above you see
active (exited) on the
Active: line. This happens because systemd doesn’t know the status of each PostgreSQL cluster running on the instance. One instance / installation of PostgreSQL can run mutliple clusters od PostgreSQL.
We can check, list of PostgreSQL clusters running on our machine using the command below.
There is one cluster running on our machine. The cluster named
main running PostgreSQL 9.6. To check this cluster service status, we can use the command below.
sudo service [email protected] status
As an alternative to service, we can use systemctl to check this cluster status.
sudo systemctl status [email protected]
Stop PostgreSQL service using the command below.
sudo systemctl stop postgresql
Start PostgreSQL service using the command below.
sudo systemctl start postgresql
Restart PostgreSQL service using the command below.
sudo systemctl restart postgresql
Reload PostgreSQL service using the command below.
sudo systemctl reload postgresql
Connect to PostgreSQL using psql
Now let’s connect to the PostgreSQL 9.6 server using
psql client application.
By default, postgres create system user named
postgres. There is also role in postgresql database named
postgres wth superuser permission.
sudo su - postgres
After switching to postgres user, we can connect using
To list all databases in PostgreSQL we can use
To list all users in PostgreSQL we can use
To exit from
psql we can use
To exit from
postgres user type
Change postgres User Password
Now we’re on our own linux user. Let’s try to login to
$ psql -U postgres -W -h 127.0.0.1 Password for user postgres:
It prompts for a password. Since we haven’t set any password, let’s leave it empty. We get the error message below.
psql: fe_sendauth: no password supplied
Since we cannot login without providing password, we set new password for
postgres user. Change user to
postgres user like shown above and use psql to connect to PostreSQL.
After logging in, type
\password. Enter new password and confirmation password for user
When we login again to
127.0.0.1 and provide a password, we can log in successfully.
Uninstall PostgreSQL 9.6 from Ubuntu 20.04
In this section, we learn how to uninstall PostgreSQL 9.6 and its dependencies on Ubuntu 20.04 completely.
Please be careful when running steps in this section since this will stop your PostgreSQL database server.
You can use the command below to uninstall all PostgreSQL 9.6 packages, both server, and client.
sudo apt-get remove postgresql-9.6 \ postgresql-client-9.6 \ postgresql-client-common \ postgresql-common
Remove the PostgreSQL data directory using the command below.
rm -rf /var/lib/postgresql/
Remove PostgreSQL 9.6 configuration directories using the command below.
rm -rf /etc/postgresql rm -rf /etc/postgresql-common
PostgreSQL 9.6 References
You can follow the links below to learn more about PostgreSQL 9.6 from PostgreSQL official documentation.
In this tutorial, we learn how to install PostgreSQL 9.6 on ubuntu 20.04. We also learn managing PostgreSQL services, using tools like
top to check PostgreSQL service.
After that, we learn how to connect to PostgreSQL 9.6 server using psql client, changing
postgres superuser password, and in the end, we learn how to uninstall PostgreSQL 9.6. I hope this is useful for initiating your journey to PostgreSQL 9.6.