Steps for Installing Postgresql in Ubuntu 20.04 LTS

In this tutorial, we will learn the commands to install and set up a PostgreSQL database server on Ubuntu 20.04 LTS Linux.

What is PostgreSQL?

PostgreSQL is an object-relational database system (ORDBMS). Being an open-source database project it is freely available and usable without a license fee. PostgreSQL was originally developed at the University of California and uses the simple BSD license.

It is a very advanced database system in the open-source area. PostgreSQL supports most parts of the SQL2003 standard and has a large number of its own extensions.

The user can expand the system with self-defined data types, operators, and functions. Apart from the support of referential integrity and advanced transaction management, PostgreSQL offers definitions of triggers and rules to regulate access to database objects.

Installing Postgresql Database server in Ubuntu 20.04 LTS

The steps given here will work for Ubuntu 21.04/19.04/18.04 and other Linux OS based on Ubuntu as well.

1. Open Command Terminal

We need to access the command line to install Postgresql on Ubuntu, for which open ‘Terminal’ on your system either from the Application launcher or using the keyboard shortcut command– CTRL+ALT+T. CLI users are already on the terminal.

 

2. Run Ubuntu 20.04 system update

Now, the first thing we have to do is running of system update, to make sure all the existing system packages and repository cache are up to date.

sudo apt update

 

3. Add PostgreSQL GPG key & repository

The packages to set up PostgreSQL on Ubuntu 20.04 are available in its base repository, hence we don’t need to add any third-party repo at all. However, currently while writing this article the version available to install from the base repo was PostgreSQL 12, therefore if you want 14 or 13, then we need to add its repository.

Add GPG Key:

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

Add repository in Ubuntu 20.04:

Paste the below-given command block and hit the Enter key.

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

Run system update command:

sudo apt update

 

4. Command to Install PostgreSQL on Ubuntu 20.04

Now, we can install all the latest stable versions of the PostgreSQL Database on our Ubuntu 20.04 server or desktop.

Choose the version of the Database you want to install:

For Version 12

sudo apt install postgresql-12

For Verison 13

sudo apt install postgresql-13

For other versions such as 11 / 10 / 9.6, simply change the number in the above command with the one you want to install.

 

5. Check the Database Server Service Status

PostgreSQL service will automatically start after the installation, however, let’s confirm that is working fine without any errors, run:

sudo systemctl status postgresql

 

6. Secure PostgreSQL default Database

To make sure our PostgreSQL is secured with a strong password, set a password for its system user and then change the default database admin user account using below given commands-

Change user password

passwd postgres

Login using Postgres system account-

su - postgres

Now, change the Admin database password-

psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"

Note: Replace your-password in the above with a secure password that you want to set for the admin database user.

 

7. How to create a new database

Let’s say you want to create a database on PostgreSQL to use with some application, then here are the commands to follow-

Login with Postgre system user-

su - postgres

psql

Create a new user. Here we are creating a demo user protected by a password. Change them.

CREATE USER demo WITH PASSWORD 'password';

Create new PostgreSQL database-

CREATE DATABASE h2sdb;

Now, let’s Grant all rights or privileges on created database to the user:

GRANT ALL ON DATABASE h2sdb TO demo;

However, the owner of the Database still will be the default Admin user of PostgreSQL to change that you can use the command-

Syntax:

ALTER DATABASE database_name OWNER TO user-name;

Example:

ALTER DATABASE h2sdb OWNER TO demo;

To check and confirm whether the database has been created or not simply type

\l

You will get the following kind of output

 

8. Configure PostgreSQL to access remotely

By default, this database server will listen only to the local host, to access it remotely, we need to perform some changes in its configuration file. We can limit it to some particular IP address or open it for all.

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

If you are using some other version of the Database then change number 13 with that in the above command.

In the Configuration file, find – listen_addresses and first, remove the # given in front of it. After that replace localhost with * sign to allow all Ip-address to connect to the Database server, whereas to limit it to some particular Ip-addresses then type them instead of ‘*‘. Multiple addresses need to be separated with commas.

Save the file by pressing Ctrl+X, Y, and hit the Enter key.

Edit pg_hba.conf

sudo nano /etc/postgresql/13/main/pg_hba.conf

Change the host value as shown in the below screenshot.

host        all           all              0.0.0.0/0             md5

host        all            all             ::0/0                 md5

Save the file Ctrl+X, Y, and press the Enter key.

 

Restart the Database server to apply the changes:

sudo systemctl restart postgresql

Check whether it listening for all addresses or not, for that you can run:

sudo apt install net-tools

sudo netstat -antup | grep 5432

output:

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

Finally, open port ‘5432’ in the firewall:

sudo ufw allow 5432

 

9. Connect database locally or remotely

To access created database on localhost, you can use the following command syntax:

psql -h localhost -d DB-name -U DB-User

Replace DB-name and DB -User with the one you want to connect.

Well, to connect  PostgreSQL from some remote Linux system, you have to install this database client, first.

sudo apt install postgresql-client

Now, change the bold values in the below command to connect remote PostgreSQL running server.

psql -h server-ip-address -d DB-name -U DB-User

Example:

 

Ending note:

This was the quick way to set up and start working with PostgreSQL on Ubuntu 20.04 Linux servers or Desktop systems. To understand more about you can refer to the official documentation guide.

 

Other Articles:

• How to access remote MySQL database in local phpMyAdmin
• Install PostgreSQL and pgAdmin in AlmaLinux / Rocky 8
• Install MongoDB Compass GUI on Ubuntu 20.04 
• How to install & Configure Nessus on Ubuntu 20.04