Easy usage of postgresql on heroku

Install

First of all, move to your workspace directory (of course which is include .git and heroku toolbelt) and command like below.

// Check available addons
% heroku addons:list

// Install postgres
% heroku addons:add heroku-postgresql

// Check addons in your repository
% heroku addons
=== hogehogehogehoge Configured Add-ons
heroku-postgresql:dev  HEROKU_POSTGRESQL_TEAL

This is really easy installation.
Next, getting to little by little details.

% heroku config    === hogehogehogehoge Config Vars
FACEBOOK_APP_ID:            000000000000000
FACEBOOK_SECRET:            000000000000000000
HEROKU_POSTGRESQL_TEAL_URL: postgres://userhoge:passwordfuga@servernamefunya:portgowa/dbnamechowa

Format of URL is like below.
HEROKU_POSTGRESQL_XXX_URL: postgres://[username]:[password]@[servername]:[port]/[dbname]

TIPS

** heroku-postgresql addon is serviceby Heroku Postgres, which is independent from heroku.**
So you can access heroku postgres which you made via heroku from outside of heroku.

Then you can make backup of heroku postgresql.

% heroku addons:add pgbackups
Adding pgbackups on powerful-castle-8258... done, v12 (free)
You can now use "pgbackups" to backup your databases or import an external backup.
Use `heroku addons:docs pgbackups` to view documentation.

…. OK, let's see the document.

% heroku addons:docs pgbackups
Opening pgbackups docs... done

ʕ̡̢̡ʘ̅͟͜͡ʘ̲̅ʔ̢̡̢

omg… just opened browser….
then,

% heroku addons:docs heroku-postgresql
Opening heroku-postgresql docs... done

(。-ω-)….

I know it. I know ……
Just opened the browser....

ok....

backup

Anyway, the backup is under heroku management.

% heroku pgbackups:capture
 !    Unknown database. Valid options are: HEROKU_POSTGRESQL_TEAL_URL

?? error has occurred
Does this mean you should add option "HEROKU_POSTGRESQL_TEAL_URL" ??

% heroku pgbackups:capture HEROKU_POSTGRESQL_TEAL_URL

HEROKU_POSTGRESQL_TEAL_URL  ----backup--->  b001

Capturing... done
Storing... done

+（0ﾟ・∀・） + ﾜｸﾃｶ +

% heroku pgbackups
ID    Backup Time          Status                          Size   Database
----  -------------------  ------------------------------  -----  --------------------------
b001  2013/04/04 00:49.18  Finished @ 2013/04/04 00:49.25  1.4KB  HEROKU_POSTGRESQL_TEAL_URL

ﾜｰｲヽ(ﾟ∀ﾟ)ﾒ(ﾟ∀ﾟ)ﾒ(ﾟ∀ﾟ)ﾉﾜｰｲ

restore

Next Try to restore pg on Heroku.
(Ordinary you should not easily restore DB. you should lose the data on DB. but this time, any data has no difference.)

OK LET'S RESTORE!!

% heroku pgbackups:restore HEROKU_POSTGRESQL_TEAL_URL b001HEROKU_POSTGRESQL_TEAL_URL  <---restore---  b001
                                            HEROKU_POSTGRESQL_TEAL_URL
                                            2013/04/04 00:49.18
                                            1.4KB


 !    WARNING: Destructive Action
 !    This command will affect the app: powerful-castle-8258
 !    To proceed, type "appidhoge" or re-run this command with --confirm appidhoge


> appidhoge


Retrieving... done
Restoring… done

OK!!ε=＼＿○ﾉ ｲﾔｯﾎｰｩ！

Next!!

export

Export heroku repository db data and import them into local db

% heroku pgbackups:url b001              "https://s3.amazonaws.com/hkpgbackups/[e-mail]/b001.dump?AWSAccessKeyId=fugafuga&Expires=hunyahunya&Signature=wachawacha"

access this url. this url will lose validity in 10 mins.

% curl -o heroku.dump 'https://s3.amazonaws.com/hkpgbackups/[e-mail]/b001.dump?AWSAccessKeyId=fugafuga&Expires=hunyahunya&Signature=wachawacha'  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1416  100  1416    0     0    988      0  0:00:01  0:00:01 --:--:--  1931
% ls
heroku.dump  <- this!!

To insert this downloaded data to local postgres

% pg_restore --verbose --clean --no-acl --no-owner -h localhost -U [username] -d [dbname] [backup file name]

reference) http://www.ownway.info/Ruby/index.php?heroku%2Fhow%2Fmanagement%2Fdatabase