Re: [Pgpool-general] How to configure pgpool for pool conexions..
On Thu, 2011-10-06 at 10:59 -0500, Matias Israel Malpica Escobar wrote: Hi everyone i´ve been trying to understand and configure pgpool for a pool of connections (not sure if that is the term), i already follow a manual and confiigure it like this: listen_addresses = '*' port = pcp_port = 9898 socket_dir = '/var/run/postgresql' pcp_socket_dir = '/var/run/postgresql' backend_socket_dir = '/var/run/postgresql' pcp_timeout = 10 num_init_children = 32 max_pool = 4 child_life_time = 300 connection_life_time = 0 child_max_connections = 0 client_idle_limit = 0 authentication_timeout = 60 logdir = '/var/run/postgresql' replication_mode = false load_balance_mode = false replication_stop_on_mismatch = false replicate_select = false reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' print_timestamp = true master_slave_mode = false connection_cache = true health_check_timeout = 20 health_check_period = 60 health_check_user = 'pgpool2' failover_command = '' failback_command = '' insert_lock = false ignore_leading_white_space = true log_statement = false log_connections = false log_hostname = false parallel_mode = false enable_query_cache = false pgpool2_hostname = 'pgsql1' system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = '' backend_hostname0 = '192.168.0.3' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = '192.168.0.4' backend_port1 = 5432 backend_weight1 = 1 enable_pool_hba = false recovery_user = 'pgpool2' recovery_password = '' recovery_1st_stage_command = '' recovery_2nd_stage_command = '' recovery_timeout = 90 After this i startes pgpool and everything seems to be working, how do i actually test it? Now i have a couple of questions. With this i understand that i am creating 4 pools for 32 connections each, so it would be 128 connections? Yes, but only 32 active at one time. The purpose of this is, first allow pgpool to handle connections instead of postygres so the end user do not receive an error, adn second use the same connection to dispach several request that are alike... Obviously, if your user tries to connect to a database that doesn't exist, he'll get an error message. If he tries to execute a badly written query, he'll also get an error message. pgpool will be able to reuse a connection if the user wants to connect to the same database with the same user. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
If your database opens the port then pgpool finds it in use when it starts. Jose Autonomy Ops verificare tua hinc -Original Message- From: Matias Israel Malpica Escobar [mailto:matias.malp...@thyssenkrupp.com] Sent: Thursday, October 06, 2011 2:39 PM To: Jose Mendoza Cc: pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] How to configure pgpool for pool conexions.. I am trying to open the database using the port (pgpool port), let me know if that answers your question.. I am restarting services anyway... De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:39 p.m. Para: Matias Israel Malpica Escobar; Guillaume Lelarge CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
Thanks for the quick reply, mmm but i never configure the databse to open the port , would it help if i copy my pgpool.config file? De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:47 p.m. Para: Matias Israel Malpica Escobar CC: pgpool-general@pgfoundry.org Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. If your database opens the port then pgpool finds it in use when it starts. Jose Autonomy Ops verificare tua hinc -Original Message- From: Matias Israel Malpica Escobar [mailto:matias.malp...@thyssenkrupp.com] Sent: Thursday, October 06, 2011 2:39 PM To: Jose Mendoza Cc: pgpool-general@pgfoundry.org Subject: RE: [Pgpool-general] How to configure pgpool for pool conexions.. I am trying to open the database using the port (pgpool port), let me know if that answers your question.. I am restarting services anyway... De: Jose Mendoza [jose.mend...@autonomy.com] Enviado el: jueves, 06 de octubre de 2011 04:39 p.m. Para: Matias Israel Malpica Escobar; Guillaume Lelarge CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. What is opening the port pgpool or the database? Sometimes I found out that postgres opens the port and have to stop pgpool and postgres and restart postgrsql and pgpool again. Jose Autonomy Ops verificare tua hinc -Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general-boun...@pgfoundry.org] On Behalf Of Matias Israel Malpica Escobar Sent: Thursday, October 06, 2011 1:58 PM To: Guillaume Lelarge Cc: pgpool-general Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# The database is actually on the same server, and i have checked the iptables and the port is open... PG_HBA is also reflecting that i can permit conections to UNIX conection... What i am doing wrng De: Guillaume Lelarge [guilla...@lelarge.info] Enviado el: jueves, 06 de octubre de 2011 11:25 a.m. Para: Matias Israel Malpica Escobar CC: pgpool-general Asunto: RE: [Pgpool-general] How to configure pgpool for pool conexions.. On Thu, 2011-10-06 at 11:13 -0500, Matias Israel Malpica Escobar wrote: Hi thanks for your answers, what i wanted to say about the connections and errors from pgpool was that, for what i understand postgres returns an error if the number of connections are higher than its maximum, and pgpool puts the remaining connections in a queue is that right? I am actually doing it with the configuration that i have? Is there any way to test it? Yes, that's right. You don't get error message telling you the usual FATAL message about max_connections. I don't know a quick way to test it. Perhaps with pgbench and a number of clients a bit higher than the max_connections of PostgreSQL? I would first try that. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
On October 6, 2011 02:44:34 PM Matias Israel Malpica Escobar wrote: Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# What is socket_dir set to in pgpool.conf? If it isn't /tmp, then psql will need a hint how to find the unix domain socket to talk to. ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
Re: [Pgpool-general] How to configure pgpool for pool conexions..
-Original Message- From: pgpool-general-boun...@pgfoundry.org [mailto:pgpool-general- boun...@pgfoundry.org] On Behalf Of Alan Hodgson Sent: Thursday, October 06, 2011 5:52 PM To: pgpool-general@pgfoundry.org Subject: Re: [Pgpool-general] How to configure pgpool for pool conexions.. On October 6, 2011 02:44:34 PM Matias Israel Malpica Escobar wrote: Another question for you guys... I have succesfully installed and configure pgpool, if i go to pgadmin and connect to port of my server i can see the databases and everything... but when i try to run the following i get this error: [root@etc]# psql -d test -U testuser -p psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.? [root@ etc]# What is socket_dir set to in pgpool.conf? If it isn't /tmp, then psql will need a hint how to find the unix domain socket to talk to. First, check that you can connect to the database by doing: psql -U postgres -h localhost -p 5432 (assuming proper postgresql.conf entries, like - listen_addresses = '*') If it succeeds, then, in pgpool.conf, check that backend_hostname0 is pointing to where the database is. If it's localhost, the following should most likely be in your pgpool configuration: backend_hostname0 = 'localhost' backend_port0 = 5432 backend_weight0 = 0 backend_data_directory0 = '/var/lib/pgsql/9.0/data' Restart pgpool and try again connecting to the database through pgpool by doing: psql -U postgres -p If it doesn't work. Stop pgpool, and run it with debug output by doing the following as root: pgpool -n -d It should tell you that it cannot connect to backend0 for a reason. Then come back and show us your findings. -Daniel ___ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general
