How to install POSTGRESQL and PGADMIN on linux 7 (REDHAT,CENTOS,ORACLE LINUX)

 

INTRODUCTION: 

This doc will guide you through the process of installing Postgresql14, Pgadmin4 and also Creating users, databases, schemas

VERSION:
PostgreSQL14
PGAdmin-4

Verify Platform compatibility  using below link
https://www.enterprisedb.com/resources/platform-compatibility

Add LINUX 7 postgresql repository 

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

INSTALL POSTGRESQL14 

Yum install postgresql14-server

Create default databases:

Creating a database cluster consists of creating the directories in which the cluster data will live,  creating the postgres, template1, and template0 databases. The postgres database is a default database meant for use by users, utilities and third party applications. template1 and template0 are meant as source databases to be copied by later CREATE DATABASE commands. template0 should never be modified, but you can add objects to template1, which by default will be copied into databases created later.

Although initdb will attempt to create the specified data directory, it might not have permission if the parent directory of the desired data directory is root-owned. To initialize in such a setup, create an empty data directory as root, then use chown to assign ownership of that directory to the database user account, then su to become the database user to run initdb

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

Enable postgres service auto start

sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14

INSTALLING  PGADMIN4:

PGAdmin as a tool to help in your day to day database management activities with PostgreSQL
pgadmin can be install for WEB mode and Desktop mode .

— Install for both desktop and web modes.
sudo yum install pgadmin4

—Install for desktop mode only.
sudo yum install pgadmin4-desktop

— Install for web mode only.
sudo yum install pgadmin4-web

Here We are installing both mode

sudo yum install pgadmin4

 

Setting up For the web mode

Run the web setup script to configure the system to run in web mode, Need to provide login details which will be used to login in console

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

CONSOLE

http://yourhostip/pgadmin4

 

CREATING DATABASE AND USERS,SCHEMAS

Create User as role

CREATE USER test1 WITH PASSWORD ‘test1’;

Create database

CREATE DATABASE testdb;

Grant Privillages

This command grants all privileges on the specified database to the user

GRANT ALL PRIVILEGES ON DATABASE testdb TO test1;

Connect to the Database:

\c testdb;

Create schema and match the user

CREATE SCHEMA test1;

This command sets the default schema for the user

ALTER USER test1 SET search_path to test1;