pg_dumpall without prompting password [duplicate]

Question

We are trying to automate the backup of all PostgreSQL databases of a cluster, and we decided to use 'pg_dumpall' utility. But we couldn't find a way to execute 'pg_dumpall' without prompting password. We are using PostgreSQL-10.

We are trying to execute the following command

pg_dumpall -U "username" -h "hostname" > "location"

How can we automate pg_dumpall without prompting password? Please help us on this. Thanks

Sunrise

Check out this answer on a neighboring site. There are a few ways (set environment variable, use a .pgpass file, etc) – Freberg

Thanks Matt.. that worked – Sunrise

Answer 1

I am answering my own question

we can use ".pgpass" file to avoid prompting password

" CREATING AND USING A .PGPASS FILE

=====================================

A .pgpass file will allow you to use postgres CLI tools such as psql and pg_dump without having to manually enter a password — You can use the programs from scripts without having to run them as a non password protected user.

First, create the .pgpass file

#nano /root/.pgpass 

According to the official doccumentation the format of the file is as follows:

hostname:port:database:username:password

The file supports the use of # tags for comments and * to match on wildcards. Here is an example of mine:

*:*:*:postgres:jerry_pass 

Enter your database information and save.

Next, set the permissions. If they are not 600 Postgres will ignore the file.

#chmod 600 /root/.pgpass

Now how do we make postgres use it? A quick scan through psql man page shows:

-w, --no-password 

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

Here are some usage examples:

#psql -d postgres -U postgres -w
#pg_dump -U postgres -w -Fc   "

Sunrise