I recently needed to write a simple scheduled backup script for PostgreSQL, and quickly noticed that pg_dump wanted an interactive password every time. That obviously does not fit well with automation, so I looked up a couple of ways around it.
There are two common solutions:
- Use an environment variable and assign the database password to
PGPASSWORD:
1 | export PGPASSWORD="$put_here_the_password" |
- Create a
.pgpassfile in the user’s home directory and store the connection credentials there:
1 | localhost:5432:mydbname:postgres:mypass |
After that, set the file permissions to 600, otherwise PostgreSQL may ignore it.
Both methods allow password-protected commands such as pg_dump to run without prompting for input.
Reference:
http://www.postgresql.org/docs/current/static/libpq-pgpass.html
