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:

\l
Note: This is pgcli specific command

How to list all the tables in database:

use <database_name>

\dt

Note: This is pgcli specific command

How to list all users:

\du

How to find db sizes in PSQL:

SELECT
pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

How to create database in PSQL:

createdb -h <hostname> -U <username> <database_name>

or

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.

Leave a Reply

Your email address will not be published. Required fields are marked *