Ubuntu 20.04 - Manage PostgreSQL 12

Result

This is result if postgresql successfuly installed.

Step by Step

1. update package list

sudo apt update

2. install required package

sudo apt install postgresql postgresql-contrib

3. Default role

By default, Postgres uses a concept called “roles” to handle authentication and authorization.

sudo -i -u postgres

3.1. Exit postgresql

q;

Service Check

check status service

sudo systemctl status postgresql

restart service

sudo systemctl restart postgresql

start service

sudo systemctl start postgresql

stop service

sudo systemctl stop postgresql

Creating New Role Step by Step

1. create role from default role

sudo -u postgres createuser --interactive

after that fill name, superuser and privilage

2. show list of role, login as default role then

du;

rename role name

ALTER ROLE old_name RENAME TO new_name;

Creating New Database Step by Step

create database

sudo -u role_name createdb db_name

u can fill db name same as role name.

Note : role_name should have privilage to create database;

Opening PostgreSQL customize role from prompt Step by Step :

add user

sudo adduser role_name

customize role

sudo -i -u role_name

grant role to database

GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_role_name;

change role password

ALTER ROLE your_role_name WITH PASSWORD 'your_password';

show information

conninfo;

change password

password;

then fill new password and reenter new password

Database management PostgreSQL Step by Step :

restore database

sudo -u role_name psql db_name < location_dump.sql

look version database, login as default role/customize role

select VERSION();

show database, login as default role/customize role

list

show table, login as default role/customize role

dt;

customize show table

dt+;

connect database, login as default role/customize role

c db_name;

Allow Remote Database management PostgreSQL Step by Step :

edit configuration

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

find this

#listen_addresses = 'localhost'

then replace to

#listen_addresses = '*'

edit configuration pga_hba

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

find

# IPv4 local connections: 

host   all            all             127.0.0.1/32           md5

replace to

# IPv4 local connections: 

host   all            all             0.0.0.0/0           md5

you can change for customize remote

# Deny remote connections for role3

host    all    role3    0.0.0.0/0    reject

allowing port postgresql in firewall

sudo ufw allow 5432/tcp

restart postgresql service

sudo systemctl restart postgresql