This is a cheat sheet command page for everything related to PostgreSQL Database Server.
List all existing database :
postgres=# \l
or
postgres=# \list
List all roles with the privileges :
postgres=# \du
List all role names :
postgres=# SELECT rolname FROM pg_roles;
Quit from PostgreSQL Command Console :
postgres=# \q
or
postgres=# \quit
Create Database :
postgres=# create database db_name
Drop Database :
postgres=# drop database db_name
Restore Database from a plain file backup database :
psql -Upostgres_username db_name < db_dump_file_location_and_name psql -Upostgres_username db_name < db_dump_file_location_and_name.dump
Restore Database from a PostgreSQL custom-format dump file backup database :
pg_restore -Upostgres_username -d db_name db_dump_file_location_and_name.pgdump
Select and Use Database :
postgres=# \connect db_name
or
postgres=# \c db_name
Show All Tables in a Database :
dbname=# \d
or
dbname=# \dt
Create User in PostgreSQL Database Server
postgres=# CREATE USER username WITH PASSWORD 'password'
Connect to a database
postgres=# \c database_name
How to Add Column of a Table in PostgreSQL Database
postgres=# alter table schema_name.table_name add column column_name column_type;
Grant All Privileges to a specific schema to a specific user
postgres=# grant all privileges on schema schema_name to user_name;
Grant All Privileges to All Tables in a Certain Schema in PostgreSQL Database
postgres=# grant all privileges on all tables on schema schema_name to user_name;/pre>
Add or Change Role Attribute to User in PostgreSQL Database
postgres=#alter user user_name [with] role_attribute_name;