Postgres

Reference: http://blog.jasonmeridth.com/posts/postgresql-command-line-cheat-sheet/

See SQL Queries for query examples

---

Access the command line

$ psql postgres
postgres=# 
Exit command line
\q   # backslash q

---

List commands

List databases

\l  # backslash l

Connect to database

\c a_db_name

List roles (aka users)

\du

List tables in connected database

\dt

List columns in table in connected database

\d a_table_name

---

Create & Delete commands

Create role (aka user)

CREATE ROLE new_role_name WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

Create database

CREATE DATABASE new_database_name WITH OWNER a_role_name ENCODING 'UTF8';

Delete database

DROP DATABASE a_db_name;

---

Privileges & Access

Grant role (user) privileges

Ref: https://www.postgresql.org/docs/9.1/static/sql-grant.html

GRANT ALL PRIVILEGES ON DATABASE a_db_name TO a_role_name;

Change owners of a database

Ref: https://www.postgresql.org/docs/9.1/static/sql-alterdatabase.html

ALTER DATABASE a_db_name OWNER TO new_owner;

Change user to superuser

ALTER USER a_role_name WITH SUPERUSER;

---

Postgres Service

Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac

Connect to database

\c a_db_name

Install Postgres...

brew install postgresql

Connect to database

\c a_db_name

Start/Restart/Stop Postgres...

Prereq: Install Homebrew services (one time only)

brew tap homebrew/services

Running postgres service

brew services start postgresql
brew services restart postgresql
brew services stop postgresql

NOTE: Restarts automatically after computer restart.

Connect to database

\c a_db_name

Check version...

psql -V

---

Using with Rails...

Reference: https://launchschool.com/blog/how-to-install-postgresql-on-a-mac

Install postgres gem

rails new -d postgresql

OR

gem install pg -- --with-pg-config=/usr/local/bin/pg_config

OR

edit Gemfile and add gem

gem 'pg', '~> 0.18'

Typical database.yml configuration

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: myapp_dev
  username: your_username_for_postgres
  password: your_password_for_postgres

test:
  <<: *default
  database: myapp_test
  username: your_test_username_for_postgres
  password: your_test_password_for_postgres