psql
Start psql connected to <db> as <user>.
$ psql -U <user> -d <db>
| description | command |
|---|---|
| quit session | \q or \! |
| help | \? |
| help with <sql> | \h <sql> |
| list all databases | \l |
| defined users | \du |
| defined tables | \dt |
| defined schemas | \dn |
| defined functions | \df |
| defined views | \dv |
| <relation>* definition | \d <relation> |
| show history | \s |
| save history to <file> | \s <file> |
| show all commands syntax | \h * |
| connect/change to <db> | \connect <db> |
* Where <relation> can be a table, index, view, or sequence.
CLI
Create <admin> user/role using <superuser>.
$ createuser -U <superuser> -P --createdb --createrole <admin>
Note: <admin> should own the <admin> db.
Create <db> for <owner> using <admin>.
$ createdb -U <admin> -O <owner> <db>
Use <admin> to create <new_user>. The -P flag will prompt for <new_user>’s password.
$ createuser -P -U <admin> <new_user>
Remove <user> using <admin>.
$ dropuser -U <admin> <user>
Remove <db> using <admin>.
$ dropdb -U <admin> <db>
Extract a database into a script file or other archive.
$ pg_dump -U <user> <db> > <output>.sql
Restore an archive created with pg_dump.
$ pg_restore -j <jobs> -U <user> -d <db> <dump>