1

OS: Ubuntu Xenial 16.04 LTS

PostgreSQL Version: 9.5.4

I am trying to set up automatic database backups on my server, and every time I run pg_dump, it tells me that no password was supplied. Here's the command I'm using to test:

pg_dump --host $DB_HOST --port 5432 --username $DB_USER --no-password  --format custom --blobs --verbose --file ~/backups/postgresql/test.backup $DB_NAME

All of my environment variables are working just fine. To supply PostgreSQL with the password, I've created a .pgpass file in ~/.pgpass. Here's what it looks like:

# hostname:port:database:username:password $DB_HOST:5432:$DB_NAME:$DB_USER:$DB_PASS

The file has 0600 permissions, as specified in the guide. Yet, for some reason, it's not using the password whenever I run pg_dump. I've looked at other answers and multiple guides online but still can't figure out what the problem is.

Any help would be greatly appreciated.

Alexander
  • 184
  • 2
  • 11
  • You do realize that you need to have literal values in `~/.pgpass`, not shell variables like `$DB_HOST`? It's not entirely clear from how you wrote your question. – Tometzky Oct 10 '16 at 22:15
  • I actually did not know that. Thank you for the clarification! Is there a way I can use environment variables in the file? – Alexander Oct 11 '16 at 02:55
  • No. But you can use environment variables `$PGDATABASE` `$PGPASSWORD` etc and don't need `.pgpass` at all. See [docs](https://www.postgresql.org/docs/9.5/static/libpq-envars.html) – Tometzky Oct 11 '16 at 09:54

1 Answers1

1

You need to have literal values in ~/.pgpass, not shell variables like $DB_HOST.

If you need environment variables then you can use these supported by libpq (PostgreSQL client library) like $PGDATABASE or $PGPASSWORD. There are many others. You won't need ~/.pgpass at all is you use them.

Tometzky
  • 2,679
  • 4
  • 26
  • 32
  • Thank you very much; this worked beautifully! I decided to hardcode the values into my `~/.pgpass` file. – Alexander Oct 12 '16 at 14:26