How to populate a heroku postgresql database with a sql file

Question

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

Yoni Rabinovitch · Accepted Answer · 2018-01-10 08:19:10Z

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

answered Jan 10, 2018 at 8:19

Yoni Rabinovitch

5,2611 gold badge27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stefan Roth Over a year ago

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?

Yoni Rabinovitch Over a year ago

You may have errors in your SQL syntax. No way of knowing without seeing your file and the exact errors you get.

Stefan Roth Over a year ago

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?

Yoni Rabinovitch Over a year ago

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 stackoverflow.com/questions/2987433/….

Collectives™ on Stack Overflow

How to populate a heroku postgresql database with a sql file

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related