pgbouncer or pgpool (of the extensions I searched) do this. Which ones are more of a challenge? I mean I have read on some forum that PGPOOL -II would just shutdown and stop or misbehave. Any such experiences or gotchas with pgbouncer?
We have experimented with both at scale and found PgBouncer to be a lot more stable and reliable. We haven't seen gotcha failures, or any failures really, in the last few years. It's also refreshingly simple to automate and scale, although there are shortcomings in the configuration design that ends up requiring some cleverness.
"although there are shortcomings in the configuration design that ends up requiring some cleverness."
Like?
Namely, it doesn't support configuration via CLI or env vars, which means you have no option but to generate a file to be loaded in. To be fair, that's kind of a newer pattern. Also, multi-DB PgBouncer is a little more insecure than it leads on to be by default, along with a few other things you learn quickly as you handle larger and more complex use cases. It's not bad, just not as good as it could be in my opinion.
We use pgbouncer with pools of thousands of concurrent connections. It does what it says on the tin.