heroku pg: pull not fetching tables from heroku database

Question

I'm trying to pull a heroku database to my local Windows computer by using heroku bash command

heroku pg:pull HEROKU_POSTGRESQL_COLOR mydatabase --app appname,

when I running above command I get the following error:

'env' is not recognized as an internal or external command, operable program or batch file.!

But local database 'mydatabase' is created, but without any tables.

My heroku app's database has a table in it, but it is not getting pulled to my local database.

Help me to solve it.

Astragalus

how you connect with heroku ? – Grantham

From CLI using $heroku login, I have logged into heroku. And pushed my app using $git push heroku master. Connected to heroku db using $heroku pg:psql and created a table from there. I can see the table i created from jackDB tool. But when I'm trying to pull table from heroku to my local database using heroku pg:pull HEROKU_POSTGRESQL_COLOR mydatabase --app appname, its showing error like "env' is not recognized as an internal or external command, operable program or batch file.!". A local database 'mydatabase' is created, but with out any tables. – Astragalus

oh . ok i usually take dump file. – Grantham

Any suggestions you have to solve the problem? – Astragalus

Answer 1

a couple of things:

1.When there is an error such as "'env' is not recognized as an internal or external command, operable program or batch file" it means that the system is trying to execute a command named env. This has nothing to do at all with setting up your environment variables.

Env is not a command in windows, but in unix. I understand that you have a windows machine though. What you can do is run "git bash". (You could get it by itself but it comes with Heroku's CLI).

This gives you a unix-like environment where the "env" command is supported, and then you can run the actual heroku pg:pull command.

2.If that still doesn't work, there is a workaround which works,without installing anything extra. Actually this is based on a ticket which I submitted to Heroku so I'm just going to quote their response:

"The pg:push command is just a wrapper around pg_dump and pg_restore commands. Due to the bug you encountered, it sounds like we should go ahead and do things manually. Run these using cmd.exe (The Command Prompt application you first reported the bug). First grab the connection string from your heroku application config vars.

heroku config:get DATABASE_URL

Then you want to pick out the username / hostname / databasename parts from the connection string, ie: postgres:// username : password @ hostname : port / databasename. Use those variables in the following command and paste in the password when prompted for one. This will dump the contents of your heroku database for a local file.

pg_dump --verbose -F c -Z 0 -U username -h hostname -p port databasename > heroku.dump

Next you will load this file into your local database. One thing that the CLI does before running this command is to check and make sure the target database is empty, because running this against a database with real data is something you want to avoid so be careful with pg_restore. When running this manually you run the risk of mangling your data without the CLI check, so you may want to manually verify that the target database is empty first.

pg_restore --verbose --no-acl --no-owner  -h localhost -p 5432 -d mydb2 < heroku.dump

I am sorry this is not a better experience, I hope this will help you make progress. We are in the process of rewriting our pg commands so that they work better on all platforms including windows, but there is no solid timeline for when this will be completed."

Nepotism

I did have to change the pg_hba.conf. Had to add the IP Address the error gave me along with username that was given in string when I ran heroku config:get DATABSE_URL. . .In other words the 1st time I tried, I got an error with an IP Address and the username. I added this in the pg_hbba.conf as another connection. – Tressa

Answer 2

For taking backup like dump file in heroku firstly you need the backups addon, installing..

$heroku addons:add pgbackups

Then running below command will give you dump file in the name of latest

$ heroku pgbackups:capture
$ curl -o latest.dump `heroku pgbackups:url`

or

 wget "`heroku pgbackups:url --app app-name`" -O backup.dump

Edited:(After chatting with user,)

Problem: 'env' is not recognized as an internal or external command, operable program or batch file.!

I suspected that one of the PATH variable to particular program is messed up. You can double click and check that in WINDOWS\system32 folder.

Ok so How to edit it:

1. My Computer > Advanced > Environment Variables
2. Then choose PATH and click edit button

Grantham

Okay Thanks. I will try this..But I need to push my db back to heroku also. But heroku pg:push mydatabase HEROKU_POSTGRESQL_COLOR --app appname is not working(devcenter.heroku.com/articles/heroku-postgresql). Same error like "env is not recognized as an internal or external command, operable program or batch file.!" is coming. You have any suggestion for how to push local database to heroku – Astragalus

I guess you are using windows ? – Grantham

are you using virtualenv? or what is env there ? – Grantham