FATAL: authentication failed for user DETAIL: username and/or password does not match pgpool

 

POSTGRES FAILOVER/FAILBACK + PGPOOL

In my postgres environment which consists on one primary and one standby database got failover.

After failover the primary become the standby and standby become the primary. 

But the issue is pgpool not able to process the connection to new primary node and at application we start receiving below error after failover/failback command(switchover/failover)

Error Reported At Client End

psql: error: server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

Error Reported In Pgpool.log File

FATAL:  unable to read from client

DETAIL:  pcp_read failed with error : "Success"

LOG:  PCP process with pid: 47261 exit with SUCCESS.

LOG:  PCP process with pid: 47261 exits with status 256

FATAL:  pgpool is not accepting any new connections

DETAIL:  all backend nodes are down, pgpool requires at least one valid node

HINT:  repair the backend nodes and restart pgpool

LOG:  child process with pid: 47254 exits with status 256

LOG:  fork a new child process with pid: 47316

LOG:  forked new pcp worker, pid=47323 socket=7

FATAL:  unable to read from client

DETAIL:  pcp_read failed with error : "Success"

LOG:  PCP process with pid: 47323 exit with SUCCESS.

LOG:  PCP process with pid: 47323 exits with status 256

Resolution 

1.  Check the pgpool.log file to find the reason for this error and notified below error.

cat /var/log/edb/pgpool4.2/edb-pgpool42.log

DETAIL:  all backend nodes are down, pgpool requires at least one valid node

HINT:  repair the backend nodes and restart pgpool

2. By looking this error,  came to know that pcp_attach_node not ran properly. But I have configured in such a way that it should run automatically after switchover/failover.  So what went wrong in my case

/usr/edb/pgpool4.2/bin/pcp_attach_node  -U pcpuser -h **** -w -n 0

3. Run the above command manually to find why the node not attach once standby database(earlier which was primary) came online.

export PCPPASSFILE=/var/lib/edb-as/.pcppass

enterprisedb:~$ /usr/edb/pgpool4.2/bin/pcp_attach_node  -U pcpuser -h *** -w -n 0

FATAL:  authentication failed for user "pcpuser"

DETAIL:  username and/or password does not match

4.Checked my pcp.conf file and .pcppass file to check if the password is same. 

enterprisedb@:/etc/edb/edb-pgpool42$ cat pcp.conf

pcpuser:6a01bfa30172639e770a6aacb78a3ed4

enterprisedb@:/etc/edb/edb-pgpool42# cat /var/lib/edb-as/.pcppass

*******:9898:pcpuser:**

It was okay.

5. Finally checked the permission and owner the the pcp.cof and .pcppass. In my case the file permission was the issue.

enterprisedb@ip:/etc/edb/edb-pgpool42$  ls -lrth  /var/lib/edb-as/.pcppass

-rw-rw-r-- 1 enterprisedb enterprisedb 30 Dec 27 09:20 /var/lib/edb-as/.pcppass

root@ip:/etc/edb/edb-pgpool42# chmod 0600 /var/lib/edb-as/.pcppass

root@ip:/etc/edb/edb-pgpool42# ls -lrth  /var/lib/edb-as/.pcppass

-rw------- 1 enterprisedb enterprisedb 30 Dec 27 09:20 /var/lib/edb-as/.pcppass

enterprisedb@ip:~$ export PCPPASSFILE=/var/lib/edb-as/.pcppass

enterprisedb@ip:~$ /usr/edb/pgpool4.2/bin/pcp_attach_node  -U pcpuser -h ***** -w -n 0

pcp_attach_node -- Command Successful

enterprisedb@ip-172-31-80-35:~$