heroku で DB(postgresql)を利用するときのメモ #PostgreSQL

add Postgresql on heroku

# mysql はクレカの登録が必要らしい
$ heroku addons:add cleardb:ignite
Adding cleardb:ignite on gentle-reaches-3456... failed
 !    Please verify your account to install this add-on plan (please enter a credit card)
 !    For more information, see https://devcenter.heroku.com/categories/billing
 !    Verify now at https://heroku.com/verify
heroku で DB (PostgreSQL) を利用するときのメモ

# PostgreSQL ならクレカ登録不要
$ heroku addons:add heroku-postgresql:dev
※ 右記エラーになる場合は……「Couldn't find either the add-on service or the add-on plan of "heroku-postgresql:dev".」
　　以下でOK。
$ heroku addons:add heroku-postgresql

Adding heroku-postgresql:dev on gentle-reaches-3456... done, v16 (free)
Attached as HEROKU_POSTGRESQL_PINK_URL
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pgbackups:restore.
Use `heroku addons:docs heroku-postgresql` to view documentation.

# addon で登録されているのを確認.
$ heroku addons
=== gentle-reaches-3456 Configured Add-ons
heroku-postgresql:dev  HEROKU_POSTGRESQL_PINK

# config にも追加されている
 heroku config
=== gentle-reaches-3456 Config Vars
DATABASE_URL:               postgres://ecnlppyfsbaacr:Fl9OQOTtNMDhJmkIgToK6i569p@ec...
HEROKU_POSTGRESQL_PINK_URL: postgres://ecnlppyfsbaacr:Fl9OQOTtNMDhJmkIgToK6i569p@ec...

# heroku のpostgresqlに接続する
$ heroku pg:psql
---> Connecting to HEROKU_POSTGRESQL_PINK_URL (DATABASE_URL)
psql (9.3.5, server 9.3.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

gentle-reaches-3456::PINK=>

local環境にPostgreSQL

$ brew install postgresql
$ initdb /usr/local/var/postgres -E utf8
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

# heroku上のpostgresのdbをlocalのpostgresqlにpull
$ heroku pg:pull HEROKU_POSTGRESQL_PINK_URL mylocaldb
$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mylocaldb | hiyuzawa | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | hiyuzawa | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 template0 | hiyuzawa | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/hiyuzawa          +
           |          |          |             |             | hiyuzawa=CTc/hiyuzawa
 template1 | hiyuzawa | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/hiyuzawa          +
           |          |          |             |             | hiyuzawa=CTc/hiyuzawa
(4 rows)

node での接続例

app.get('/db', function (request, response) {
  pg.connect(process.env.DATABASE_URL || "tcp://localhost:5432/mylocaldb", function(err, client, done) {

  });
})

こうしておくとlocalでの動作時はlocalのPostgresqlにデプロイ後はherokuのPostgresqlに接続する。