CODEWITHSUNDEEP
postgresqlhigh-availabilityfailoverpgpoolfailovercluster
dani
dani

Reputation: 33

pgpool-II Session to Watchdog Delegate Ip Terminated When Either Primary or Standby Node Failed

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.

Upvotes: 0

Views: 777

Answers (2)

Muhammad Usama
Muhammad Usama

Reputation: 3097

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"

Upvotes: 0

thienvd
thienvd

Reputation: 1

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.

Upvotes: 0

Related Questions