How I can copy my local PostgreSQL database to Heroku for SpringBoot app

Question

I have deployed my SpringBoot app to Heroku. Now I would like to copy my local PostgreSQL to Heroku.

I have found some information on devcenter.heroku.com. However I don't understand enough about the using of file db.changelog-master.yaml.

Could anyone give me details about the simplest solutions to copy the database?

At this point I have leant to create tables on Heroku's Postgres through Java API and Flyway. I am using script with CREATE statements. However a Big question is how to INSERT the existing data. Of course it is possible to create such script by hands and include there all the data, but I'm sure that there are should be more elegant way. — Kirill
– Kirill, Commented Apr 7, 2017 at 15:09

RangerRanger · Accepted Answer · 2017-04-08 14:54:42Z

2

Create a valid dump of your local postgres database and host it somewhere publicly available. Now you will be able to restore this entire dataset (schema and records) with pg:backups:restore as shown here. The sole caveat here is that the target database must be completely empty for this to work. You can empty a Heroku postgres database with heroku pg:reset.

If you cannot take the approach listed above then you can run pg_restore directly from your local instance, provided your local version of Postgres is >= the target version of Postgres. This also applies to creating the dumpfile and is a requirement because pg utilities are not guaranteed to be forward compatible. Documentation for pg_restore is here.

answered Apr 8, 2017 at 14:54

RangerRanger

2,5132 gold badges20 silver badges38 bronze badges

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

4 Comments

Kirill Over a year ago

Thank you. Is it possible to give me an additional advice. I copy my Postgres dump to Dropbox and give it the access to All. However Heroku says that Backup '...' not found. What is the prefered way to host the dump publicly?

Kirill Over a year ago

So now I have uploaded my file to Amazon S3 and gave the public access. The link is s3.eu-west-2.amazonaws.com/kirillch/localizedb.sql. However Heroku again said that Backup '...' not found. I use the following command: heroku pg:backups:restore 's3.eu-west-2.amazonaws.com/kirillch/localizedb.sql' DATABASE_URL -a my_app_name

RangerRanger Over a year ago

What response do you get back when you curl the file?

Kirill Over a year ago

Response is: Backup 's3.eu-west-2.amazonaws.com/kirillch/localizedb.sql' not found for my_app

Collectives™ on Stack Overflow

How I can copy my local PostgreSQL database to Heroku for SpringBoot app

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related