Last modified: Wed Sep 6 13:53:04 JST 2006

pgpool-II Tutorial

Welcome to the Tutorial for pgpool-II. From here, you can learn how to install, setup, and run parallel queries or do replication using pgpool-II. We assume that you already know basic PostreSQL operations, so please refer to the PostgreSQL document if needed.

Table of Contents
1. Let's Begin!
1.1. Installing pgpool-II
1.2. Configuration Files
1.3. Configuring PCP commands
1.4. Preparing Database Nodes
1.5. Starting/Stopping pgpool-II
2. Your First Replication
2.1. Configuring Replication
2.2. Checking Replication
3. Your First Parallel Query
3.1. Configuring Parallel Query
3.2. Configuring the System Database
3.3. Partitioning Rule Definition
3.4. Checking Parallel Query

1. Let's Begin!

First, we must learn how to install, configure pgpool-II and database nodes before using replication or parallel query.

1.1. Installing pgpool-II

Installing pgpool-II is very easy. In the directory which you have extracted the source tar ball, execute the following commands.

$ ./configure
$ make
$ make install

configure script collects your system information and use it for the compilation procedure. You can pass command line arguments to configure script to change the default behavior, such as the installation directory. pgpool-II will be installed to /usr/local directory by default.

make command compiles the source code, and make install will install the executables. You must have write permission on the installation directory.

In this tutorial, we will install pgpool-II in the default /usr/local directory.

Note: pgpool-II requires libpq library in PostgreSQL 7.4 or later (version 3 protocol). If configure script displays the following error message, libpq library may not be installed, or it is not of version 3.

configure: error: libpq is not installed or libpq is old

If the library is version 3, but above message is still displayed, your libpq library is probably not recognized by configure script.

configure script searches for libpq library under /usr/local/pgsql libaray. If you have installed PostgreSQL to a directory other than /usr/local/pgsql, use --with-pgsql, or --with-pgsql-includedir and --with-pgsql-libdir command line options when you execute configure.

1.2. Configuration Files

pgpool-II configuration parameters are saved in pgpool.conf file. The file is in "parameter = value" per line format. When you install pgpool-II, pgpool.conf.sample is automatically created. We recommend copying and renaming it to pgpool.conf, and edit it as you like.

$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf

pgpool-II only accepts connections from the local host using port 9999. If you wish to receive conenctions from other hosts, set listen_addresses to '*'.

listen_addresses = 'localhost'
port = 9999

We will use the default parameters in thie tutorial.

1.3. Configuring PCP Commands

pgpool-II has an interface for administration purpose to retrieve information on database nodes, shutdown pgpool-II, etc. via network. To use PCP commands, user authentication is required. This authentication is different from PostgreSQL's user authentication. A username and password need to be defined in pcp.conf file. In the file, a username and password are listed as a pair on each line, and they are separated by a colon (:). Passwords are encrypted in md5 hash format.

postgres:e8a48653851e28c69d0506508fb27fc5
When you install pgpool-II, pcp.conf.sample is automatically created. We recommend copying and renaming it to pcp.conf, and edit it.
$ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf

To encrypt your password into md5 hash format, use pg_md5 command, which is installed as a part of pgpool-II executables. pg_md5 takes text as an command line argument, and displays its md5-hashed text.

For example, give "postgres" as the command line argument, at pg_md5 displays md5-hashed text to the standard output.

$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

PCP commands are executed via network, so the port number must be configured with pcp_port parameter in pgpool.conf file.

We will use the default 9898 for pcp_port in this tutorial.

pcp_port = 9898

1.4. Preparing Database Nodes

Now, we need to set up backend PostgreSQL servers for pgpool-II. These servers can be placed within the same host as pgpool-II, or on separate machines. If you decide to place the servers on the same host, different port numbers must be assigned for each server. If the servers are placed on separate machines, they must be configured properly so that they can accept network connections from pgpool-II.

In this tutorial, we will place three servers within the same host as pgpool-II, and assign 5432, 5433, 5434 port numbers respectively. To configure pgpool-II, edit pgpool.conf as follows.

backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_hostname2 = 'localhost'
backend_port2 = 5434
backend_weight2 = 1

For backend_hostname, backend_port, backend_weight, set the node's hostname, port number, and ratio for load balancing. At the end of each parameter string, node ID must be specified by adding positive integers starting with 0 (i.e. 0, 1, 2, …).

backend_weight parameters are all 1, meaning that SELECT queries are equally distributed among three servers.

1.5. Starting/Stopping pgpool-II

To fire up pgpool-II, execute the following command on a terminal.

$ pgpool

The above command, however, prints log messages on the terminal, so the recommended options to use are like the following.

$ pgpool -n -d > /tmp/pgpool.log 2>&1 &

When -n option is passed, pgpool-II is executed as non-daemon process, and the terminal will not be detached. This option is required to redirect log messages to a file. -d option enables debug messages to be generated.

To stop pgpool-II process, execute the following command.

$ pgpool stop

If any client is still connected, pgpool-II waits for them to disconnect, and then terminate itself. Execute the following command instead if you want to shutdown pgpool-II forcibly.

$ pgpool -m fast stop

2. Your First Replication

Replication enables the same data to be copied to multiple database nodes.

In this section, we'll use three database nodes, which we have already set up in section "1. Let's Begin!", and takes you step by step to create a database replication system. Sample data to be replicated will be generated by pgbench benchmark program.

2.1. Configuring Replication

To enable the database replication function, set replication_mode to true in pgpool.conf file.

replication_mode = true

When replication_mode is set to true, pgpool-II will send a copy of a received query to all the database nodes.

When load_balance_mode is set to true, pgpool-II will distribute SELECT queries among the database nodes.

load_balance_mode = true

In this section, we enable both replication_mode and load_balance_mode.

2.2. Checking Replication

To reflect the changes in pgpool.conf, pgpool-II must be restarted. Please refer to section "1.5 Starting/Stopping pgpool-II".

After configuring pgpool.conf and restarting pgpool-II, let's try the actual replication and see if everything is working OK.

First, we need to create a database to be replicated. We will name it "bench_replication". This database needs to be created on all the nodes. Use createdb commands through pgpool-II, and the database will be created on all the nodes.

$ createdb -p 9999 bench_replication

Then, we'll execute pgbench with -i option. -i option initializes the database with pre-defined tables and data.

$ pgbench -i -p 9999 bench_replication

The following table is the summary of tables and data, which will be created by pgbench -i. If, on all the nodes, the listed tables and data are created, replication is working correctly.

Table Name Number of Rows
branches 1
tellers 10
accounts 100000
history 0

Let's use a simple shell script to check the above on all the nodes. The following script will display the number of rows in branches, tellers, accounts, and history tables on all the nodes (5432, 5433, 5434).

$ for port in 5432 5433 5434; do
>     echo $port
>     for table_name in branches tellers accounts history; do
>         echo $table_name
>         psql -c "SELECT count(*) FROM $table_name" -p $port bench_replication
>     done
> done

3. Your First Parallel Query

As we have seen in the previous section, replication copies the queries so that each database node will hold the exact copy. Parallel query, on the other hand, distributes particular ranges of the data to each node according to pre-defined rules.

To enable parallel query in pgpool-II, you must set up another database called "System Database" (we will denote it as SystemDB from this point).

SystemDB holds the user-defined rules to decide what data will be saved in which database node. Another use of SystemDB is to merge results sent back from the database nodes using dblink.

In this section, we will use three database nodes which we have set up in section "1. Let's Begin!", and takes you step by step to create a parallel query database system. We will use pgbench again to create sample data.

3.1. Configuring Parallel Query

To enable the parallel query function, set parallel_mode to true in pgpool.conf file.

parallel_mode = true

Setting paralle_mode to true does not start parallel query automatically. pgpool-II needs SystemDB and the rules to know how to distribute data to the database nodes.

Also, dblink used by SystemDB makes connections to pgpool-II. Therefore, listen_addresses needs to be configured so that pgpool-II accepts those connections.

listen_addresses = '*'

