[pgpool-general: 5035] Re: Newbie questions

[Rakesh Kumar]

So in replication mode, nothing stops us from using different versions of PG server (typically during a rolling upgrade).  pgpool should not care whether one node is 9.5 and the other 9.6.  Pls confirm.

________________________________________
From: Gervais de Montbrun <gervais at silverorange.com>
Sent: Friday, October 7, 2016 10:08
To: Rakesh Kumar
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 5033] Newbie questions

Hi Rakesh,

I can answer some of these, but I am running a slighly older version of pgpool. I Installed the version that is in the EPEL repo on CentOS. If I had to do it all again, I think I would pull from postgresql and pgpool repos directly.

I have two servers running pgpool and postgres with connection pool, replication and load balancing mode enabled. Both servers run pgpool and postgres. There is a virtual NIC with a hostname assigned to it. Applications talk directly to the virtual nic and pgpool proxies writes to both servers, read go to either server.  If the pgpool node holding the virtual NIC becomes unavailable, then the other pgpool node takes over. If either of the postgres nodes goes offline, pgpool just keeps trucking along and sends all requests to the remaining node. Also, if the postgres nodes return differing values for a write, then pgpool will stop using one of the nodes and continue with only one backend postgres node.

To answer your questions:

1.  I dunno
2. pgpool knows when a node goes down and will put that node offline. To bring it back online, some recovery scripts need to be written. It essentially puts the working node into a state where it is writing WAL files, rsyncs the whole postgres directory over to the downed node and then starts postgres back up on that node. The node recovers the WAL files and tells pgpool that it is back in business.
3. Also dunno
4. As all psql traffic is being passed to the "head node" using the virtual NIC, the clients have no idea that the head node has changed and no changes are required on their end. pgpool does kill and restart child processes when this failover happens and there might be a slight (split second) hiccup in service. At least this has been my experience.
5. HA is possible. You run multiple pgpool servers and they use a watchdog to keep track of eachother. Scripts are triggered to flip the virtual NIC to another node that takes over as the head node.

Lots of answers here:
http://www.pgpool.net/docs/latest/pgpool-en.html
http://www.pgpool.net/docs/latest/pgpool-en.html#replication_mode
http://www.pgpool.net/docs/latest/pgpool-en.html#stream
http://www.pgpool.net/docs/latest/pgpool-en.html#online-recovery
http://www.pgpool.net/docs/latest/pgpool-en.html#watchdog

It's all the same site really.  :-)

I hope this is helpful and not more confusing.

Cheers,
Gervais



On Oct 6, 2016, at 12:38 PM, Rakesh Kumar <rakeshkumar464 at outlook.com<mailto:rakeshkumar464 at outlook.com>> wrote:

I am researching pgpool technology.  Let us say we want to use it as an alternative to streaming replication of PG.  Apparently in replication mode, pgpool can replicate to all participating nodes, which presumably will be in raw mode (non replicating mode).

My questions:

1 - Do we have a choice of limiting number of nodes for sync writes (and remaining one in async), so that overall performance impact is limited.
2 - If a node is down, does pgpool has the ability to resync from the last known transaction that was committed. How does it do it? Does it maintain
    a state information or save all transactions that need to be replayed later on.
3 - Is app-stickiness possible. By app-stickiness I mean, a given app or session always goes to the same node.  This helps in avoiding stale reads.
4-  Is the failover transparent to the client. Meaning, if a node goes down, do clients have to use a new connection string.
5 -  How about high availability  of pgpool itself.  Can we have multiple pgpools?

If there is a documentation to answer these questions, please let me know.

thanks.

_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>
http://www.pgpool.net/mailman/listinfo/pgpool-general