3

I have a set of 4 vagrant systems.

  • web1 (Main db)
  • web2 (db replicated from web1)
  • mgmt (a node from which Im connecting from)
  • web3 (pgpool is running)

My pgpool is setup to load balance between the databases on web1 and web2. It works well and I can connect to the db even when one of them is down through pgpool. But when I brought both databases down (web1 and web2), I lose the connection which was existing through pgpool.

Now when I bring the main db up again, I'm assuming that pgpool should regain that connection, but it does not. Even if my assumption is wrong, how do I get pgpool to gain the connections to my db on web1 and web2 again?

Currently after both databases go down, I get the message - 

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

When I bring the database back up again, I still get this message -

psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

I'm able to gain the connections back only if i restart the systems. Is there something that I'm missing or have I misunderstood the way pgpool works?

I have not added the pgpool and postgresql conf files here as I dont think they are relevant to this, but if needed I will add them.

1ac0
  • 2,875
  • 3
  • 33
  • 47
leoOrion
  • 1,833
  • 2
  • 26
  • 52

1 Answers1

3

If the backend node is set to failover in case of node failure, Pgpool-II detaches the failed node after the failover is performed on it. Which is what happening in your case. But once detached the failed nodes are never automatically attached back to Pgpool-II even if they become available/reachable again. The reason for that is Pgpool-II can't ensure by itself if the node failure has caused some data corruption and/or if it is still properly replicated as intended by user. So failed nodes needs to be manually attached to Pgpool-II and for that you can use the pcp_attach_node command.

Muhammad Usama
  • 2,797
  • 1
  • 17
  • 14
  • can you please show me some reference to this in the documentation? – leoOrion Aug 29 '17 at 11:36
  • 1
    I doubt if it is mentioned in the Pgpool-II official documentation, But this is how the Pgpool-II works. See the https://blog.dbi-services.com/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration/ blog post which touch the topic of re-attaching the node to Pgpool – Muhammad Usama Aug 29 '17 at 14:19