CODEWITHSUNDEEP
databasepostgresqlherokuheroku-postgresheroku-cli
Stefan Roth
Stefan Roth

Reputation: 51

How to populate a heroku postgresql database with a sql file

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

Upvotes: 4

Views: 4754

Answers (1)

Yoni Rabinovitch
Yoni Rabinovitch

Reputation: 5261

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

Upvotes: 6

Related Questions