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

Answer 2

I just faced the exact same problem and was successful in resolving this. Rather than use the single line 'heroku pg:push' command with / without username/password, I relied instead on 2-steps: Step-1: pg_dump Step-2: pg_restore

This as pointed out by https://stackoverflow.com/users/4051445/na-peters above as well as briefly hinted by Heroku at: https://devcenter.heroku.com/articles/heroku-postgresql#pg-psql

Key thing is the password to enter is NOT the password you'd use when accessing your local Postgresql database. Instead, the password is a 64-character string you will obtain from: heroku config:get DATABASE_URL -a

To extract it follow instructions by NA Peters above (it is the y string between : and @ in the postgres://xxxxxxx:yyyyyyyyyyyyyyyyyyyyyyyy@hhhhhh:5432/dddddd Expect it to work

Answer 3

For you Windows PowerShell folks out there struggling with the restore, try:

Get-Content -raw local.dump | pg_restore -F c --verbose --no-acl --no-owner -U <username> -h <hostname> -p 5432 -d <databasename>

Then paste the password in when prompted.

Answer 4

1. Run SET PGUSER=postgres. This is important as otherwise heroku will use a different user and your password will not work.
2. Run heroku pg:push localdb DATABASE_URL --app herokuapp
3. Enter your password for postgres when it prompts.