How To Install PostgreSQL 10 on Ubuntu 18.04

In this tutorial we learn how to install PostgreSQL on Ubuntu 18.04. We will also learn how to manage postgresql service, check postgresql port and resource.
By Muhammad Panji |
Tags:
Categories:

Introduction

In this tutorial, we learn how to install PostgreSQL 10 on Ubuntu 18.04 (Bionic Beaver).

PostgreSQL or Postgres is an open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance.

PostgreSQL is ACID(Atomicity, Consistency, Isolation, Durability)-compliant and transactional.

PostgreSQL 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 10 was released on 5 October 2017. Enhancements in PostgreSQL 10 includes:

  • Logical replication using publish/subscribe
  • Declarative table partitioning
  • Improved query parallelism
  • Significant general performance improvements
  • Stronger password authentication based on SCRAM-SHA-256
  • Improved monitoring and control

Prerequisites

This tutorial assume you already have the following requirements:

  • A machine with fresh install of Ubuntu Server 18.04. You can use 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 or a root user access. If you’re usig root user remove sudo from each command on this tutorial.

Step 1 — Update Base Systems

Since this is a fresh install of Ubuntu Server 18.04, before we install PostgreSQL 10, 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 10.

Since Ubuntu 18.04 doesn’t have PostgreSQL 10 in its repository, we need to add oficial PostgreSQL repository so we can install PostgreSQL 10.

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 10 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 10 on Ubuntu 18.04

Use the command below to install PostgreSQL 10 on Ubuntu 18.04.


sudo apt-get install postgresql-10

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

Verify PostgreSQL 10 Installation on Ubuntu 18.04

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


dpkg -l | grep postgresql

Check PostgreSQL 10 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 10 resource usage like CPU and memory usingtop command.


top -u postgres

Managing PostgreSQL 10 Services on Ubuntu 18.04

Check 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.


pg_lsclusters

There is one cluster running on our machine. The cluster named main running PostgreSQL 10. To check this cluster service status, we can use the command below.


sudo service postgresql@10-main status

As an alternative to service, we can use systemctl to check this cluster status.


sudo systemctl status postgresql@10-main

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 10 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.

Switch to postgresql user.


sudo su - postgres

After switching to postgres user, we can connect using psql command.

To list all databases in PostgreSQL we can use \l command.

To list all users in PostgreSQL we can use \du command.

To exit from psql we can use \q command.

To exit from postgres user type exit or CTRL+D

Change postgres User Password

Now we’re on our own linux user. Let’s try to login to localhost or 127.0.0.1.


$ 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 postgres.

When we login again to localhost or 127.0.0.1 and provide a password, we can log in successfully.

Uninstall PostgreSQL 10 from Ubuntu 18.04

In this section, we learn how to uninstall PostgreSQL 10 and its dependencies on Ubuntu 18.04 completely.

Please be careful when running steps in this section since this stops your PostgreSQL database server.

Use the command below to uninstall all PostgreSQL 10 packages, both server, and client.


sudo apt-get remove postgresql-10 \
    postgresql-client-10 \
    postgresql-client-common \
    postgresql-common

Remove the PostgreSQL data directory using the command below.


rm -rf /var/lib/postgresql/

Remove PostgreSQL 10 configuration directories using the command below.


rm -rf /etc/postgresql
rm -rf /etc/postgresql-common

PostgreSQL 10 References

You can follow the links below to learn more about PostgreSQL 10 from PostgreSQL official documentation.

Conclusion

In this tutorial we learn the following:

  • how to install PostgreSQL 10 on ubuntu 18.04
  • Manage PostgreSQL services
  • using tools like netstat, ss, ps, and top
  • How to connect to PostgreSQL 10 server using psql client
  • changing postgres superuser password
  • and in the end, we learn how to uninstall PostgreSQL 10

I hope this is useful for initiating your journey to PostgreSQL 10 on Ubuntu 18.04.