Show Menu
Cheatography

PostgreSQL interactive terminal commands Cheat Sheet by

Frequently used commands with PostgreSQL

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
       

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
 

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