pgpool

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::...

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