I have to do a dump of a Postgres database on a regular basis.
So pg_dump is the obvious choice to do this as is cron to do this periodically.
But the main problem with this is – pg_dump expects a password to be entered at the prompt. There’s no way to pass the password in the prompt to be able to do this.
Stackoverflow to the rescue.
What is required is a .pgpass file in the home directory.
This is cross platform solution, of course.
On Linux, the file should be .pgpass in the ~ (HOME) directory of the user which is being used to do the pg_dump operation.
~/.pgpass
On Windows the file should be
%APPDATA%\postgresql\pgpass.conf
The pgpass file should contain the following information –
host:port:database:username:password
As an example –
localhost:5432:demodb:demouser:demopassword
On Linux, the file permissions should be set to 600, i.e. only the current user has read / write permissions on it.
> chmod 600 ~/.pgpass
The pg_dump command should have the database name, the database user details.
> pg_dump -d demodb -U demouser -f filename.sql
Any time the database is read, either through a shell script or through a program, the pgpass file will be referred to get the credential details.
Reference: PostgreSQL: Documentation: 14: 34.16. The Password File