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 psql intera­ctive terminal

Roles and databases management

creat­euser {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_re­store -d \
{database} dump.tar
restore a database from an archive file
pg_du­mpall > dump.sql
dump all databases to a text file
pg_dump -Ft is the same as --for­mat­=tar
    -c will drop database before restoring if already exists
    -C will also create a database unless already exists

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi


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 Configuration Cheat Sheet