无法通过Rails连接Heroku上已有的PostgreSQL数据库

Hey there, let's tackle this problem from two angles—first getting your database migration working on Heroku, then sorting out potential issues in your controller code that might tie into database connectivity.

1. Fixing the PG::ConnectionBad Error During heroku run rake db:migrate #

This timeout error usually boils down to misconfigured Heroku Postgres settings, missing credentials, or network hiccups. Here's what to check step by step:

• Confirm Heroku Postgres is attached to your app
  First, run this command to see if your app has the Postgres add-on enabled:

  heroku addons
  

  If you don't see heroku-postgresql in the list, add the free hobby tier with:

  heroku addons:create heroku-postgresql:hobby-dev
  

• Validate your database config vars
  Heroku automatically sets a DATABASE_URL environment variable for your Postgres instance. Verify it exists with:

  heroku config:get DATABASE_URL
  

  If it's missing, restart your app to trigger Heroku's auto-config:

  heroku restart
  

  Also, make sure your config/database.yml for production relies on this variable (don't hardcode IPs/ports—Heroku rotates these):

  production:
    url: <%= ENV['DATABASE_URL'] %>
  

• Rule out network or timeout quirks
  Sometimes local network restrictions block connections to Heroku's Postgres. Try switching to a different network (like a mobile hotspot) or run the migration with the --no-tty flag to bypass terminal-related timeouts:

  heroku run --no-tty rake db:migrate
  

  If you manually set an IP whitelist for your Postgres instance, double-check that Heroku's dyno IPs are allowed (or temporarily disable the whitelist to test).

• Check your Postgres instance status
  Head to your Heroku Dashboard, navigate to the Postgres add-on for your app, and confirm it's not in maintenance mode or experiencing outages. If it looks stuck, restart the instance with:

  heroku pg:restart DATABASE
  

2. Potential Pitfalls in Your Controller Code #

Your index method uses raw SQL with ActiveRecord::Base.connection.execute, which could cause issues on Heroku if not configured properly:

• Schema name mismatch
  Heroku Postgres uses public as the default schema, but your SQL references api.customer. Confirm the api schema exists on your Heroku instance by running:

  heroku run rails dbconsole
  

  Then in the Postgres shell, type \dn to list all schemas. If api doesn't exist, create it with:

  CREATE SCHEMA api;
  

  Or adjust your SQL to use public.customer if that's where your table lives.

• Avoid SQL injection risks
  While your current query doesn't use user input, always use parameterized queries if you add dynamic values later to prevent SQL injection:

  # Safe parameterized example
  sql = "SELECT * FROM api.customer WHERE id = $1"
  @examples = ActiveRecord::Base.connection.execute(sql, [params[:id]])
  

• Prefer ActiveRecord models over raw SQL
  If you have a model for your customer table (like Api::Customer), use Rails' built-in query interface—it's safer, more maintainable, and handles schema/connection details automatically:

  def index
    @examples = Api::Customer.all
    render json: @examples
  end
  

内容的提问来源于stack exchange，提问作者zhu chen