Heroku Postgress

Véase Heroku Postgress.

Heroku Postgres is the SQL database service run by Heroku that is provisioned and managed as an add-on.

Heroku Postgres is accessible from any language with a PostgreSQL driver including all languages and frameworks supported by Heroku: Java, Ruby, Python, Scala, Play, Node.js and Clojure.

[~/srcPLgrado/pegjscalc(master)]$ heroku addons
=== pegjspl0 Configured Add-ons
heroku-postgresql:hobby-dev  HEROKU_POSTGRESQL_BROWN

In addition to a variety of management commands available via the Heroku CLI, Heroku Postgres features a web dashboard, the ability to create dataclips and several additional services on top of a fully managed database service.

Provisioning the add-on

Many buildpacks (what compiles your application into a runnable entity on Heroku) automatically provision a Heroku Postgres instance for you.

Your language’s buildpack documentation will specify if any add-ons are automatically provisioned.

Additionally, you can use heroku addons to see if your application already has a database provisioned and what plan it is^58.2.

[~/srcPLgrado/pegjscalc(master)]$ heroku addons
=== pegjspl0 Configured Add-ons
heroku-postgresql:hobby-dev  HEROKU_POSTGRESQL_BROWN

If your application doesn’t yet have a database provisioned, or you wish to upgrade your existing database or create a master/slave setup, you can create a new database using the CLI.

Create new db

Heroku Postgres can be attached to a Heroku application via the CLI^58.3:

$ heroku addons:add heroku-postgresql:dev
Adding heroku-postgresql:dev to sushi... done, v69 (free)
Attached as HEROKU_POSTGRESQL_RED
Database has been created and is available

Once Heroku Postgres has been added a HEROKU_POSTGRESQL_COLOR_URL setting will be available in the app configuration and will contain the URL used to access the newly provisioned Heroku Postgres service.

This can be confirmed using the heroku config command.

[~/srcPLgrado/pegjscalc(master)]$ heroku config
=== pegjspl0 Config Vars
DATABASE_URL:                postgres://moiwgreelvvujc:GL3shXGOpURyWOPrS2G8qaxzUe@ec2-23-21-101-129.compute-1.amazonaws.com:5432/dat9smslrg6g0a
HEROKU_POSTGRESQL_BROWN_URL: postgres://moiwgreelvvujc:GL3shXGOpURyWOPrS2G8qaxzUe@ec2-23-21-101-129.compute-1.amazonaws.com:5432/dat9smslrg6g0a
LANG:                        en_US.UTF-8
PGBACKUPS_URL:               https://453643:cqz59jrxbbfcxj0fanhjfg0vz@pgbackups.herokuapp.com/client
RACK_ENV:                    production

Establish primary DB

Heroku recommends using the DATABASE_URL config var to store the location of your primary database.

[~/srcPLgrado/pegjscalc(master)]$ head main.rb 
require 'sinatra'
require "sinatra/reloader" if development?
require 'sinatra/flash'
require 'data_mapper'
require 'pp'

# full path!
DataMapper.setup(:default, 
                 ENV['DATABASE_URL'] || "sqlite3://#{Dir.pwd}/database.db" )

In single-database setups your new database will have already been assigned a HEROKU_POSTGRESQL_COLOR_URL config with the accompanying DATABASE_URL.

You may verify this via heroku config and verifying the value of both HEROKU_POSTGRESQL_COLOR_URL and DATABASE_URL which should match.

pg:info

To see all PostgreSQL databases provisioned by your application and the identifying characteristics of each (db size, status, number of tables, PG version, creation date etc…) use the heroku pg:info command.

[~/srcPLgrado/pegjscalc(master)]$ heroku pg:info
=== HEROKU_POSTGRESQL_BROWN_URL (DATABASE_URL)
Plan:        Hobby-dev
Status:      available
Connections: 0
PG Version:  9.3.3
Created:     2014-03-20 23:33 UTC
Data Size:   6.5 MB
Tables:      1
Rows:        4/10000 (In compliance)
Fork/Follow: Unsupported
Rollback:    Unsupported

To continuously monitor the status of your database, pass pg:info through the unix watch command:

[~/srcPLgrado/pegjscalc(master)]$ watch heroku pg:info
-bash: watch: no se encontró la orden
[~/srcPLgrado/pegjscalc(master)]$ brew install watch
[~/srcPLgrado/pegjscalc(master)]$ watch heroku pg:info
...

pg:psql

psql is the native PostgreSQL interactive terminal and is used to execute queries and issue commands to the connected database.

To establish a psql session with your remote database use heroku pg:psql. You must have PostgreSQL installed on your system to use heroku pg:psql.

[~/srcPLgrado/pegjscalc(master)]$ heroku pg:psql
---> Connecting to HEROKU_POSTGRESQL_BROWN_URL (DATABASE_URL)
psql (9.2.6, server 9.3.3)
WARNING: psql version 9.2, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

pegjspl0::BROWN=> \dt
               List of relations
 Schema |     Name     | Type  |     Owner      
--------+--------------+-------+----------------
 public | pl0_programs | table | moiwgreelvvujc
(1 row)

pegjspl0::BROWN=> 
pegjspl0::BROWN=> SELECT * FROM pl0_programs;
  name  |           source            
--------+-----------------------------
 3m2m1  |                     3-2-1\r+
        |           
 ap1tb  | a+1*b\r                    +
        |           
 test   |                     a+1*b\r+
        |           \r               +
        |           
 lolwut |                     3-2-1\r+
        |           
(4 rows)

If you have more than one database, specify the database to connect to as the first argument to the command (the database located at DATABASE_URL is used by default).

$ heroku pg:psql HEROKU_POSTGRESQL_GRAY
Connecting to HEROKU_POSTGRESQL_GRAY... done
...

pg:reset

To drop and recreate your database use pg:reset:

[~/srcPLgrado/pegjscalc(master)]$ heroku pg:reset DATABASE

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

> pegjspl0
Resetting HEROKU_POSTGRESQL_BROWN_URL (DATABASE_URL)... done

Es necesario a continuación rearrancar el servidor:

[~/srcPLgrado/pegjscalc(master)]$ heroku ps:restart
Restarting dynos... done

pg:pull

pg:pull can be used to pull remote data from a Heroku Postgres database to a database on your local machine. The command looks like this:

[~/srcPLgrado/pegjscalc(master)]$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
server starting

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

This command will create a new local database named mylocaldb and then pull data from database at DATABASE_URL from the app sushi.

In order to prevent accidental data overwrites and loss, the local database must not exist. You will be prompted to drop an already existing local database before proceeding.

pg:push

Like pull but in reverse, pg:push will push data from a local database into a remote Heroku Postgres database. The command looks like this:

$ heroku pg:push mylocaldb HEROKU_POSTGRESQL_MAGENTA --app sushi

This command will take the local database mylocaldb and push it to the database at DATABASE_URL on the app sushi. In order to prevent accidental data overwrites and loss, the remote database must be empty. You will be prompted to pg:reset an already a remote database that is not empty.