postgresql.md

Postgresql

• Official site

• Official docs

• Tutorial

• Quick Reference

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.

1. Create a new role (/password)
2. Create a new database for app
3. 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>