Thread: pgpool
So, I've got it installed, and tweaked the configuration, but I simply can not figure out how to connect to my databases via pgpool. Is this simply transparent? I don't see how. So I have a postmaster running on port 5434, how do I connect to that database via pgpool? I simply can not find this piece of info in the docs? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 => * pgpool has to be configured in that way that it connects to the database on port 5434 * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: > So, I've got it installed, and tweaked the configuration, but I simply > can not figure out how to connect to my databases via pgpool. Is this > simply transparent? I don't see how. > > So I have a postmaster running on port 5434, how do I connect to that > database via pgpool? I simply can not find this piece of info in the docs?
Gerd Koenig wrote: > Hi Geoffrey, > > you do not need to connect to your database directly, just connect to pgpool > itself. > e.g.: your database runs on port 5434, pgpool runs on port 5432 > => > * pgpool has to be configured in that way that it connects to the database on > port 5434 > * you/your app's should connect to the server where pgpool is running on port > 5432 (the connection to the database is established by pgpool) > > hth...::GERD::... So I've got 13 different databases on 13 different postmasters, now does pgpool know which databases I'm trying to connect to? > > > On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: >> So, I've got it installed, and tweaked the configuration, but I simply >> can not figure out how to connect to my databases via pgpool. Is this >> simply transparent? I don't see how. >> >> So I have a postmaster running on port 5434, how do I connect to that >> database via pgpool? I simply can not find this piece of info in the docs? > -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Gerd Koenig wrote: > Hi Geoffrey, > > you do not need to connect to your database directly, just connect to pgpool > itself. > e.g.: your database runs on port 5434, pgpool runs on port 5432 > => > * pgpool has to be configured in that way that it connects to the database on > port 5434 What parameter in the config file is this? I can't seem to locate it? > * you/your app's should connect to the server where pgpool is running on port > 5432 (the connection to the database is established by pgpool) > > hth...::GERD::... > > > On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: >> So, I've got it installed, and tweaked the configuration, but I simply >> can not figure out how to connect to my databases via pgpool. Is this >> simply transparent? I don't see how. >> >> So I have a postmaster running on port 5434, how do I connect to that >> database via pgpool? I simply can not find this piece of info in the docs? > -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Geoffrey wrote: > Gerd Koenig wrote: >> Hi Geoffrey, >> >> you do not need to connect to your database directly, just connect to >> pgpool itself. >> e.g.: your database runs on port 5434, pgpool runs on port 5432 >> => >> * pgpool has to be configured in that way that it connects to the >> database on port 5434 > > What parameter in the config file is this? I can't seem to locate it? I mis-read the docs, figured this out, thanks. > >> * you/your app's should connect to the server where pgpool is running >> on port 5432 (the connection to the database is established by pgpool) >> >> hth...::GERD::... >> >> >> On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: >>> So, I've got it installed, and tweaked the configuration, but I simply >>> can not figure out how to connect to my databases via pgpool. Is this >>> simply transparent? I don't see how. >>> >>> So I have a postmaster running on port 5434, how do I connect to that >>> database via pgpool? I simply can not find this piece of info in the >>> docs? >> > > -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
So I've got the following: port = 9999 . . backend_hostname0 = 'localhost' backend_port0 = 5434 backend_weight0 = 1 backend_data_directory0 = '/data/pgsql/master' backend_hostname1 = 'localhost' backend_port1 = 5435 backend_weight1 = 1 backend_data_directory1 = '/data/pgsql/mwv' In my pgpool.conf file and I've restarted the pgpool processes. I can connect to the first entry as follows: psql -p 9999 master But if I attempt to connect to the second postmaster as follows: psql -p 9999 mwv I can not connect. What am I missing? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Le 21/06/2010 15:52, Geoffrey a écrit : > So I've got the following: > > port = 9999 > . > . > backend_hostname0 = 'localhost' > backend_port0 = 5434 > backend_weight0 = 1 > backend_data_directory0 = '/data/pgsql/master' > backend_hostname1 = 'localhost' > backend_port1 = 5435 > backend_weight1 = 1 > backend_data_directory1 = '/data/pgsql/mwv' > > In my pgpool.conf file and I've restarted the pgpool processes. I can > connect to the first entry as follows: > > psql -p 9999 master > > But if I attempt to connect to the second postmaster as follows: > > psql -p 9999 mwv > > I can not connect. What am I missing? > You can't. backend_hostnamen, with n greater then 0, is only used in replication, and load balancing mode. If you want to be able to connect on various databases from various servers, pgBouncer is what you're looking for. -- Guillaume http://www.postgresql.fr http://dalibo.com
Guillaume Lelarge wrote: > Le 21/06/2010 15:52, Geoffrey a écrit : >> So I've got the following: >> >> port = 9999 >> . >> . >> backend_hostname0 = 'localhost' >> backend_port0 = 5434 >> backend_weight0 = 1 >> backend_data_directory0 = '/data/pgsql/master' >> backend_hostname1 = 'localhost' >> backend_port1 = 5435 >> backend_weight1 = 1 >> backend_data_directory1 = '/data/pgsql/mwv' >> >> In my pgpool.conf file and I've restarted the pgpool processes. I can >> connect to the first entry as follows: >> >> psql -p 9999 master >> >> But if I attempt to connect to the second postmaster as follows: >> >> psql -p 9999 mwv >> >> I can not connect. What am I missing? >> > > You can't. backend_hostnamen, with n greater then 0, is only used in > replication, and load balancing mode. > > If you want to be able to connect on various databases from various > servers, pgBouncer is what you're looking for. Ouch, okay, thanks for the clarification. I simply did not pick up on that in the docs. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Guillaume Lelarge wrote: > If you want to be able to connect on various databases from various > servers, pgBouncer is what you're looking for. It does not appear that pgbouncer will 'limit exceeding connections' as does pgpool. So if I have a pool of 20 connections and 20 connections are used, what happens to the 21st connection attempt? Is it rejected or put into a queue to wait for the next available connection? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
On 06/21/10 5:37 AM, Geoffrey wrote: > So I've got 13 different databases on 13 different postmasters, now > does pgpool know which databases I'm trying to connect to? you would need 13 different connection pools.
John R Pierce wrote: > On 06/21/10 5:37 AM, Geoffrey wrote: >> So I've got 13 different databases on 13 different postmasters, now >> does pgpool know which databases I'm trying to connect to? > > you would need 13 different connection pools. Can this be done? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey <lists@serioustechnology.com> wrote: > John R Pierce wrote: >> >> On 06/21/10 5:37 AM, Geoffrey wrote: >>> >>> So I've got 13 different databases on 13 different postmasters, now does >>> pgpool know which databases I'm trying to connect to? >> >> you would need 13 different connection pools. > > Can this be done? Yes
Scott Marlowe wrote: > On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey <lists@serioustechnology.com> wrote: >> John R Pierce wrote: >>> On 06/21/10 5:37 AM, Geoffrey wrote: >>>> So I've got 13 different databases on 13 different postmasters, now does >>>> pgpool know which databases I'm trying to connect to? >>> you would need 13 different connection pools. >> Can this be done? > > Yes Can you point me to any docs that explain how this is done? As there is only one pgpool.conf file, I'm wondering how to go about that process. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey <lists@serioustechnology.com> wrote:
> Scott Marlowe wrote:
>>
>> On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey <lists@serioustechnology.com>
>> wrote:
>>>
>>> John R Pierce wrote:
>>>>
>>>> On 06/21/10 5:37 AM, Geoffrey wrote:
>>>>>
>>>>> So I've got 13 different databases on 13 different postmasters, now
>>>>> does
>>>>> pgpool know which databases I'm trying to connect to?
>>>>
>>>> you would need 13 different connection pools.
>>>
>>> Can this be done?
>>
>> Yes
>
> Can you point me to any docs that explain how this is done? As there is
> only one pgpool.conf file, I'm wondering how to go about that process.
sudo apt-get install pgpool2
(password, yes, wait...)
pgpool --help
Usage:
pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
[ -n ] [ -d ]
pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
[ -m SHUTDOWN-MODE ] stop
pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload
Common options:
-a HBA_CONFIG_FILE Sets the path to the pool_hba.conf configuration file
(default: /etc/pool_hba.conf)
-f CONFIG_FILE Sets the path to the pgpool.conf configuration file
(default: /etc/pgpool.conf)
-F PCP_CONFIG_FILE Sets the path to the pcp.conf configuration file
(default: /etc/pcp.conf)
-h Prints this help
Looks like there's a -f and -F option to use various files.
Admittedly, you might not be starting it up with /etc/init.d/pgpool2
each time now. But the basics are there.
Scott Marlowe wrote: > On Mon, Jun 21, 2010 at 2:42 PM, Geoffrey <lists@serioustechnology.com> wrote: >> Scott Marlowe wrote: >>> On Mon, Jun 21, 2010 at 1:45 PM, Geoffrey <lists@serioustechnology.com> >>> wrote: >>>> John R Pierce wrote: >>>>> On 06/21/10 5:37 AM, Geoffrey wrote: >>>>>> So I've got 13 different databases on 13 different postmasters, now >>>>>> does >>>>>> pgpool know which databases I'm trying to connect to? >>>>> you would need 13 different connection pools. >>>> Can this be done? >>> Yes >> Can you point me to any docs that explain how this is done? As there is >> only one pgpool.conf file, I'm wondering how to go about that process. > > sudo apt-get install pgpool2 > (password, yes, wait...) > pgpool --help > Usage: > pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] > [ -n ] [ -d ] > pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] > [ -m SHUTDOWN-MODE ] stop > pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload > > Common options: > -a HBA_CONFIG_FILE Sets the path to the pool_hba.conf configuration file > (default: /etc/pool_hba.conf) > -f CONFIG_FILE Sets the path to the pgpool.conf configuration file > (default: /etc/pgpool.conf) > -F PCP_CONFIG_FILE Sets the path to the pcp.conf configuration file > (default: /etc/pcp.conf) > -h Prints this help > > Looks like there's a -f and -F option to use various files. > Admittedly, you might not be starting it up with /etc/init.d/pgpool2 > each time now. But the basics are there. Thanks muchly, that should give me a good kick start. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
A couple of questions regarding pgpool: Is there a problem with using multiple connection pools for the same database? Point being, we might want to give a higher number of connections to one group of users then another. I can see doing this by having separate connection pools. The higher priority users have a larger connection pool. Is there a problem with using connection pooling and traditional connections to connect to the same database? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
On Tue, Jun 22, 2010 at 07:07:33AM -0400, Geoffrey wrote: > A couple of questions regarding pgpool: > > Is there a problem with using multiple connection pools for the same > database? Point being, we might want to give a higher number of > connections to one group of users then another. I can see doing this by > having separate connection pools. The higher priority users have a > larger connection pool. That's entirely reasonable. > Is there a problem with using connection pooling and traditional > connections to connect to the same database? Nope. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com