0

I'm trying to setup postgres cluster of two nodes (primary and standby). In order to activate automatic failover, I'm using pgpool-II.

I followed the following article: https://www.pgpool.net/docs/41/en/html/example-cluster.html and the only thing difference I did is installing postgresql version 12 instead of version 11.

Knowing that I'm trying it useing two centos7 images on VMware. I faced the following issues:

When I run systemctl status pgpool.service on both nodes, it returned success. Also I can access postgresql using the watchdog delegate IP.

But what testing failover, everything goes wrong.

Scenario 1:

  1. I accessed my database using watchdog delegate Ip.

  2. I disconnect the standby server.

Result: My session to postgresql continued to work for less than a minute and then it failed. and I'm unable to connect again, until I reconnect the standby node, and restart the pgpool service again.

Scenario 2:

  1. I accessed my database using watchdog delegate Ip.

  2. I disconnect the primary server.

Result: My session stopped directly. and the standby server is not promoted to be master.

I noticed something (might be related to the above described problem): when I try to run the following command psql 192.168.220.146 -p 9999 -U postgres -c "show pool_nodes"

it fails to work and returned the following:

psql: error: could not connect to server: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.9999"

However if I ran: psql 192.168.220.160 -p 5432 -U postgres it works fine and I can access the postgres shell.

My pool_hba file:

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

host    all         pgpool           0.0.0.0/0                   scram-sha-256
host    all         postgres         0.0.0.0/0                   scram-sha-256

Any help would be appreciated.

dani
  • 33
  • 1
  • 4
  • Can you share your pool_hba.conf? – richyen Feb 13 '20 at 08:06
  • @richyen I editted my post, I already fixed the pool_hba.conf issue as I was using wrong IP's instead of (0.0.0.0/0), now the old error is gone, but I'm getting another one! psql: error: could not connect to server: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.9999" – dani Feb 13 '20 at 08:45
  • Can you confirm your listen_addresses is set to `'*'` for both Postgres and pgpool? – richyen Feb 14 '20 at 05:43
  • Yes it is set to '*' in both – dani Feb 14 '20 at 10:08
  • I have a very similar problem. (https://stackoverflow.com/questions/61739146/pgpool-ii-delegated-ip-is-not-available-when-disconnected-primary-or-standby-no) Have you found a solution? – Belokonev Alexnadr May 11 '20 at 21:03

2 Answers2

0

I followed the following article: https://www.pgpool.net/docs/41/en/html/example-cluster.html and the only thing difference I did is installing postgresql version 11.

I not ping delegate_IP = '192.168.1.233'. May i help you?

Thanks you.

thienvd
  • 1
0

you are not providing -h argument to psql for specifying the IP address. So effectively psql is trying to connect to UNIX domain socket and considering the IP address in the command as the database name.

Try putting -h before the IP address

psql -h 192.168.220.146 -p 9999 -U postgres -c "show pool_nodes"

Muhammad Usama
  • 2,797
  • 1
  • 17
  • 14