Note: Parallel query and replication cannot be enabled at the same time. To use the parallel query function, replication_mode must be set to fales. Also, parallel query and replication store data in different format; therefore, "bench_replication" created in section "2. Your First Replication" cannot be reused.

replication_mode = false
load_balance_mode = false

In this section, we will set parallel_mode to true, listen_addresses to '*', replication_mode and load_balance_mode to false.

3.2. Configuring SystemDB

SystemDB is just another PostgreSQL database with dblink installed, and "dist_def" table to hold the distribution rules is defined. You can place SystemDB in a separate machine, or it can co-exist within one of the database nodes.

In this section, we will create SystemDB within the 5432 port node. The following list is the configuration parameters for SystemDB

system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''

Actually, the above are the default settings of pgpool.conf. Now, we must create a user called "pgpool", and a database called "pgpool" owned by user "pgpool".

$ createuser -p 5432 pgpool
$ createdb -p 5432 -O pgpool pgpool

3.2.1. Installing dblink

Next, we must install dblink into "pgpool" database. dblink is one of the tools included in contrib directory in the PostgreSQL source code.

To install dblink to your system, execute the following commands.

$ USE_PGXS=1 make -C contrib/dblink
$ USE_PGXS=1 make -C contrib/dblink install

After dblink has been installed into your system, we will define dblink functions in "pgpool" database. If PostgreSQL is installed in /usr/local/pgsql, dblink.sql (a file with function definitions) should have been installed in /usr/local/pgsql/share/contrib. Now, execute the following command to define dblink functions.

$ psql -f /usr/local/pgsql/share/contrib/dblink.sql -p 5432 pgpool

3.2.2. Defining dist_def table

Next, we will define a table called "dist_def" to hold the distribution rules. When pgpool-II was installed, a file called system_db.sql should have been installed in /usr/local/share/system_db.sql (note that in this tutorial, we are using the default installation directory, /usr/local). systeym_db.sql contains definitions to create special tables including "dist_def" table. Execute the following command to define "dist_def" table.

$ psql -f /usr/local/share/system_db.sql -p 5432 -U pgpool pgpool

In system_db.sql, tables including "dist_def" are installed in "pgpool_catalog" schema. If you have configured system_db_schema to use other schema, you need to edit system_db.sql accordingly.

The definition for "dist_def" is as shown here, and the table name cannot be changed.

CREATE TABLE pgpool_catalog.dist_def (
    dbname text, -- database name
    schema_name text, -- schema name
    table_name text, -- table name
    col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- distribution key-column
    col_list text[] NOT NULL, -- list of column names
    type_list text[] NOT NULL, -- list of column types
    dist_def_func text NOT NULL, -- distribution function name
    PRIMARY KEY (dbname, schema_name, table_name)
);

A tuple stored in "dist_def" can be classified into two types.

A distribution rule decides how to distribute data to a particular node. Data will be distributed depending on the value of "col_name" column. "dist_def_func" is a function that takes the value of "col_name" as its argument, and returns an integer which points to the appropriate database node ID where the data should be stored.

A meta-information is used to rewrite queries. Parallel query must rewrite queries so that the results sent back from the backend nodes can be merged into one result.

3.3. Defining Distribution Rules

In this tutorial, we will define rules to distribute pgbench's sample data into three database nodes. The sample data will be created by "pgbench -i -s 3" (i.e. scale factor of 3). We will create a new database called "bench_parallel" for this section.

In pgpool-II's source code, you can find dist_def_pgbench.sql file in sample directoy. We will use this sample file here to create distribution rules for pgbench. Execute the following command in extracted pgpool-II source code directory.

$ psql -f sample/dist_def_pgbench.sql -p 5432 pgpool

Here is the explanation of dist_def_pgbench.sql.

Inside dist_def_pgbench.sql, we are inserting four rows into "dist_def" table. There are four different distribution functions for each table (accounts, branches, tellers, and history). For key-columns, bid, tid, and aid are defined for branches, tellers, and accounts respectively (all of which are primary keys), and for history, tid is the key-column.

