0

Trying to install Postgresql-9.4 High Availability (HA) in RHEL7, using three nodes. In all three nodes, installed postgresql, updated postgresql.conf and pg_hba.conf files accordingly. After completing all configurations, tried to check verify the synchronous streaming replication by using the command in Master server

select * from pg_stat_replication;

It returns

postgres=# select * from pg_stat_replication;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_
location | flush_location | replay_location | sync_priority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+---------------+-------
---------+----------------+-----------------+---------------+------------
(0 rows)

Which shows, there is no replication. When I was looking into the postgresql log files, I found the connection is not being established from standby nodes to the Master node. The log files in all three nodes looks like,

Node 0:

FATAL:  no pg_hba.conf entry for replication connection from host "x.x.x.x (node1)", user "postgres", SSL off
FATAL:  no pg_hba.conf entry for replication connection from host "x.x.x.x (node2)", user "postgres", SSL off

Node 1 and Node 2:

FATAL:  could not connect to the primary server: 
FATAL:  no pg_hba.conf entry for replication connection from host "x.x.x.x (node0)", user "postgres", SSL off

To check the connection, I've disabled iptables in all three nodes. Able to ping from/to all other nodes, telnet from slave to master works and from master to slave doesn't work. Not sure, why it happens (even when iptables service is stopped).

0

In the master node (node 0): update the pg_hba.conf file, add postgres (user) to access the database.

In the standby nodes (node1 & node2): disable iptables or configure firewall to accept connections to and from master.

In /etc/sysconfig/iptables, add below lines and restart.

 -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432(port) -j ACCEPT
 -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT

This solution resolved my issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.