0

I know this has been asked before and I've read plenty of stuff, such as this, but somehow can't get this working.

I have the following command, being run on a Postgres Docker container, which successfully generates a dump of my DB - but straight to the terminal (STDOUT).

docker exec -it db pg_dump -h example.com -U postgres postgres

Now, if I redirect STDOUT like so:

docker exec -it db pg_dump -h example.com -U postgres postgres > file.txt

...it then hangs, and nothing happens - presumably because it gets stuck on the password prompt, and there's nowhere for it to show that.

I did try to use a .pgpass file instead, but I don't know how to associate that with the pg_dump operation (the .pgpass merely living in the same directory didn't seem to result in it being used.) Contents as follows:

example.com:5432:postgres:postgres:12345

...and then:

docker exec -it db pg_dump

...which simply complains that role "root" does not exist. What am I doing wrong here?

user9540234
  • 85
  • 7
  • As the documentation loudly explains, `.pgpass` is in your home directory. You forgot the `-U` option and the database name in your final dump attempt. Never run something like that as root user! – Laurenz Albe Jun 16 '22 at 11:55
  • Thanks for your comment. which wasn't overly friendly in tone. Why do I need `-U` (user) and database name if both of those things are also specified in the `.pgpass` file? And home directory as in... `C:\Users\`? – user9540234 Jun 16 '22 at 14:54
  • 1) Because a `.pgpass` file can have multiple combinations of user/port/database/host/password and the only way to determine which is the appropriate one is by explicitly supplying the correct parameters. 2) As to the locations it is spelled out here [pgpass](https://www.postgresql.org/docs/14/libpq-pgpass.html) – Adrian Klaver Jun 16 '22 at 16:36
  • 1
    I didn't mean to be unfriendly - to make up I wrote an answer. – Laurenz Albe Jun 16 '22 at 19:57

1 Answers1

1

As the documentation explains,

The file .pgpass in a user's home directory can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). [...]

On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass.

The password file contains the password for a specific host, port, user and database, so you have to specify those in your connection attempt. The client library then takes the password from the corresponding entry in the password file.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263