Postgres cheatsheet

Postgres.md

Setup Postgres in Server

rpm -i http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm
yum -y install postgresql92-server postgresql92-contrib postgresql92-devel
yum -y install postgresql-devel # this is for gem pg

service postgresql-9.2 initdb
chkconfig postgresql-9.2 on
service postgresql-9.2 restart

# Create the user
sudo -u postgres psql
create user <username> with password '<password>';
create database myapp_production owner <username>;
ALTER ROLE <username> SUPERUSER; # Needed if your app needs to install extensions

# Change PG login
vi /var/lib/pgsql/9.2/data/pg_hba.conf

# Change the bottom lines from Ident to md5
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

# Restart the service
service postgresql-9.2 restart

Setting DB credentials in environment - 12 Factors app

• As Rails database.yml does not load the environment yet, it is very tricky to add environment variables
• Use the gem dotenv at https://github.com/bkeepers/dotenv
• Add gem 'dotenv-rails' to your Gemfile
• Add require "dotenv/capistrano" to your config/deploy.rb
• Create the file .env on the server /path/to/shared and add your login like below:

PGUSER=root
PGPASSWORD=123123

• Deploy your app again and the database.yml will be able to read ENV.

Database.yml

• Make sure you have setup your credentials for your DB in database.yml

production:
  adapter: postgresql
  database: depot_production
  host: localhost
  username: <%= ENV['PGUSER'] %>
  password: <%= ENV['PGPASSWORD'] %>

Bundle

# Set the pg configuration in bundle in order to install pg gem
bundle config build.pg --with-pg-config=/var/lib/pgsql/9.2/data/pg_hba.conf

Others

# Access locally
psql DATABASE_NAME

# To quit in psql console:
\q