| Hi D.J., Hope this helps. Generally, I tend to think of it like there are three separate features provided by connection poolers:
Depending on the solution you choose, it might implement some of these features. Reading your mail, you are looking for all of them, and are not clear yet which to focus on. I would bring in a specialist at this moment, but let me try to give you a head start: I am aware if wo main connection pooling implementations and they all deliver some of these features:
Since you mention psql, the first implementation will not help you that much (except for transparent client failover). The second implementation will do what you require. You connect to the pooler, and the pooler connects to postgres. To psql, connecting to the pooler is transparent. He connects to a port and gets a Postgres connection. What happens in the background of that, is transparent. Now, getting into your comments / questions: Great, look at Pgpool-II and PgBouncer. They have overlapping use cases, but depending on the exact situation, might be that one fits better than the other. Yes it does (as do all connection poolers that mimic a postgres backend,) Sound like you are looking for client connection failover here. Read this: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING and specifically '34.1.1.3. Specifying Multiple Hosts’ for the most basic approach to implement this. Yes. It works, and depending on your use case, it can even add performance enhancing options. On the other hand, it tries to fix many things in one tool, and that makes it a complex solution too. And it adds limitations to the solution too. I have seen a lot of implementations, where people focussed on one thing, but neglected another important thing. My best advice is: Bring in a specialist for this one. Well, read the documentation on Pgpool-II: http://www.pgpool.net/mediawiki/index.php/Documentation And look into PGBouncer too: https://pgbouncer.github.io/faq.html There is client failover in libpq. I think t was specifically decided to not fix connection pooling in core, since fixing it in the app layer / external connection poolers keeps Postgres cor code cleaner. And there are a lot of situations, where you want connection pooler features, so let's keep lean code for that. Fixing the 'read-only queries’ feature must be done on the client side at all times.
There is no real DDOS proof. In the end, any system can be brought down by a DDOS attack if done under the right circumstances. And all mitigations for DDOS can be circumvented in one way or another. This is not specific to Postgres. It is a very generic thing. You can build a very DDOS-resilient solution with postgres. But that greatly depends on what you want to mitigate and how much effort you want to put into it. An example is connection exhaustion: You can manage that in a lot of ways
But every mitigation needs some thinking, setting some limit, and depending on what you want to do, you might need to code (like a logon trigger). EnterpriseDB has a lot of experience with this regard. And we have a product that even extents possibilities here. So I would say, bring in a professional with a lot of experience. It is probably the best way to build a solution that fits best to the things you mentioned in this question.  Sebastiaan Alexander Mannem Senior Consultant Anthony Fokkerweg 1 1059 CM Amsterdam, The Netherlands T: +31 6 82521560 www.edbpostgres.com     
|
