As a DevOps and Sys Admin I get to deal with different databases server PSQL is one of the most commonly used database server. So I am sharing some of the most commonly used postgreSQL commands, feel free to comment if you would like to add any other.
I use pgcli in order to connect to postgres server, pgcli is a command line interface for MySQL and MariaDB with auto-completion and syntax highlighting.
NOTE: All the below commands are for Unix based system (Mac, Linux) and works if you install pgcli.
Login to mysql server:
pgcli -h <hostname> -U <username> <databasename>
You can save password value in the variable “PGPASSWORD” then it will let you auto login. If you like to input password then use
-W in the above command.
How to list all databases:
\lNote: This is pgcli specific command
How to list all the tables in database:
Note: This is pgcli specific command
How to list all users:
How to find db sizes in PSQL:
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
How to create database in PSQL:
createdb -h <hostname> -U <username> <database_name>
CREATE DATABASE <database_name>
How to make PSQL database backup/dump:
pg_dump -h <hostname> -U <username> -d <database> -v --file='location/filename.sql'
How to restore PSQL dump:
psql -h <hostname> -U <username> -d <database> --file='location/filename.sql'
Note: Before you restore PSQL database, you must create database manually then restore from backup file.