How to Install PostgreSQL and pgAdmin4 on ubuntu 20.04.

PostgreSQL is a open-source relational database management system.We can store structured and unstructured data.It is used for data store and analytics applications.It provides an implementation of the SQL querying language.

How to Install PostgreSQL & pgAdmin on ubuntu

Update the System.

apt-get update

Install the packages.

apt-get install wget curl ca-certificates 

Add repository & key.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
&
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" 
|sudo tee  /etc/apt/sources.list.d/pgdg.list

Update the packages.

apt-get update

Install PostgreSQL 12.

apt-get install postgresql-12 postgresql-client-12

Check the status of PostgreSQL.

systemctl status postgresql 

Here is the command output.

● postgresql.service - PostgreSQL RDBMS
   Loaded:loaded(/lib/systemd/system/postgresql.service;enabled; vendor preset:enabled)
     Active: active (exited); 17s ago
   Main PID: 3945 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4706)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Login To PostgreSQL.

su - postgres 

Here is the command output.

[email protected]:~$

Change the Password of PostgreSQL.

psql -c "ALTER USER postgres WITH PASSWORD 'password_here';" 

Run the following command to get the postgress prompt.

psql 

Here is the command output.

psql (12.7 (Ubuntu 12.7-1.pgdg20.04+1))
Type "help" for help.
postgres=#

Check the connection details of PostgreSQL.

\conninfo

Here is the command output.

You are connected to database "postgres" as user "postgres" via socket
 in "/var/run/postgresql" at port "5432".

Create a Database.

CREATE DATABASE database-name;

Here is the command output.

CREATE DATABASE

Create a user.

CREATE USER user-name WITH ENCRYPTED PASSWORD 'password';

Here is the command output.

CREATE ROLE

Provide grant all privileges.

GRANT ALL PRIVILEGES ON DATABASE database-name to user-name;

Here is the command output.

GRANT

List the created databases.

\l

Here is the command output.

                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-----------+----------+----------+---------+---------+-------------------------
 db-name   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | user-name=CTc/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres

Restart the service.

systemctl restart postgresql 

Install pgAdmin

Update the System.

apt-get update

Install Repo & Key.

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add -
&
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal 
pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list' 

Update the packages.

apt-get update

Install pgAdmin.

apt-get install pgadmin4 

Run the following command.To set pgAdmin4 login credentials such as Email-id & password.

sudo /usr/pgadmin4/bin/setup-web.sh 

Here is the command output.

Setting up pgAdmin 4 in web mode on a Debian based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: 

Access pgAdmin Web-interface

http://server-ip/pgadmin4

Here is the output.

• Provide the user-name/Email-d & password.

• Click on Add New Server.

• At General,Provide New server-name
• Click on Connection.

• Provide Host-name/address.
• Provide username & password
• Click on Save.

• Now Postgres database server is added successfully to pgAdmin4.