PostgreSQL Frequently Using Commands

List, Add, Drop Database

List databases

postgres=# \d

Add database

postgres=# createdb testdb

or

postgres=# create database testdb with owner facar;

Drop database

postgres=# dropdb testdb

List, Add, Drop, Grant User and Schema

List Users

postgres=# select * from pg_user;

Add User

postgres=# create user facar with encrypted password ‘password’;

or

postgres=# createuser (interactive user create)

Change User Password

postgres=# \password facar
postgres=# alter user facar with encrypted password ‘newpassword’;

Create Schema

postgres=# create schema schfacar;
postgres=# grant usage on schema schfacar to facar;
postgres=# grant all on schema schfacar to facar;

Add Search Path

postgres=# alter user facar set search_path=schfacar;

Grant Permissions to User

postgres=# alter user facar superuser;
postgres=# alter user facar createdb;
postgres=# alter user facar replication;
postgres=# alter user facar bypassrls;
postgres=# grant all privileges on database testdb to facar;

Revoke Permissions from User

postgres=# alter user facar nosuperuser;
postgres=# alter user facar nocreatedb;
postgres=# alter user facar noreplication;
postgres=# alter user facar nobypassrls;
postgres=# revoke all privileges on database testdb from facar;

Connect Database

[root@testdb ~] su – postgres
-bash-4.2$ psql
-bash-4.2$ psql testdb (connect testdb)
-bash-4.2$ psql -U facar testdb (connect testdb with facar user)
-bash-4.2$ psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)

[root@testdb ~]  psql
[root@testdb ~]  psql testdb (connect testdb)
[root@testdb ~]  psql -U facar testdb (connect testdb with facar user)
[root@testdb ~]  psql -U facar testdb -h “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 IP and 5432 port)

Note : If you want to connect database with different user from postgres, you have to add line pg_hba.conf like that

host         all            all               192.168.20.12/32          md5

Information Queries

postgres=# \conninfo (current connection information)
postgres=# \dt (tables of current user schema)
postgres=# \d (list of object of current user schema)
postgres=# \d tablename (columns of tablename)
postgres=# \df (list of functions)
postgres=# \df+ (list of functions with details)
postgres=# select version(); (detailed version of database)
postgres=# \? (psql commands list for help)
postgres=# \h (list of help options)
postgres=# \h CREATE USER (help of CREATE USER command)
postgres=# \timing (open timing to show query execution time)
postgres=# \c testdb (connect testdb)
postgres=# \c testdb facar (connect testdb with facar user)
postgres=# select pg_database_size(current_database()); (size of current database as byte)
postgres=# select pg_database_size(‘testdb’); (size of testdb database as byte)
postgres=# select pg_relation_size(‘tablename’); (size of table as byte)
postgres=# select usename, datname from pg_stat_activity; (who are connected to which database now)
postgres=# select pg_reload_conf(); (to reload configurations like service postgresql reload)