One of the things making your Heroku hosted database, certainly from a remote box, or a console difficult is the need to get an application running with the Heroku gems. This means also means your system admin team needs to be concerned with Gems too. Here are the steps to gain access to your Heroku data base with a psql console to execute commands.

1) We do need application access and the gem to do this.

➜  words git:(master) heroku config
=== words Config Vars
AWS_KEY:                     keys
AWS_SECRET:                  secrets
DATABASE_URL:                postgres://

The line of interest is the DATABASE_URL

2) Now construct a request like this.

$ psql "user=foo password=baz port=5432 dbname=foochickenheads sslmode=require"

With this setup you can access many of the postgres programs you may need not currently supported by heroku pg:commands.

3) We can also use this setup to gain Postgres access with commands not available through $ heroku pg. This next example looks at pg_dump.

$ pg_dump -f 'users' "user=foo password=baz port=5432 dbname=foochickenheads sslmode=require" > ~/dumps/users.sql 


Leave a Reply

Screen Shot 2013-12-08 at 9.49.35 PM

I have helped a dozen companies build and optimize their engineering teams to deliver great products.

Let’s work together and I can align your business needs with your technology initiatives.