Show Menu

PostgreSQL interactive terminal commands Cheat Sheet by

Frequently used commands with PostgreSQL
postgres     postgresql     psql

Connecting to PostgreSQL

psql
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

\du
list all roles with their permis­sions
\l
list all databases with their owners
\c
show the current user and the database that you are connected to
\c {database}
connect to a particular database
\dt
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 \
{datab­ase}
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
pg_re­store
    -c will drop database before restoring if already exists
    -C will also create a database unless already exists

Download the PostgreSQL interactive terminal commands Cheat Sheet

1 Page
//media.cheatography.com/storage/thumb/squixy_postgresql-interactive-terminal-commands.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

 

Comments

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