Postgresql

Notes

Enter psql shell: [<docker>] psql -U <user> -W -d <database>
Connection info: # \c [<db>] [<user>]
List all databases: # \l
List all tables: # \d
List all users: # \du

Backing up postgres (use pg_dumpall for… all)

pg_dump -U <user> <database> | xz -4 > /tmp/postgres_dump_$(date +"%Y-%m-%d_%H_%M").sql.xz

Restoring from dump
```
xz -d < /tmp/postgres_dump_<date>.sql.xz | <docker exec> psql -U  <user> -d <database>
```
- If restoring a particularly big dump or throwing errors, docker cp it inside container and restore from within.
To run above commands inside docker container (denoted by <docker exec>)
```
docker exec -it [-u <user>] <postgres-container-name> <command>
```

Setup for new Apps

Assuming a postgres instance is already running and a new app is being setup that requires a database in it.

Create a new role (/password)
Create a new database for app
Restrict acess for the role to that database

# CREATE DATABASE <database>;
# CREATE USER <user> WITH ENCRYPTED PASSWORD '<pass>';
# ALTER DATABASE <database> OWNER TO <user>;
# GRANT ALL PRIVILEGES ON DATABASE <database> TO <user>;

Use this connection string for access: postgres://<user>:<pass>@<host>:<port>/<database>