Viewing database in Heroku

Question

I have a Django app on Heroku set up and the database is starting to collect data. To debug things more easily I would like to connect my development machine directly to the database. Is this possible to do with Heroku?

Answer 1

As Kirsten says, the shared database plans are not currently accessible from outside the Heroku platform (that's something reserved for the 'production' Postgres service). However, there are a few other options.

One of the main ones is to pull your data down and look at it locally. By using the Taps rubygem this is luckily very very simple:

$ heroku db:pull
...
Receiving schema
Receiving data
8 tables, 591 records
users:         100% |==============================================| Time: 00:00:00
pages:         100% |==============================================| Time: 00:00:00
comments:      100% |==============================================| Time: 00:00:00
tags:          100% |==============================================| Time: 00:00:00
Receiving indexes
Resetting sequences

There are a few other options available which can make this task lighter if you're using a large dataset:

# -c, --chunksize SIZE # specify the number of rows to send in each batch
# -d, --debug          # enable debugging output
# -e, --exclude TABLES # exclude the specified tables from the push
# -f, --filter REGEX   # only push certain tables
# -r, --resume FILE    # resume transfer described by a .dat file
# -t, --tables TABLES  # only push the specified tables

All of this can be seen in the heroku gem source.

Taps can also be used outside of the Heroku context. See the README for more information..

A second option, and one which is much more preferable with larger datasets is to use the Heroku pgbackups add-on. This will let you create a dump of your database, and then download the file locally to import against a clean DB. This is significantly quicker than Taps due to the way that Taps works.

To use is nice and simple:

$ heroku update

$ heroku addons:add pgbackups
Adding pgbackups to myapp... done

$ heroku pgbackups:capture

DATABASE_URL  ----backup--->  b003

Dump... 2.6MB, done
Upload... 2.6MB, done

$ heroku pgbackups
ID   | Backup Time         | Size    | Database             
-----+---------------------+---------+----------------------
b003 | 2010/10/22 15:16.01 |   2.6MB | SHARED_DATABASE_URL  
b004 | 2010/10/22 15:18.12 | 424.7MB | HEROKU_POSTGRESQL_URL

$ heroku pgbackups:url b004
"http://s3.amazonaws.com/hkpgbackups/app1234567@heroku.com/b004.dump?AWSAccessKeyId=ABCD1234&Expires=1289261668&Signature=3mMBeKISewgEUDT%2FL5mRz4EYS4M%3D"

That last URL can be downloaded and imported.

Answer 2

i have not tested but the database_url use to include whole configuration for database

for instance

DATABASE_URL => postgres://name:password@ec2-107-22-181-237.compute-1.amazonaws.com/kjrbnwxjoc

now mapping to database.yml

adapter: postgres database: kjrbnwxjoc pool: 5 timeout: 5000 username: name password: password host: ec2-107-22-181-237.compute-1.amazonaws.com

Hope this will help you until and and unless heroku is allowing to access their database remotely and hopefully not because if they allowed you to access db remotely then you can put any kind of load on their db. For debugging purpose you can use xeround to be configured as your database for heroku app and local app ... they do give a trial period.

Thanks

Answer 3

I dont think you can use the config for direct access. According to the heroku documentation here: http://devcenter.heroku.com/articles/database

Can I access my database from another app or host? Shared Database

No, connecting to your database from machines outside of Heroku is not supported. We recommend that you encapsulate data access in an API to manipulate it.

Dedicated Database

It’s possible to connect to our dedicated databases using our pg:ingress feature. Please see using the PG console for more information.