PostgreSQL with Rails on OS X

postgres_rails_os_x

# Setting up and using PostgreSQL with Rails on OS X
# Allows you to use PostgreSQL instead of sqlite3, and have the same database type as Heroku

# Install postgres using homebrew
brew install postgres

# Follow directions provided by postgres installation
# Instructions for this part can be retrieved again using "brew info postgres"
# This creates a default user according to your system name 
# If you encounter an error similar to this, then check out these helpful links
# "creating template1 database in /usr/local/var/postgres/base/1 ... FATAL: could not create shared memory segment: Cannot allocate memory"
# https://github.com/mxcl/homebrew/issues/3162
# http://willbryant.net/software/mac_os_x/postgres_initdb_fatal_shared_memory_error_on_leopard
initdb /usr/local/var/postgres

# Auto Start / Shutdown
# Follow the directions provided by postgres installation or "brew info postgres"
mkdir -p ~/Library/LaunchAgents
cp /usr/local/Cellar/postgresql/....... (follow the directions provided by postgres)
launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

# Start Postgres (not really needed)
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

# Stop Postgres (not really needed)
pg_ctl -D /usr/local/var/postgres stop -s -m fast

# View Process List (see if Postgres is working)
ps auxwww | grep postgres

# Creating Users
# There is a default user of YOUR_NAME from OS X
# create a user in the terminal as follows
# select as a superuser to use it with rails!
createuser <user_name>
createuser johnny

# Rails and Postgres
# This customizes the database.yml file in the config folder
# Uses gem "pg" instead of "sqlite3"
# Bundle install should automatically install the 'pg' gem
rails new <app_name> -d postgresql

# Configure the database.yml to change the user to whatever you want
# Can be the same as the app name (good for production), but not necessary for development
development:
  adapter: postgresql
  encoding: unicode
  database: <app_name>_development
  pool: 5
  username: <username>
  password:

test:
  adapter: postgresql
  encoding: unicode
  database: <app_name>_test
  pool: 5
  username: <username>
  password:

# Creates the databases according to the database.yml file 
# Make sure the right users are created as superusers
rake db:create:all

# Migrate your model to the databases
rake db:migrate

# Connect to your rails development database
# Same as psql <app_name>_development
rails db

# Connecting to Databases
# When you initialize your db, three databases will exist already: "postgres", "template0", "template1"
# These serve as templates upon with other databases will be based on, and you can connect to them if you want
# http://www.postgresql.org/docs/8.1/static/manage-ag-templatedbs.html
# -U selects user 
# Without -U, it defaults to the owner of the database
psql -U <user_name> <db_name>
psql <db_name>
psql -U johnny appname_development
psql appname_development
psql postgres

# Using psql shell
# Use all your awesome SQL statements here!
SELECT * FROM pg_user; # shows all the database users
\? # postgres help
\h # mysql help
\h <command> # mysql help for a specific command
\dt # list tables
\d <table_name> # look at a specific table
\l # list all databases
\q # quit out of psql

# Creating Databases
# -O selects owner / -E selects encoding
# without -O, it creates a db with the default user as the owner
# Good to keep with rails convention and call db by appname_development, appname_test, and appname_production
# Not necessary with rails as you can use rake db:create:all (but manually it works too)
createdb -O <user_name> -E utf8 <db_name>
createdb -O johnny -E utf8 appname_development
createdb -O johnny -E utf8 appname_test

# Deleting Stuff
dropdb <db_name> # delete database
dropuser <user_name> # delete user

Sources: 
http://www.postgresql.org/docs/9.1/interactive/index.html
http://dazedthots.blogspot.com/2011/07/using-postgresql-as-your-test-db-for.html
http://www.funonrails.com/2011/03/getting-started-with-rails-3-postgres.html
http://blog.willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/
http://mrfrosti.com/2011/06/setup-postgresql-with-rails-on-linux/
http://railscasts.com/episodes/342-migrating-to-postgresql