INSERT INTO pgpool_catalog.dist_def VALUES (
    'bench_parallel',
    'public',
    'branches',
    'bid',
    ARRAY['bid', 'bbalance', 'filler'],
    ARRAY['integer', 'integer', 'character(88)'],
    'pgpool_catalog.dist_def_branches'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
    'bench_parallel',
    'public',
    'tellers',
    'tid',
    ARRAY['tid', 'bid', 'tbalance', 'filler'],
    ARRAY['integer', 'integer', 'integer', 'character(84)'],
    'pgpool_catalog.dist_def_tellers'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
    'bench_parallel',
    'public',
    'accounts',
    'aid',
    ARRAY['aid', 'bid', 'abalance', 'filler'],
    ARRAY['integer', 'integer', 'integer', 'character(84)'],
    'pgpool_catalog.dist_def_accounts'
);

INSERT INTO pgpool_catalog.dist_def VALUES (
    'bench_parallel',
    'public',
    'history',
    'tid',
    ARRAY['tid', 'bid', 'aid', 'delta', 'mtime', 'filler'],
    ARRAY['integer', 'integer', 'integer', 'integer', 'timestamp without time zone', 'character(22)'],
    'pgpool_catalog.dist_def_history'
);

Now, we must define the distribution functions for each table. Note that you can use the same function from different tables. Also, you can define functions using languages other than SQL (e.g. PL/pgSQL, PL/Tcl, etc.).

The following table is the summary of tables and data, which will be created by pgbench -i -s 3.

Table Name Number of Rows
branches 3
tellers 30
accounts 300000
history 0

We are going to define four functions which distribute the shown data equally among three database nodes. These functions return 0, 1, or 2 depending on the given argument.

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)
RETURNS integer AS $$
    SELECT CASE WHEN $1 > 0 AND $1 <= 1 THEN 0
        WHEN $1 > 1 AND $1 <= 2 THEN 1
        ELSE 2
    END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_tellers(anyelement)
RETURNS integer AS $$
    SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0
        WHEN $1 > 10 AND $1 <= 20 THEN 1
        ELSE 2
    END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement)
RETURNS integer AS $$
    SELECT CASE WHEN $1 > 0 AND $1 <= 100000 THEN 0
        WHEN $1 > 100000 AND $1 <= 200000 THEN 1
        ELSE 2
    END;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_history(anyelement)
RETURNS integer AS $$
    SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0
        WHEN $1 > 10 AND $1 <= 20 THEN 1
        ELSE 2
    END;
$$ LANGUAGE sql;

3.4. Checking Parallel Query

To reflect the changes in pgpool.conf, pgpool-II must be restarted. Please refer to section "1.5 Starting/Stopping pgpool-II".

After configuring pgpool.conf and restarting pgpool-II, let's try and see if parallel query is working OK.

First, we need to create a database to be distributed. We will name it "bench_parallel". This database needs to be created on all the nodes. Use createdb commands through pgpool-II, and the database will be created on all the nodes.

$ createdb -p 9999 bench_parallel

Then, we'll execute pgbench with -i -s 3 options. -i option initializes the database with pre-defined tables and data. -s option specifies the scale factor for initialization.

$ pgbench -i -s 3 -p 9999 bench_parallel

The tables and data created are shown in "3.3. Defining Distribution Rules".

One way to check if the data have been distributed correctly is to execute a SELECT query via pgpool-II and directly on the backend, and compare two results. If everything is configured right, "bench_parallel" should be distributed as follows.

Table Name Key-Column Name Values
Node 1 Node 2 Node 3
branches bid 1 2 3
tellers tid 1 - 10 11 - 20 21 - 30
accounts aid 1 - 100000 100001 - 200000 200001 - 300000
history tid 1 - 10 11 - 20 21 - 30

Let's use a simple shell script to check the above on all the nodes and via pgpool-II. The following script will display the minimum and maximum values in accounts table using port 5432, 5433, 5434, and 9999.

$ for port in 5432 5433 5434 9999; do
>     echo $port
>     psql -c "SELECT min(aid), max(aid) FROM accounts" -p $port bench_parallel
> done