4

First of, I want to say, that I am not a DB expert and I have no experience with the heroku service.

I want to deploy a play framework application to the heroku service. And I need a database to do so. So I created a postgresql database with this command, since it's supported by heroku:

Users-MacBook-Air:~ user$ heroku addons:create heroku-postgresql -a name_of_app

And I got this as response

Creating heroku-postgresql on ⬢ benchmarkingsoccerclubs... free
Database has been created and is available
! This database is empty. If upgrading, you can transfer
! data from another database with pg:copy

So the DB is now existing but empty of course. For development I worked with a local H2 Database.

Now I would want to populate the DB on heroku using a sql file, since it's quite a lot of data. But I couldn't find how to do that. Is there a command for the heroku CLI, where I can hand over the sql file as an argument and it populates the database? The File basically consists of a few tables which get created and around 10000 Insert commands.

EDIT: I also have CSV files from all the tables. So if there is a way how I can populate the Postgres DB with those would be also great

S-Man
  • 22,521
  • 7
  • 40
  • 63
Stefan Roth
  • 51
  • 1
  • 5

1 Answers1

6

First, run the following to get your database's name 

heroku pg:info --app <name_of_app>

In the output, note the value of "Add-on", which should look something like this:

Add-on:                postgresql-angular-12345

Then, issue the following command:

heroku pg:psql <Add-on> --app <name_of_app> < my_sql_file.sql

For example (assuming your sql commands are in file test.sql):

heroku pg:psql postgresql-angular-12345 --app my_cool_app < test.sql
Yoni Rabinovitch
  • 5,171
  • 1
  • 23
  • 34
  • Yeah I did that. I now run into some errors when I run the sql file. I get an error for each ' char for ever CREATE TABLE or INSERT INTO. Any idea how to get around that? – Stefan Roth Jan 10 '18 at 08:41
  • You may have errors in your SQL syntax. No way of knowing without seeing your file and the exact errors you get. – Yoni Rabinovitch Jan 10 '18 at 08:51
  • There shouldn't be any syntax errors. And for sure not that much. I ran this file before in the h2-browser console and it worked just fine. I now have also the CSV files from the h2-database, is it possible to populate the Postgres database with those CSV files? – Stefan Roth Jan 10 '18 at 09:06
  • Can't really help without seeing your SQL file and the errors you are getting. Try first by passing in a very simple SQL file, say just with 1 Create Table. Once you have that working, you can move on to a more complicated SQL script. As for populating Postgres from CSV, see https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table. – Yoni Rabinovitch Jan 10 '18 at 10:34