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

          Rails 4 Cheat Sheet
          PostgreSQL Cheat Sheet