how to Install Postgesql in ubuntu

Note :- For GUI Accessing Of Postgresql Database In Ubuntu Use pgadmin4  Click-Here-Io-Install

Step 1) Install Postgesql in ubuntu

$ sudo apt-get update
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update

$ sudo apt-get -y install postgresql


Step 2) Bydefault User for PostgreSQL

• By default, PostgresQL creates a user "postgres" with the role of "postgres". It also creates a system account with the same name "postgres".
• So for connect to the Postgres server, log in to your system as user postgres and connect the database.
  
• Postgresql by default  port number 5432 
  

$ sudo su - postgres
$ psql


Step 3) Check the postgresql service status

$ sudo service postgresql status

Create Postgres User, Database and Grant privileges/access.

1)  Switch to postgres user

sudo su postgres


2) Enter the the interactive terminal for working with Postgres

psql

• Update Password For Postgres :   ALTER USER postgres WITH PASSWORD '123456';

3) Create the database

postgres=# CREATE DATABASE database_name;



4) Create user and password.

postgres=# CREATE USER my_username WITH PASSWORD 'my_password';


5) Grant privileges on database to user

postgres=# GRANT ALL PRIVILEGES ON DATABASE database_name to my_username;

6) Give role attributes To ROLE

postgres=# ALTER USER demo_user WITH superuser;

 
                    PostgreSQL Basic Commands.

• Check All the USER/ROLE

postgres=# \du

• Drop ROLE/USER and Database

postgres=# DROP ROLE demo_user;
postgres=# Drop DATABASE  demo_db;

• Show All Database
  

postgres=# \l

 

• Enter Into Database

postgres=#  \c  database_name

• Show Tables 

postgres=# \dt

• Change a Role password:

postgres=# ALTER ROLE demo_user WITH PASSWORD 'hu8jmn3';

• Give role attributes To ROLE

postgres=# ALTER USER demo_user WITH superuser;

• Login through other postgres user

$ psql -h localhost -U demo_user -d demo_db

• Take Database Dump

$ pg_dump -h 127.0.0.1 -U demo_user -d demo_db > demo_db.sql

• Restore Database Dump

$ psql -h 127.0.0.1 -U demo_user -d demo_db < database.sql

• Below command show user name and db name

postgres=# \conninfo

• Below command show The Size Of Database

Enter Into DB  : \c database-name
postgres=# SELECT pg_size_pretty(pg_database_size(current_database()));

How To Remotly Login PostgreSQL Server

Step 1) Open The Port 5432 In Your Firewall Inbond Policy

Step 2) Edit the postgresql configuration file and change listen_address

$ vim /etc/postgresql/13/main/postgresql.conf

From listen_addresses='localhost'
To listen_addresses='*' or listen_addresses='Remote Server IP'

Step 3) Edit file /etc/postgresql/13/main/pg_hba.conf

vim /etc/postgresql/13/main/pg_hba.conf

From host all all 127.0.0.1/32 md5 

To host all all all md5
OR
To  host all all Remote_Server_IP md5

Step 4) restart postgresql service

sudo service postgresql restart