blob: 4ac13a2738123fda3acefa70cb220bf32e4865ad (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
# 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>
|