If you send to the Heroku 1.sql file generated for H2 , there is a high probability that they conflict, instead set the Heroku URL in application.conf as db.default.url and run it in dev mode on localhost first to generate valid Postres SQL. You will also need to comment on the settings of db.default.user and db.default.password , as the Heroku URL already contains this information.
If you do not know the current connection URL, you will find it:
https://postgres.heroku.com/ → YourDBs → db-name → Connection settings → JDBC URL
To connect with localhost (or another computer, not in Heroku space), you need to add this parameter to the URL:
&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
Thanks to this approach, you can test your application locally with a connection to a remote Heroku database (do not be surprised - this combination will be much slower than working with local app + local DB or Heroku app + Heroku DB ), in any case, testing is better than repeatedly push small corrections to the cloud.
You can also perform evolution from localhost - without clicking on the code in Heroku, and this is a very short approach if you have problems in your SQL.
In addition, it’s nice to turn off SQL logging in local detector mode and look for redundant SQL queries if you delete all unnecessary queries and are satisfied on localhost, after deployment in Heroku you will just be more satisfied :)
Reset
If you create the correct 1.sql file, than most likely you will need to reset your database containing an invalid structure (since I assume that this is not a problem yet, since you are simply moving from local to Heroku). First, find the database name for the current application (with bash, in the application folder):
heroku pg
And then reset it with (it will destroy all data, so first think about backing up if you don't want to lose your data!):
heroku pg:reset HEROKU_DATABASE_SOMEBASE