# The PostgreSQL DBMS

Default user, db: `postgres`

## `psql` CLI

    psql <database> <user>

### Meta commands

- `\c[onnect]`: connects to a database, new server (can specify dbname, username,
host, port, etc., use `-` to leave unspecfied)
- `\l[ist]`: list databases.
- `\dt`: list tables
  - There's a bunch of complicated `\d*` commands
- `\o <file>`: output query outputs to file. Empty files returns to outputting to
    CLI.
- `\pset format <format>`: sets output format. `aligned` is the default. Can use
  e.g. `csv` for outputting to files that can be easily opened.

## User management

Everything should be done as user `postgres`.

New user:

    createuser --pwprompt mypguser

Change password:

    # in `psql`
    ALTER ROLE user WITH PASSWORD 'xxx';

## New database

    sudo su postgres
    createdb -O mypguser mypgdatabase

    # delete db
    dropdb mypgdatabase

Or in `psql`:

    CREATE DATABASE <dbname>;

## Backup/dumping

    # Can supply password with PGPASSWORD env var
    pg_dump [-U username] [-h hostname] [-p port] [-t table_pattern] <database>