如何解决本地Postgres数据库未同步至Heroku生产数据库的问题

Hey there! Let's figure out why your local Postgres database isn't syncing with your Heroku production instance—this is a super common gotcha, so let's break down the fixes step by step.

First off: Your local Postgres and Heroku's production Postgres are completely separate, independent databases. They don't sync automatically—you have to explicitly trigger structure or data transfers between them. That's probably the main thing tripping you up!

1. Database Structure (Tables/Columns) Isn't Syncing? #

If your local schema is updated but Heroku's isn't, you're likely missing a migration step:

• If you use a framework with migration tools (like Rails, Django, etc.), run the migration command on Heroku:

  # For Rails
  heroku run rails db:migrate
  # For Django
  heroku run python manage.py migrate
  

• If you made manual schema changes locally (without migrations), you'll need to run the same SQL commands on Heroku's database. Connect directly to it first:

  heroku pg:psql
  

  Then paste your SQL statements to update the schema.

2. Local Data Isn't Showing Up on Heroku? #

Data won't copy over on its own—you have to export from local and import to Heroku:

• Option 1: Export then import

  # Export local database to a SQL file
  pg_dump -d your_local_db_name -f local_data.sql
  # Import the file into Heroku's database
  heroku pg:psql < local_data.sql
  

• Option 2: Use Heroku's direct push command (faster)

  # Push your local DB directly to Heroku (replace your_local_db_name with your actual DB name)
  heroku pg:push your_local_db_name DATABASE_URL
  

  Note: DATABASE_URL is Heroku's default environment variable for your production DB—confirm it exists with heroku config:get DATABASE_URL.

3. Version Compatibility Issues #

If you're hitting errors during sync, check if your local Postgres version matches Heroku's:

• Check Heroku's Postgres version:

  heroku pg:info
  

  Look for the "Version" line.
• If your local version is way off (e.g., you're on 15 and Heroku is on 13), use Brew to switch to a matching version:

  # Install the matching version (replace 14 with your target version)
  brew install postgresql@14
  # Stop current service, link the new version, start it up
  brew services stop postgresql
  brew link --force postgresql@14
  brew services start postgresql@14
  

4. Double-Check Your App's Database Connection #

Make sure your deployed app is actually using Heroku's database, not a hardcoded local connection:

• Verify Heroku has the correct database URL set:

  heroku config
  

  You should see DATABASE_URL listed.
• Ensure your app's production config uses this environment variable instead of hardcoding local credentials. Most modern frameworks handle this automatically, but it's worth double-checking.

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