Push database to heroku: how to use heroku pg:push

Question

I want to push my local postgresql database to heroku, using heroku pg:push command. The command looks like this: heroku pg:push mylocaldb DATABASE --app sushi according to the heroku document: https://devcenter.heroku.com/articles/heroku-postgresql.

Here is my local database info:

Name: mysitedb
User: bill
Password: bill

The DATABASE_URL environment variable in my machine is set to: postgres://bill:bill@localhost/mysitedb.

My app's name is secure-gorge-4090. I tried heroku pg:push mysitedb DATABASE --app secure-gorge-4090. The output was:

 !    Remote database is not empty.
 !    Please create a new database, or use `heroku pg:reset`

I was surprised that I have put nothing into my DATABASE. But I still ran heroku pg:reset DATABASE to reset my DATABASE. After that, I tried heroku pg:push mysitedb DATABASE --app secure-gorge-4090 again but the output was still the same.

I tried heroku pg:push postgres://bill:bill@localhost:8000/mysitedb DATABASE --app secure-gorge-4090. The output was:

!    LOCAL_SOURCE_DATABASE is not a valid database name

I don't know how to use this command to move my local database to heroku. I need your help. Thanks!

Answer 1

I know this is a old discussion but I had the exact same problem. Though it's not quite as convenient, I managed to achieve this via pg:backups instead.

This is detailed pretty nicely on the heroku support site

Start by installing the free pgbackups addon:

heroku addons:add pgbackups

Then backup the database using your local pg_dump utility (included in PostGreSQL distro)

pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dmp

Then put that dump file somewhere URL-addressible (e.g. Dropbox) and run the heroku import(make sure it's double quotes for Windows):

heroku pg:backups:restore 'https://dropbox.com/dYAKjgzSTNVp4jzE/mydb.dmp' DATABASE_URL

Answer 2

Are you actually typing in the token DATABASE in your commands, or is that a placeholder you're using for this question? From the docs you linked to:

Like pull but in reverse, pg:push will push data from a local database into 
a remote Heroku Postgres database. The command looks like this:

$ heroku pg:push mylocaldb HEROKU_POSTGRESQL_MAGENTA --app sushi

This command will take the local database “mylocaldb” and push it to the 
database at DATABASE_URL on the app “sushi”. In order to prevent accidental 
data overwrites and loss, the remote database must be empty. You will be 
prompted to pg:reset an already a remote database that is not empty.

Usage of the PGUSER and PGPASSWORD for your local database is also supported
for pg:push, just like for the pg:pull commands.

When you do heroku config -a secure-gorge-4090, you should see an entry for HEROKU_POSTGRESQL_[SOME COLOR NAME]. Make sure you're using whatever that token is instead of DATABASE in your commands.

Since you have a username and password on your local database, you also need to do the part mentioned about PGUSER and PGPASSWORD. Here's the example from the pg:pull docs:

$ PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

So you should do something like:

$ PGUSER=bill PGPASSWORD=bill heroku pg:push mysitedb HEROKU_POSTGRESQL_[SOME COLOR] -a secure-gorge-4090

Answer 3

You need the following command

PGUSER=root PGPWD=root heroku pg:push (local database name) DATABASE_URL --app heroku (app name)

make sure that you have entered correct postgres username and password