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

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
       
 

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
          Acceso a bases de datos relacionales en Java. Cheat Sheet