Heroku pg:push psql: FATAL: password authentication failed for user

Question

I know similar questions have been asked but none of the solutions have worked. I am trying to push my local db to my Heroku db, and I keep getting psql: FATAL: password authentication failed for user "windows username".

I am on windows, so I tried SET PGUSER=postgres SET PGPASSWORD=password

Then ran heroku pg:push localdb DATABASE_URL --app herokuapp

But am still getting this stupid password error. The thing is it still looks like it is using my windows user name and not postgres username.... how do I resolve this?

Answer 1

Thanks to Heroku support I was finally able to get this to work. So for Windows users, these are the steps:

First you want to dump your local database out to a dump file:

pg_dump --verbose -F c -Z 0 -U postgres -h localhost -p 5432 yourdbname > local.dump

Then you want to 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 One warning, running this against a production 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 -U username -h hostname -p 5432 -d databasename < local.dump

Then when prompted for a password, just paste in the password from the connection string