2016-06-20-postgres.md 2.4 KB
description: > Manage PostgreSQL databases from the terminal.
title: PG Client Applications
Table of Contents
- TOC {:toc}
psql
Start psql connected to <db> as <user>.
$ psql -U <user> -d <db>
| description | command |
|--------------------------|-----------------|
| quit session | `\q` or `\!` |
| help | `\?` |
| help with \ | `\h ` |
| list all databases | `\l` |
| defined users | `\du` |
| defined tables | `\dt` |
| defined schemas | `\dn` |
| defined functions | `\df` |
| defined views | `\dv` |
| \* definition | `\d ` |
| show history | `\s` |
| save history to \ | `\s ` |
| show all commands syntax | `\h *` |
| connect/change to \ | `\connect ` |
* 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>
Resources
- Psql section{:rel="nofollow noreferrer noopener"} from PostgreSQL: Introduction & Concepts.{:rel="nofollow noreferrer noopener"}
- Psql Manual{:rel="nofollow noreferrer noopener"}
- PostgreSQL Client Applications{:rel="nofollow noreferrer noopener"}