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?
As the documentation explains,
The file
.pgpassin 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.