List, Add, Drop Database
List databases
postgres=# \d
Add database
postgres=# createdb testdb
or
postgres=# create database testdb with owner facar;
Drop databasepostgres=# 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)