I have to do a dump of a Postgres database on a regular basis.
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
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
On Windows the file should be
The pgpass file should contain the following information –
As an example –
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
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