Postgresql – Can’t set up witness in Repmgr 2.0

failoverpostgresqlpostgresql-9.1repmgr

I am trying to setup repmgr using the autofailover quick setup tutorial provided on Github Link

But I am kind of stuck on the witness section, when trying to create the witness with repmgr -d repmgr -U repmgr -h [MASTERIP] -D /var/lib/postgresql/9.1/witness -f /var/lib/postgresql/9.1/repmgr/repmgr.conf witness create

I get the following error:

Connection to database failed: fe_sendauth: no password supplied

I am not sure which database the error is talking about? Is it the master's DB or the witness'?

Also, I haven't been able to find a password parameter to pass to repmgr yet, is there even one?

Below are the repmgr.conf files for each server:

Witness: 

cluster=main
node=3
node_name=witness
conninfo='host=[WITNESS IP] dbname=witness user=witness password=witness  port=5499'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Standby: 

cluster=main
node=2
node_name=node2
conninfo='host=[STANDBY IP] dbname=[DB NAME] user=[DB USER NAME] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Master: 

cluster=main
node=1
node_name=node1
conninfo='host=[MASTER IP] dbname=[MASTER DB] user=[MASTER DB USER] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Best Answer

The error message means that repmgr account can't login onto master server with provided password (probably empty one).

You need to allow connections on master, in pg_hba.conf:

host    all     repmgr  192.168.1.1/32       md5
host    replication     all     192.168.1.1/32       md5

then trigger a master server reload:

/usr/lib/postgresql/9.1/bin/pg_ctl reload -D /var/lib/postgresql/9.1/main

(where -D is the data directory of PostgreSQL).

Assumsing you've created a repmgr account, on master with some secret_passw0rd

createuser --login --superuser -P repmgr -W
createdb -O repmgr repmgr

then on standby server create a .pgpass file in postgres home:

su - postgres
cat > .pgpass << EOF
*:*:*:repmgr:secret_passw0rd
EOF
chmod 0600 .pgpass

That's all under assumption that you're using md5 password authentication for replication, if you switch to trust you could skip setting the password:

host    all     repmgr  192.168.1.1/32       trust
host    replication     all     192.168.1.1/32       trust

Now you can check connection to your master server:

psql -h master.hostname -U repmgr
Related Question