#16. postgresql useful commands

Remove commands

sudo apt-get --purge remove postgresql postgresql-*

dpkg -l | grep postgres  - list all postgres related packages

sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/

sudo deluser postgres

sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

pg_config --version 

psql --version 

Install commands 

sudo apt update && sudo apt upgrade

sudo apt install postgresql postgresql-contrib

sudo systemctl start postgresql.service

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

sudo -i -u postgres - switch over to postgres account

psql - access postgresql prompt

\q - exit from postgresql prompt

sudo -u postgres psql - open postgres prompt without switching accounts

create new role

sudo -i -u postgres

createuser --interactive

or with sudo command,

sudo -u postgres createuser --interactive

reset password of the role

sudo -u postgres psql

alter role aarya with password 'Pass@test9123'

create new db

sudo -i -u postgres

createdb aarya_test

or with sudo command,

sudo -u postgres createdb aarya

openning new role

sudo -i -u aarya

psql

or

 sudo -u aarya psql

 

  \conninfo - check connection info inside prompt

  

 sudo systemctl status postgresql

/etc/apt/sources.list.d/pgdg.list