Linux – pg_basebackup automatic password

bashcentoslinuxpostgresql-9.3

I have a master and slave database setup. The master database is on a secure network and only the postgres port is allowed through to the slave database server.

The database needs to be secured and only the admin has the database password. The servers are also in a remote site that is not accessible to the admin without going on site.

I found that the slave database will get corrupted/out of sync if the connection between the two are interrupted for any length of time. I wrote a script to resync the slave database but the issue I'm facing is that once the pg_basebackup runs it prompts for a password. I need a simple way to pass the password to the pg_basebackup command.

sudo -u postgres pg_basebackup -h <masterDB-IP> -D /var/lib/pgsql/9.3/data -U rep -v -P

Any suggestions?

Best Answer

You can use .pgpass file as explain here. Of course, you need to make sure the file is not world-readable to secure your password.

Another option is to use some trusted user. You can create a specific system user (without login password) and grant privilege on the remote DB server using trust method only from trusted IP in pg_hba.conf config file. You don't need to pass any password. All you need is to secure this account which is done by not setting any password. Only root user can su to this user to execute command or configure cron job.

Related Topic