Search code examples
postgresqlherokupgpsql

heroku pg:sql --app <app_name> tries to connect to wrong host

I just deployed my production application on heroku.

I can still access my staging database with no problems using:

heroku pg:psql --app staging-app

But when I try to do the same thing for production:

heroku pg:psql --app production-app

I get a connection timeout and an error saying that host xxx is not listening on port 5432.

When I go to the heroku website and get the settings for the production database, it shows that it's running on host yyy (i.e., not xxx). Can anyone tell me how to get the pg:psql command to connect to the correct host?

The web server seems to be connecting to the database with no problems, and if I run:

heroku run --app production-app rails console

then I can see records in the database via ActiveRecord.

BTW, the command:

heroku pg:info --app production-app

Gives these results:

=== HEROKU_POSTGRESQL_MAROON
Plan:        Dev
Status:      available
Connections: 0
PG Version:  9.1.5
Created:     2012-08-08 17:55 UTC
Data Size:   5.9 MB
Tables:      0
Rows:        0/10000 (In compliance)
Fork/Follow: Unavailable

=== SHARED_DATABASE (DATABASE_URL)
Data Size: 600k
Solution

  • Apparently the correct command is:

    heroku pg:psql HEROKU_POSTGRESQL_MAROON --app production-app

    So both the database name and the --app parameter are required (for some reason, all the examples of running this command I ran into did one or the other, but not both).

    I'm guessing that the database name is required even when the application is specified because of the presence of the SHARED_DATABASE database (this database is not present in my staging app).

    By the way, I found the answer to this problem when I realized that I was using the now deprecated heroku gem and switched to the heroku toolbelt, which instead of trying to connect to an invalid host and timing out after 60 seconds, actually prints a semi-useful message:

     !    Unknown database. Valid options are: HEROKU_POSTGRESQL_MAROON_URL, SHARED_DATABASE

    (I rate it semi-useful because you have to know enough to remove the _URL part from the database name.)

    UPDATE:

    In trying to deploy my application I discovered that things like rake db:setup were not working correctly because they were going to the shared database instead of the postgres database.

    I deprovisioned the shared database (see https://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres#3-deprovision-your-shared-database). After doing that I also had to manually set the DATABASE_URL to be the same as HEROKU_POSTGRESQL_MAROON_URL (the documentation seems to imply that that will happen automatically, but in my case it didn't).

    Now I can run the command

    heroku pg:psql --app production-app

    with no problems, and most importantly, my rake tasks operate on the correct database.

    Hope this helps somebody else.