How-to

How to Install PostgreSQL on Ubuntu 20.04

What is PostgreSQL?

PostgreSQL or Postgres is one of the most popular RDBMS (Relational Database Management System). It’s free and open-source RDBMS emphasizing extensibility and technical standards compliance.

PostgreSQL is designed to handle a range of workloads. It is an advanced RDBMS with an enterprise-class grade, supports both SQL (relational) and JSON (non-relational) querying.

PostgreSQL can be used for simple applications to the data warehouse and web application with many concurrent users.

Changelog Related PostgreSQL on Ubuntu 20.04 FocalFossa
Updated to New version PostgreSQL 12.2 with imporved query performance, especially for large data sets.

What we will do?

In this tutorial, we will show you how to install and configure PostgreSQL on Ubuntu 20.04.

Prerequisites

For this tutorial, make sure you have got a Ubuntu 20.04 server with root privileges.

Below is the tutorial about the Ubuntu 20.04 server installation.

Install Ubuntu 20.04 Server

Before going any further, log in to your Ubuntu server and type the sudo command to get the root privileges on your system.

sudo su

Step 1 – Install PostgreSQL on Ubuntu 20.04

First, we will install PostgreSQL to the Ubuntu system. And we will use the PostgreSQL packages provided by the official Ubuntu repository, which is the latest version of PostgreSQL 20.

Install PostgreSQL using the apt command below.

apt install postgresql postgresql-contrib

Once the installation is complete, start the PostgreSQL service and add it to the system boot.

systemctl start postgresql
systemctl enable postgresql

And the PostgreSQL service has been started, check it using the following command.

systemctl status postgresql

Install PostgreSQL 12 on Ubuntu 20.04

As a result, the PostgreSQL is up and running on the Ubuntu 20.04. And by default, it’s running on the TCP port ‘5432‘, check the PostgreSQL port using netstat command below.

ss -plt4

Checking Port using ss command

As a result, you’ve successfully installed the PostgreSQL on Ubuntu 20.04 server.

Step 2 – Setup PostgreSQL Authentication on Ubuntu 20.04

The PostgreSQL database supports for multiple authentication methods, which is controlled by the configuration file ‘/etc/postgresql/12/main/pg_hba.conf’.

The most commonly used authentication methods for PostgreSQL are below.

  • Trust – This method allows users/roles to connect without any password, as long as the requirement is met in the configuration ‘pg_hba.conf’.
  • Password – This method will ask the user/role for its password when connecting to the PostgreSQL. supported options are md5, scram-sha-256, or password as plain-text.
  • Ident – This method works by obtaining the client operating system username. Only supports for TCP/IP connection and additional options ‘map’ for user mapping.
  • Peer – This method is similar to Ident, but only works on local connections.

In this step, you will learn how to set up the PostgreSQL authentication using the password authentication method, and you will use the ‘md5’ as a supported method.

To set up the authentication method, go to the ‘/etc/postgresql/12/main’ directory and edit the configuration ‘pg_hba.conf’.

cd /etc/postgresql/12/main/
vim pg_hba.conf

Change the authentication method for local connection to md5 as below.

# "local" is for Unix domain socket connections only
local   all             all                                     md5

Save and close.

Next, restart the PostgreSQL service using the command below.

systemctl restart postgresql

Setup Authentication MD5 PostgreSQL on Ubuntu 20.04

As a result, you’ve changed the authentication for local connection with the ‘md5’ as its Password authentication method.

Step 3 – Connect to PostgreSQL Shell

PostgreSQL comes with a default user called ‘postgres’, which can be used to log in to the PostgreSQL shell.

To log in to the PostgreSQL shell, run the command below.

sudo -i -u postgres psql

Once you’ve logged in, check the connection info using the following query.

\conninfo

Login to PostgreSQL Shell with psql command

As a result, you’ve connected to the default database ‘postgres‘ with the default user ‘postgres‘ via socket in the ‘/var/run/postgresql’ directory at port ‘5432‘.

Step 4 – Basic PostgreSQL Operation

In this step, you will learn about the basics of PostgreSQL operations, such as create a user/role, create a database, create a table, insert data, etc.

– Create User/Role

To create user/role with login permission, run the following query.

CREATE ROLE gahyeon WITH LOGIN ENCRYPTED PASSWORD 'gahyeonpasswd';

After that, check all users/roles on PostgreSQL.

\du

As a result, you’ve created a new role named ‘gahyeon‘ with permission to log in and connect to the database.

PostgreSQL create a new user

– Create Database and Grant Privileges

To create the database on the PostgreSQL, run the following query.

CREATE DATABASE mydatabase;

Next, grant the user ‘gahyeon‘ to access the database ‘mydatabase‘.

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO gahyeon;

Now check available databases on PostgreSQL.

\l

As a result, you’ve created a new database named ‘gahyeon‘ and give the user ‘gahyeon‘ permission to access the database.

Create a New Database PostgreSQL

– Connect to PostgreSQL Shell as Specific Role

To connect to the PostgreSQL shell using the specific user/role, log in to the postgres user using the following command.

su - postgres

Next, connect to the PostgreSQL shell as user ‘gahyeon‘, to the database ‘mydatabase‘ using the following command.

psql -U gahyeon -d mydatabase

Now type the password for user ‘gahyeon’. Once you’ve logged in, check the connection info.

\conninfo

As a result, you’ve connected to the PostgreSQL shell as a specific user ‘gahyeon‘ to the database ‘mydatabase‘.

Connect to Database as a User psql

– Create Table and Insert Data

To create the table, run the following queries.

CREATE TABLE account(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   created_on TIMESTAMP NOT NULL,
   last_login TIMESTAMP
);

After that, insert the data to the table ‘account’ using the queries below.

INSERT INTO account VALUES(1,'gahyeon','passgahyeon','[email protected]', '2019-09-01', '2019-12-10');

Next, check list tables on the database and dump all data from the table using the following query.

\dt
select * from account;

As a result, you’ve created a new table ‘account‘ and inserted new data into it.

Insert and Show Data PostgreSQL

And finally, you’ve successfully installed the PostgreSQL on Ubuntu 20.04 Server.

Write A Comment