Show Menu
Cheatography
  1. Home >
  2. Programming >

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

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 \
 {database}
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 
--form­at=tar

pg_restore

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

Metadata

  • Languages:
  • Published: 15th June, 2015
  • Last Updated: 12th May, 2016

Favourited By

jeaninem71 fs8080 mythicalsunlight rossT mandarb cath2o

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

          PostgreSQL 101 for DBAs/sysadmins Cheat Sheet
          Postgresql settings/config quick reference Cheat Sheet