Re: psql cli tool and connection pooling
Hi D.J.,
Hope this helps.
Sebastiaan Alexander Mannem
Senior Consultant
Anthony Fokkerweg 1
1059 CM Amsterdam, The Netherlands
T: +31 6 82521560
www.edbpostgres.com
Generally, I tend to think of it like there are three separate features provided by connection poolers:
- Connection pooling, where you are trying to save connection overhead
- Offloading read-only queries to standby’s
- Delivering transparent client failover, where you can failover master/standby transparent to client connections
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:
- The one built into the application language
- Java has a connection pooling mechanisme built in
- .NET has one too
- There might be others
- Libpq has native functionality for transparent client failover (psql is based on libpq)
- Connection poolers that mimic a postgres backend,
- Pgpool-II is one like that
- Pgbouncer is another example
- There are others, but let's stick to these two for now.
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.I would love to see a feature on psql cli tool where i can point it to a connection pool provider back end instead of a postgres host.
Yes it does (as do all connection poolers that mimic a postgres backend,)I read that pgpool2 emulates a postgres server, but i've never tried it myself
Sound like you are looking for client connection failover here.I know that some languages provide connection pooling, but i'm relying heavily on psql and its features and i think built-in master/slave connection pooling in psql cli would make my day, and that of any other dirty bash scripters out there.
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.Can anyone comment on their experience with pgpool2 in a high activity production environment?
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/DocumentationAre there other tools or suggestions anyone can point me to?
And look into PGBouncer too: https://pgbouncer.github.io/faq.html
There is client failover in libpq.Is there any appetite to support connection pooling natively by either the postmaster or the psql cli or some other device that could be contrib to the source tree?
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.
Does it even matter? Is server version 10 ddos-proof, other than max_connections?
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
- Superuser connections vs normal connections
- Limit max connections per user
- You can do a lot with customer logon triggers
- etc.
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
On 21 Dec 2018, at 20:08, DJ Coertzen <patrolliekaptein@gmail.com> wrote:I would love to see a feature on psql cli tool where i can point it to a connection pool provider back end instead of a postgres host. I read that pgpool2 emulates a postgres server, but i've never tried it myself. I know that some languages provide connection pooling, but i'm relying heavily on psql and its features and i think built-in master/slave connection pooling in psql cli would make my day, and that of any other dirty bash scripters out there.Can anyone comment on their experience with pgpool2 in a high activity production environment?Are there other tools or suggestions anyone can point me to?Is there any appetite to support connection pooling natively by either the postmaster or the psql cli or some other device that could be contrib to the source tree?Does it even matter? Is server version 10 ddos-proof, other than max_connections?Thanks.
Вложения
В списке pgsql-performance по дате отправления: