Installation

Generate repository : https://www.postgresql.org/download/linux/redhat/
Install Repo (CentOS 7) : yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install Postgre (CentOS 7) : yum install postgresql12; yum install postgresql12-server
Check version : psql --version

Initialize DB and Start PostgreSQL services

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12
systemctl status postgresql-12

Allowing remote access to PostgreSQL server

find "postgresql.conf" and "pg_hba.conf" file
add this line at the end of postgresql.conf file : listen_addresses = '*'
add this line at the end of pg_hba.conf file : host all all 0.0.0.0/0 md5
restart postgresql : systemctl restart postgresql-12
open port 5432 TCP in firewalls.
set password for postgres user : sudo -u postgres -i && psql -c "ALTER USER postgres PASSWORD 'XXX';"

Basic Operation

Accessing psql monitor from local : sudo -u postgres -i
Accessing psql monitor remote : /usr/pgsql-12/bin/psql -h HOST -U postgres DBNAME
create database DBNAME;
create user USERNAME with encrypted password 'XXX';
alter user USERNAME with encrypted password 'xxx';
grant all privileges on database DBNAME to USERNAME;

Account management

List all user : \du+
Display privilege : \z
Creating user : create user USERNAME with password 'xxxx';
Crate user interactive : createuser --interactive USERNAME;
Shall the new role be a superuser? (y/n)
Shall the new role be allowed to create databases? (y/n)
Shall the new role be allowed to create more new roles? (y/n)

Process management

select * from pg_stat_activity;

DB Management

List existing db : psql -l or \l+
Accessing db : psql DBNAME
Creating db : createdb DBNAME
Delete db : dropdb DBNAME
Create table : CREATE TABLE identity ( name varchar(25), age int, sex varchar(2));
Insert data into table : INSERT INTO identity VALUES ( 'darin', 23, 'L');
Insert data from file : COPY TBName FROM '/tmp/identity.txt' (DELIMITER('|'));   
List tables : \dt+
Drop tables : drop table TBName