Re: [GENERAL] Pgbouncer help
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.ja...@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.ye...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pgbouncer help
Thanks for your great inputs. Let me see, how to handle these situations in our project. Regards, Ramkumar -Original Message- From: Steve Crawford [mailto:scrawf...@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.ye...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] Pgbouncer help
On Tue, Aug 27, 2013 at 1:34 AM, Yelai, Ramkumar IN BLR STS ramkumar.ye...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.ye...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
Em 12 de abril de 2012 14:12, Phoenix Kiula phoenix.ki...@gmail.comescreveu: I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer I had this error in the pgbouncer log file after updating to the last version, 1.5, in Centos 6. For me the fix was to set the ownership of the auth_file to pgbouncer. In the previous version, 1.4, it was working with postgres as the owner of that file. Regards, Clodoaldo Thanks for any tips! ___ Pgbouncer-general mailing list pgbouncer-gene...@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgbouncer-general
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto clodoaldo.pinto.n...@gmail.com wrote: Em 12 de abril de 2012 14:12, Phoenix Kiula phoenix.ki...@gmail.comescreveu: I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer I had this error in the pgbouncer log file after updating to the last version, 1.5, in Centos 6. For me the fix was to set the ownership of the auth_file to pgbouncer. In the previous version, 1.4, it was working with postgres as the owner of that file. Regards, Clodoaldo Could you please share stats_users information in pgbouncer.ini file ? -- Thanks Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
Em 14 de abril de 2012 09:39, raghu ram raghuchenn...@gmail.com escreveu: On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto clodoaldo.pinto.n...@gmail.com wrote: Em 12 de abril de 2012 14:12, Phoenix Kiula phoenix.ki...@gmail.comescreveu: I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer I had this error in the pgbouncer log file after updating to the last version, 1.5, in Centos 6. For me the fix was to set the ownership of the auth_file to pgbouncer. In the previous version, 1.4, it was working with postgres as the owner of that file. Regards, Clodoaldo Could you please share stats_users information in pgbouncer.ini file ? That is the default: stats_users = stats, root Clodoaldo -- Thanks Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] [Pgbouncer-general] PGBouncer help (how to get it working)
On Sat, Apr 14, 2012 at 6:35 PM, Clodoaldo Neto clodoaldo.pinto.n...@gmail.com wrote: Em 14 de abril de 2012 09:39, raghu ram raghuchenn...@gmail.comescreveu: On Sat, Apr 14, 2012 at 4:31 PM, Clodoaldo Neto clodoaldo.pinto.n...@gmail.com wrote: Em 12 de abril de 2012 14:12, Phoenix Kiula phoenix.ki...@gmail.comescreveu: I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer I had this error in the pgbouncer log file after updating to the last version, 1.5, in Centos 6. For me the fix was to set the ownership of the auth_file to pgbouncer. In the previous version, 1.4, it was working with postgres as the owner of that file. Regards, Clodoaldo Could you please share stats_users information in pgbouncer.ini file ? That is the default: stats_users = stats, root Could you please modify and below parameters in pgbouncer.ini file: admin_users = postgres,MYSITE_pgbouncer stats_users = postgres,MYSITE_pgbouncer, stats, root and then Restart the pgbouncer. -- Thanks Regards, Raghu Ram EnterpriseDB: http://www.enterprisedb.com
Re: [GENERAL] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 11:00 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. I had already done this. Doesn't do anything. Pgbouncer starts (service pgbouncer restart) but when I try to connect, it tells me psql: ERROR: No such user: MYSITE_pgbouncer Where should i create the MYSITE_pgbouncer user? Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt Seems you already did this. I believe you are connecting as postgres user not from root, if yes, then check .pgpass file too (it will be in postgres user home directory). --Raghav
Re: [GENERAL] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 2:59 PM, Raghavendra raghavendra@enterprisedb.com wrote: . Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt Seems you already did this. I believe you are connecting as postgres user not from root, if yes, then check .pgpass file too (it will be in postgres user home directory). Thanks, but this is not helping. I have the exact same info in three files: 1. The root .pgpass 2. The .pgpass for the postgres user 3. The authfile specified inside pgbouncer -- /var/lib/pgsql/pgbouncer.txt Now what? It's not telling me that the user is unrecognized, so it's likely not that md5 or trust or plain password issue. Pgbouncer is not even recognizing the user! What now? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGBouncer help (how to get it working)
I had pgbouncer working somehow, but we have switched servers recently and now I cannot for the life of me figure out again how to set it up. Online guides say things like create a user ID. Well, where? Inside PG the database? Or in my CentOS system? Here's my /etc/pgbouncer.ini: [databases] * = port = 5432 [pgbouncer] listen_port = 6543 listen_addr = 127.0.0.1 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = postgres,MYSITE_pgbouncer pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 1000 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 And here's my authfile, /var/lib/pgsql/pgbouncer.txt -- MYSITE_pgbouncer Is there something else I need to do? What steps am I missing? When I start pgbouncer at the command line, I see this error: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Thanks for any tips! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGBouncer help (how to get it working)
On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. I had already done this. Doesn't do anything. Pgbouncer starts (service pgbouncer restart) but when I try to connect, it tells me psql: ERROR: No such user: MYSITE_pgbouncer Where should i create the MYSITE_pgbouncer user? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PGBouncer help (how to get it working)
On Fri, Apr 13, 2012 at 11:00 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Fri, Apr 13, 2012 at 2:41 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Apr 12, 2012 at 11:12 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: WARNING: password file /root/.pgpass has group or world access; permissions should be u=rw (0600) or less psql: ERROR: No such user: MYSITE_pgbouncer Pretty sure the error is just the perms on that file. Set them to 0600 and try again. I had already done this. Doesn't do anything. Pgbouncer starts (service pgbouncer restart) but when I try to connect, it tells me psql: ERROR: No such user: MYSITE_pgbouncer Where should i create the MYSITE_pgbouncer user? Add it in pgbouncer.auth file as per your .ini file parameter. auth_file = /var/lib/pgsql/pgbouncer.txt --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
