How to Install Postgresql on Ubuntu 20 04

Linux

Install and configure postgresql on ubuntu 20.04

PostgreSQL is a powerful, open source object-relational database system with many advanced features like reliable transactions and concurrency without read locks.Postgresql is well suited for large databases and has many advanced features.

This tutorial will guide you how to install postgresql in ubuntu.

Also Read -> Install PostgreSQL 11 on centos 7

Step 1: Install postgresql server

First we need to update the system software packages using apt

sudo apt update

Postgresql is available from Ubuntu’s default software repositories. so we use the following command to install it.

sudo apt install postgresql postgresql-contrib

By default, the PostgreSQL service is started automatically after the installation.

To check service is running or not, run the following command.

sudo systemctl status postgresql

output:

postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2020-09-22 14:37:12 UTC; 1s ago
    Process: 5185 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 5185 (code=exited, status=0/SUCCESS)
Sep 22 14:37:12 ubuntu20 systemd[1]: Starting PostgreSQL RDBMS...
Sep 22 14:37:12 ubuntu20 systemd[1]: Finished PostgreSQL RDBMS.

you can verify the version with below command using psql

sudo -u postgres psql -c "SELECT version();"

output:

PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit

Also Read -> How to Install Apache Cassandra on Ubuntu 20 04

Step 2: configure postgresql

PostgreSQL server listens on localhost (127.0.0.1) by default. we need to configure Postgres to listen on all adaptors or system IP address to allow applications hosted on external machines to connect to the database as shown below.

nano /etc/postgresql/12/main/postgresql.conf

Find the line and change as below.

listen_addresses = '*'

save the file and exit and restart postgresql service.

sudo systemctl restart postgresql
sudo systemctl status postgresql

We hope you are following "How to Install Postgresql on Ubuntu 20 04" step by step carefully. The remaining steps will help you to finish the upgrade process..

Step 3: configure postgresql client authentication

postgresql uses client authentication method to connect users and database from which hosts will be contolled by settings in auth config file.uses many types of client authentication methods including peer, ident, password, and md5.md5 is most secure and recommended method.

host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Now restart postgresql service as shown below.

sudo systemctl restart postgresql
sudo systemctl status postgresql

Also Read -> How to Install Graylog on Ubuntu 20 04

Step 4: Secure postgres

you can set passwords for postgres users for both system user (postgres) and postgres admin (postgres) user.

For system user (postgres)

sudo passwd postgres

For postgres admin user (postgres)

sudo su - postgres
psql
postgres=# \password
Enter new password:
Enter it again:

Step 5: create database

To create a new database, you need to access postgrs database shell. First we need to postgres system user account and run psql command.

root@ubuntu20:~# su - postgres
postgres@ubuntu20:~$ psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# create database lampblogsdb;
CREATE DATABASE
postgres=# CREATE USER lampblogsuser WITH PASSWORD 'P@ssw0rd';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE lampblogsdb to lampblogsuser;
GRANT
postgres=# \q
postgres@ubuntu20:~$ exit
logout

you can list database users and databases and connect to database with following commands by connecting psql shell.

postgres=# \du
postgres=# \l
postgres=# \c lampblogsdb

output:

root@ubuntu20:~# su - postgres
postgres@ubuntu20:~$ psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# \du
                                     List of roles
   Role name   |                         Attributes                         | Member of
---------------+------------------------------------------------------------+-----------
 lampblogsuser |                                                            | {}
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \l
                                     List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |     Access privileges
-------------+----------+----------+-------------+-------------+----------------------------
 lampblogsdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres              +
             |          |          |             |             | postgres=CTc/postgres     +
             |          |          |             |             | lampblogsuser=CTc/postgres
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres               +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres               +
             |          |          |             |             | postgres=CTc/postgres
(4 rows)
postgres=# \c lampblogsdb
You are now connected to database "lampblogsdb" as user "postgres".