Quick workaround for pushing your local database to heroku for windows machines.

heroku_pg:push_workaround.md

Workaround for pushing to heroku database on windows machince.

Context:

The command heroku pg:push essentially executes two commands under the hood. First, it executes pg_dump, which makes a backup file of a specific database. Then, it pipes that file into pg_restore, where it takes that dump file and uses it to restore another database. The way we usually use it is to push a local database to a deployed heroku database.

The problem is that on windows, the command breaks while trying to pipe in the backup file from pg_dump to pg_restore, so we need to do these two commands manually.

Solution:

First, follow the steps in the pre-work to add psql to your path. If it's not, then you can't use the commands pg_dump and pg_restore. Next, make sure you have a local database that you want to send to heroku. It must be filled with the tables that you want to use. Seeded data is optional, but can be helpful to test.

Make a dump file:

• After psql is added to your path, open your terminal and type psql. If you get prompted for a password then psql is working and we can move forward.
• Navigate to the project repo. This is where we are going to add our dump file. Note that you don't need to be in a specific repo, but the dump file will be added to where your PWD is currently at. Adding it to your project repo can be a good way to save it as well.
• Type in the command PGPASSWORD=1234 pg_dump -U postgres -F c -c -O book_app > out.sql, replacing values as needed.
  • PGPASSWORD is the password for the user who owns the DB. If there's no password, then leave this off althogether.
  • -U is a flag for --username. By default it's postgres but if for some reason your DB owner is different, use that user after this flag instead of postgres.
  • -F c is a shortcut for --format=c. c Stands for custom. We are making a custom formatted file here.
  • -c stands for --clean. It tells the pg_dump command how to tear down the database.
  • -O stands for --no-owner. It says to not output commands for setting ownership for the database.
  • book_app is the name of the local database you to eventually push to heroku.
  • Note the > after the command. This specifcally says we want to output this stuff into a file. Later when we do a restore we will use a < mean bring in.
  • out.sql can be named anything you want. I named it out.sql because it's the outputted file, and .sql because it's to do with sql

You may see some errors here, but if you check your directory you should see a new file called out.sql. If you do, then assume that it worked and try the next step.

Use the dump file to create your heroku database.

This step requires you to have a heroku app that's been setup with heroku postgres. You don't need to do anything with the CLI here as we will be doing all of this manually.

• First, navigate to your heroku app, click on resources, and click on the database to bring you to the database screen.
• Next, click on settings, and go to the database credentials.
• You should see a screen that looks like this.
  • Note that this has the hostname, database, username, password, port, etc... You will need most of these for the next command.
  • You will type in this command, but you will replace your specific pieces of data into this command:
  • PGUSER=mibaxbq PGPASSWORD=0bdf8bbc6930e026d pg_restore -h 'ec2-60-13.compute-1.amazonaws.com' -d d6n3tt6nc < out.sql
    • PGUSER & PGPASSWORD are the username and password from the credentials you got inside heroku.
    • -h stands for hostname.
    • -d stands for database. (The database from the creds.)
    • < Note that this must be there and in this direction.
    • out.sql This is referring to the out file we used earlier.

If you typed this in correctly you WILL see a couple errors pop up. Ignore them for now and go back to heroku and check your database. You should see that there is a table added to your database.

Note that you should only be doing this command to initially create ( and optionally seed) the deployed database. If there database already has this table and there is data in it then this will fail.

Docs:

Here are some docs specifically on the pg_dump and pg_restore commands:

• pg_dump
• pg_restore

@KHCode I am so glad you found this and that it worked for you! 😊

@michaeltreat and@KHCode THANK YOU SO MUCH. I've been struggling with this the entire day, Using Bash was the key. Thank you both! Now the data isn't showing up in the app, but at least it's in there!

@lenniecottrell Awesome! Glad this solution worked for you! 🎉

Been struggling with this for the past 8 hours. Thanks so much for your incredibly well-written solution!

@jreidmke Woohoo! No problem, happy that this is working for you! 👏

Thanks a ton!!!!

Best solution guy! Thanks!

This was really helpful. Thanks.

Since, it was written some time ago, some things may have been changed which I would like to point out in case if someone face the same issues as me:

1. Run this command pg_dump -U postgres -F c -c -O book_app > out.sql. It will automatically prompt you to enter password else it will throw an error.
2. For this to be able to run pg_restore -h ec2-60-13.compute-1.amazonaws.com -d d6n3tt6nc < out.sql. You have to run the following commands which is: SET PGUSER=YOURUSERNAME and SET PGPASSWORD=YOURPASSWORD.

This works for me. I am new to heroku so I may not have follow your instructions properly due to which I could have faced the issues but still would like to mention here so in case if someone faces the same issues as me, this may help that person.

And of course, please do not forget to edit values as required according to your app credentials.

This is indeed working in 2021, for some people who like to use the default cmd as me you would likely encounter the error:

pg_restore: error: connection to database "database_name" failed: FATAL:  password authentication failed for user "db_user "
FATAL:  no pg_hba.conf entry for host "136.158.8.50", user "db_user ", database "database_name", SSL off

Don't fret yet, it only means the default cmd being used is not using SSL in connecting to the host. As KHCode mentioned in 2017, it is recommended to use git bash in this situation since git bash would automatically have SSL built in to it.

This worked for me too, thank you.

This was really helpful. Thanks.

Thanks man.

Thank you very very much. Can anyone specify if the heroku database must be totally empty (reset)? or should it contain the empty tables? (after migrate?).

At line:1 char:87

• ... -`MYURL' -d MYCRED< out.sql

•                                                             ~
  

The '<' operator is reserved for future use.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorReco
rdException
+ FullyQualifiedErrorId : RedirectionNotSupported
How can I avoid this

@oledybedokken I'm not sure, I haven't seen that before. I found this online : https://stackoverflow.com/questions/2148746/the-operator-is-reserved-for-future-use

Are you using Powershell as your terminal? If so I would recommend using the Windows Subsystem for Linux on Windows and using the Ubuntu terminal, as this would bypass this error.

Thanks for the guide, however, I followed the steps and after restoring i got some errors telling me the tables does not exist, and going back to heroku no tables where created, please help me out I'm using windows.