1

I'm trying to pg_dump certain schemas from my Heroku-hosted PSQL database into a local file.

Heroku provides me a DATABASE_URL in the form 

# postgres://username:password@host:port/database
postgres://abcde:wxyz@ec2-21-82-72-112.compute-1.amazonaws.com:5762/riza3dj029012

Based on the above I tried dumping some schemas - 

> pg_dump --username=abcde --host=ec2-21-82-72-112.compute-1.amazonaws.com --port=5762 --dbname=riza3dj029012 --create --schema=my_schema --password  > ~/pg_dump.dmp
> password: (enter password)

Is there any way to provide the password as a flag so I don't have to type it in manually? I want to automate this in a script.

I know --no-password exists, but not sure what use that is, because it just prevents prompting for a password and then authentication (obviously) fails.

Thanks!

user2490003
  • 10,706
  • 17
  • 79
  • 155
  • 2
    Did you try making a .pgpass file a la https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html or http://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump? – eabates Oct 26 '16 at 20:19
  • That did it for me, thanks. While it works, it's not ideal because I have to create a file with my production credentials on my local machine. If the script ever fails the `pgpass` file doesn't get cleared, my local machine now holds Production DB credentials - really bad security practice, especially at companies that go through strict security audits. I wish PSQL just took a password command line option instead. – user2490003 Oct 30 '16 at 18:48

1 Answers1

1

As @eabates mentioned in the comments you can create a .pgpass file for this purpose. More info can be found in the official documentation for Postgres: https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html

Just create a file named .pgpass in the user home directory with as many lines as needed in the following format:

hostname:port:database:username:password
Steve Glick
  • 698
  • 7
  • 19