Show Menu

PostgreSQL interactive terminal commands Cheat Sheet by

Frequently used commands with PostgreSQL

Connecting to PostgreSQL

open PostgreSQL intera­ctive terminal
psql -c {command}
execute a single command and exit
psql -d {dbata­base}
connect to a particular database
psql -U {role}
connect as a particular user
psql -l
list all databases
Executed from your system command line

psql commands

list all roles with their permis­sions
list all databases with their owners
show the current user and the database that you are connected to
\c {database}
connect to a particular database
list all tables in a connected database
\d {table}
list all columns and indexes for a specific table in a connected database
Executed inside
intera­ctive terminal

Roles and databases management

createuser {user}
create a new PostgreSQL role
dropuser {user}
drop an existing PostgreSQL role
createdb {database}
create a new database
dropdb {database}
drop an existing database
Executed from your system command line

Database backup

pg_dump {database} \ 
> dump.sql
dump a database to a text file
psql {database} \ 
< dump.sql
restore a database from a text file
pg_dump -Ft \
-f dump.tar \
dump a database to an archive file
pg_restore -d \
{database} dump.tar
restore a database from an archive file
pg_dumpall > dump.sql
dump all databases to a text file
pg_dump -Ft
is the same as


will drop database before restoring if already exists
will also create a database unless already exists


No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          Rails 4 Cheat Sheet
          PostgreSQL Cheat Sheet