Thread: Pgbouncer help
HI
In our current project, we are opening several postgresql connection. Few connections are frequently used and few are occasionally used. Hence we plan to adapt connection pool method to avoid more connection to open. We plan to use “Pgbouncer”. Most of the pgbouncer example shows how to configure, but they are not explaining how to use in C++.
Please provide me a example, how to use it in C++.
Thanks & Regards,
Ramkumar
On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com> wrote: > HI > > > > In our current project, we are opening several postgresql connection. Few > connections are frequently used and few are occasionally used. Hence we plan > to adapt connection pool method to avoid more connection to open. We plan > to use “Pgbouncer”. Most of the pgbouncer example shows how to configure, > but they are not explaining how to use in C++. > > > > Please provide me a example, how to use it in C++. pgbouncer is designed to look (to the client) just like a normal postgresql server. If you want all connections to the database to go through pgbouncer, you can move the real server to a different port, and then start up pgbouncer on that vacated port. In this case, the clients do not need to make any changes at all to their configuration. If you want to keep the real server on the same port as it currently is and to use a special port to go through pgbouncer, then you need to change the clients to use that new port number. You do this the same way you would change the client to use a different port if that different port were a regular postgresql server. Cheers, Jeff
On 08/27/2013 10:40 AM, Jeff Janes wrote: > On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS > <ramkumar.yelai@siemens.com> wrote: >> HI >> >> >> >> In our current project, we are opening several postgresql connection. Few >> connections are frequently used and few are occasionally used. Hence we plan >> to adapt connection pool method to avoid more connection to open. We plan >> to use “Pgbouncer”. Most of the pgbouncer example shows how to configure, >> but they are not explaining how to use in C++. >> >> >> >> Please provide me a example, how to use it in C++. > pgbouncer is designed to look (to the client) just like a normal > postgresql server.... However... Since clients are reusing previously accessed server sessions, be sure to consider the implication of the different pool types and reset options. For example, if you have multi-statement transactions you cannot, of course, use statement-level pooling since the server connection is released after the statement. And if you set any runtime parameters (set time zone to..., set statement timeout..., etc.) then you will probably need to use session-level pooling and you will need to set server_reset_query appropriately otherwise you risk ending up either having parameters set to values you did not expect by a previously connected client or having parameters you set "disappear" when your next statement is assigned to a different server connection. A similar issue exists if you use temporary tables as you need to be sure to stick with the same server connection while your processing needs the temporary table and you need to clean it up when you release the connection so it doesn't use extra resources and doesn't interfere with statements issued a subsequent client. For more, see the following if you haven't read them already: http://pgbouncer.projects.pgfoundry.org/doc/config.html http://wiki.postgresql.org/wiki/PgBouncer Cheers, Steve
Thanks Jeff, As I understand from your point, instead of connecting Postgresql port, try to use PgBouncer port. I am using libpq library functions connect postgreql and code changes would be like this. Previous code : sprintf(conninfo, "user=%s password=%s dbname=%s hostaddr=%s port=%d", PG_USER, PG_PASS, PG_DB, PG_HOST, PG_PORT); conn = PQconnectdb(conninfo); new code: sprintf(conninfo, "user=%s password=%s dbname=%s hostaddr=%s port=%d", PG_USER, PG_PASS, PG_DB, PG_HOST, PG_BOUNCER_PORT); conn = PQconnectdb(conninfo); -----Original Message----- From: Jeff Janes [mailto:jeff.janes@gmail.com] Sent: Tuesday, August 27, 2013 11:10 PM To: Yelai, Ramkumar IN BLR STS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgbouncer help On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS <ramkumar.yelai@siemens.com> wrote: > HI > > > > In our current project, we are opening several postgresql connection. > Few connections are frequently used and few are occasionally used. > Hence we plan to adapt connection pool method to avoid more connection > to open. We plan to use "Pgbouncer". Most of the pgbouncer example > shows how to configure, but they are not explaining how to use in C++. > > > > Please provide me a example, how to use it in C++. pgbouncer is designed to look (to the client) just like a normal postgresql server. If you want all connections to the database to go through pgbouncer, you can move the real server to a different port, andthen start up pgbouncer on that vacated port. In this case, the clients do not need to make any changes at all to theirconfiguration. If you want to keep the real server on the same port as it currently is and to use a special port to go through pgbouncer,then you need to change the clients to use that new port number. You do this the same way you would change theclient to use a different port if that different port were a regular postgresql server. Cheers, Jeff
Thanks for your great inputs. Let me see, how to handle these situations in our project. Regards, Ramkumar -----Original Message----- From: Steve Crawford [mailto:scrawford@pinpointresearch.com] Sent: Wednesday, August 28, 2013 1:09 AM To: Jeff Janes Cc: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgbouncer help On 08/27/2013 10:40 AM, Jeff Janes wrote: > On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS > <ramkumar.yelai@siemens.com> wrote: >> HI >> >> >> >> In our current project, we are opening several postgresql connection. >> Few connections are frequently used and few are occasionally used. >> Hence we plan to adapt connection pool method to avoid more >> connection to open. We plan to use "Pgbouncer". Most of the >> pgbouncer example shows how to configure, but they are not explaining how to use in C++. >> >> >> >> Please provide me a example, how to use it in C++. > pgbouncer is designed to look (to the client) just like a normal > postgresql server.... However... Since clients are reusing previously accessed server sessions, be sure to consider the implication of the different pooltypes and reset options. For example, if you have multi-statement transactions you cannot, of course, use statement-level pooling since the serverconnection is released after the statement. And if you set any runtime parameters (set time zone to..., set statement timeout..., etc.) then you will probably need touse session-level pooling and you will need to set server_reset_query appropriately otherwise you risk ending up eitherhaving parameters set to values you did not expect by a previously connected client or having parameters you set "disappear"when your next statement is assigned to a different server connection. A similar issue exists if you use temporary tables as you need to be sure to stick with the same server connection whileyour processing needs the temporary table and you need to clean it up when you release the connection so it doesn'tuse extra resources and doesn't interfere with statements issued a subsequent client. For more, see the following if you haven't read them already: http://pgbouncer.projects.pgfoundry.org/doc/config.html http://wiki.postgresql.org/wiki/PgBouncer Cheers, Steve