Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

how to create ambari database backup without enter password

avatar

how to create ambari database backup without enter password?

we want to create the backup in bash script , so need to automate the CLI ( CLI must be in silent mode )

pg_dump -U {ambari.db.username} -f ambari.sql
Password: {ambari.db.password}
Michael-Bronson
1 ACCEPTED SOLUTION

avatar
Master Mentor

@Michael Bronson

You might want to take a look at the ".pgpass" file gfeature of Postgres. It allows users to use~/.pgpassfile to avoid regularly having to type in passwords.

This file should contain lines of the following format:

hostname:port:database:username:password

More info:

https://www.postgresql.org/docs/current/static/libpq-pgpass.html

.

View solution in original post

6 REPLIES 6

avatar
Master Mentor

@Michael Bronson

You might want to take a look at the ".pgpass" file gfeature of Postgres. It allows users to use~/.pgpassfile to avoid regularly having to type in passwords.

This file should contain lines of the following format:

hostname:port:database:username:password

More info:

https://www.postgresql.org/docs/current/static/libpq-pgpass.html

.

avatar

what should be the port name number ? and the hostname ( I guess the hostname of master02 because PG installed on it )

Michael-Bronson

avatar
Master Mentor

@Michael Bronson

By default Postgresql listens on 5432 port. However it might be customized at your end as well.

So you might find the port using the following command as well:

# ps -ef | grep postmaster
postgres   146     0  1 11:03 ?        00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data<br>

.

OR

# netstat -tnlpa | grep postgres | awk '{print $4}'

.

avatar

@Jay we set the file - /var/lib/pgsql/.pgpass

but still command - insist to get password - pg_dump -U ambari -f ambari.sql

so where I am wrong ?

file - /var/lib/pgsql/.pgpass

localhost:5432:postgres:ambari:bigdata
Michael-Bronson

avatar
Master Mentor

@Michael Bronson

This can happen if you enter Incorrect details about your Postgres inside the file ".pgpass". Like incorrect hostname of the DB or incorrect permission to the file ".pgpass" or incorrect location of this file..

I tried it like this and it works:

1). Created a file as following:

[root@sandbox ~]# cat ~/.pgpass 
localhost:5432:ambari:ambari:bigdata


2). Make sure that the file has 600 permission (else this file will be ignored)

[root@sandbox ~]# chmod 600 ~/.pgpass


3). Confirm that this file exist in the correct location (Means the default Home directory of the user. In my cazse "root" user has the home directory as "/root" so path should be as following):

[root@sandbox ~]# ls -l ~/.pgpass 
-rw------- 1 root root 37 Feb 19 10:27 /root/.pgpass


4). Once above is done now you can try running the pg commands without supplying the password.

[root@sandbox ~]# pg_dump -U ambari -f  /tmp/1ambari.sql
[root@sandbox ~]# ls -l /tmp/1ambari.sql
-rw-r--r-- 1 root root 5626774 Feb 19 10:31 /tmp/1ambari.sql

.


avatar

@Jay , its working now after I set the file under the root home and not postgres home

Michael-Bronson