PostgreSQL or Postgres is an object-relational database management system (ORDBMS). The PostgreSQL tutorial is basically a list of the few basic commands which can help you manage the Postgres database with ease.

To check the pgsql root user password ( On cPanel Server )

cat /root/.pgpass | cut -d : -f 5

To Check the List of databases

$ psql -l

Or got to psql prompt and then execute the following command.

$ psql

postgres-# \l

To check List of roles ( Users and Privilages )

bash-4.1$ psql template1 -c ‘\du’

To create Database

sudo su
su postgres
createdb dbname

To create and assign user on the Database

su postgres
createuser uname
psql dbname
GRANT ALL PRIVILEGES ON DATABASE dbname TO uname;

To make any user super user

ALTER ROLE databse_user WITH SUPERUSER;

To Backup The Database

$ pg_dump databsename > dumpfilename.sql

To Restore The Database

$ psql -d databsename -f dumpfilename.sql

To Backup The Database in compressed format

$ pg_dump databsename | gzip -c > dumpfilename.sql.gz