Database Reference
In-Depth Information
In load-balancing mode, pgpool makes intelligent routing decisions based upon the type of
SQL statement.
SQL Statement type
Master
Standby
Straightforward SELECT
Load is split between Master and Standby, if possible
Other/Non-SELECT
Sent to Master only
Never sent to Standby
Straightforward
SELECT
statements are easily load balanced.
Straightforward
is
defined here as being a
SELECT
statement that:
F
is issued as a top-level
SELECT
, so selects issued within functions do not get replicated
F
is not issued as a part of an explicit/extended transaction
F
doesn't use row-level locking:
SELECT
…
FOR
SHARE
/
UPDATE
F
doesn't use
nextval()
or
setval()
functions
F
doesn't access volatile functions
F
doesn't access system catalog tables
You can look for further details of pgpool at the following URL:
http://pgfoundry.org/projects/pgpool/
Other pgpool features are as follows:
F
Standby servers can be added without restarting pgpool
F
Pgpool can also be used to automate failover
F
A query cache is available in all modes
How to do it...
If using Pgpool with streaming log replication, then Hot Standby must be enabled.
We setup pgpool, and then point the applications at the pgpool server, which responds to them
like a PostgreSQL server. Pgpool then connects to the Master and Standbys. You can use a
pool_hba.conf
file to control access, just as with the normal server's
pg_hba.conf
file.
You'll need the following settings in the
pgpool.conf.sample-stream
:
replication_mode = false
Master_slave = true
replicate_select = false
load_balance_mode = true
These purely set up the load-balancing mode. In addition, pgpool supports the following
directives:
