pgpool-II 入門教程-有解無憂

歡迎閱讀 pgpool-II 入門教程，從本教程中，你將學會如何安裝，設定 pgpool-II 以及使用 pgpool-II 運行并行查詢和復制，我們假設你已經知道PostgreSQL的基礎操作，所以如果有必要，請先參考 PostgreSQL 的檔案，

目錄
1. 讓我們開始吧！: 1.1. 安裝 pgpool-II; 1.2. 組態檔; 1.3. 配置 PCP 命令; 1.4. 準備資料庫節點; 1.5. 啟動/停止 pgpool-II
2. 你的第一個復制: 2.1. 配置復制; 2.2. 檢查復制
3. 你的第一個并行查詢: 3.1. 配置并行查詢; 3.2. 配置系統資料庫; 3.3. 定義分發規則; 3.4. 定義復制規則; 3.5. 檢查并行查詢

1. 讓我們開始吧！

首先，在使用復制和并行查詢前，我們必須學習如何安裝、配置 pgpool-II 和資料庫節點，

1.1. 安裝 pgpool-II

安裝 pgpool-II 非常簡單，在你解壓原始碼 tar 包的目錄中，執行以下命令，

$ ./configure
$ make
$ make install

configure 腳本收集你的系統資訊并用于編譯程序，你可以通過傳遞命令列引數到 configure 腳本來改變它的默認行為，例如安裝目錄，默認情況下，pgpool-II將安裝到 /usr/local 目錄，

make 命令編譯原始碼，make install 命令將安裝執行檔案，你必須對安裝目錄有寫入權限，

在本教材中，我們將安裝 pgpool-II 到默認的 /usr/local 目錄中，

注: pgpool-II 需要 PostgreSQL 7.4 或更高版本（版本 3 的協議）的 libpq ，如果 configure 腳本顯示以下的錯誤資訊，則可能是 libpq 庫沒有被安裝或者它不是版本 3 的，

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

如果是版本 3 的庫，但還是顯示了以上訊息，則你的 libpq 庫可能無法被 configure 腳本識別，

configure 腳本會在 /usr/local/pgsql 目錄搜索 libpq 庫，如果你安裝 PostgreSQL 到 /usr/local/pgsql 之外的其他目錄，在執行 configure 時，請使用 --with-pgsql ，或者同時使用 --with-pgsql-includedir 和 --with-pgsql-libdir 的命令列選項，

1.2. 組態檔

pgpool-II 的配置引數保存在 pgpool.conf 檔案中，檔案以每行 “parameter = value” 的格式保存，當你安裝 pgpool-II 后， pgpool.conf.sample 被自動建立，我們建議拷貝或者重命名它為 pgpool.conf ，然后你可以隨意編輯它，

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

pgpool-II 默認只接受到 9999 埠的本地連接，如果你希望從其他主機接受連接，請設定 listen_addresses 為 '*'.

listen_addresses = 'localhost'
port = 9999

在本教程中我們將使用默認引數，

1.3. 配置 PCP 命令

pgpool-II 有一個用于管理功能的介面，用于通過網路獲取資料庫節點資訊、關閉 pgpool-II 等，要使用 PCP 命令，必須進行用戶認證，這種認證和 PostgreSQL 的用戶認證不同，這需要在 pcp.conf 檔案中定義一個用戶和密碼，在這個檔案中，一個用戶名和密碼成對地出現在每一行中，它們用冒號（:）隔開，密碼為用 md5 哈希加密的格式，

postgres:e8a48653851e28c69d0506508fb27fc5

在你安裝 pgpool-II時， pcp.conf.sample 被自動建立，我們建議復制一份并重命名為 pcp.conf 并且編輯它，

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

為了加密你的密碼為 md5 哈希格式，請使用 pg_md5 命令，它作為 pgpool-II 執行檔案套件的一部分被安裝，pg_md5 使用一個命令列引數，并顯示它的 md5 哈希文本，

例如，使用 “postgres” 作為命令列引數，pg_md5 會顯示 md5 哈希的文本到標準輸出，

$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

PCP 命令通過網路執行，所以必須在 pgpool.conf 檔案中配置 pcp_port 埠，

在本教程中，我們將使用默認的 9898 作為 pcp_port 的引數，

pcp_port = 9898

1.4. 準備資料庫節點

現在，我們需要設定用于 pgpool-II 的后臺 PostgreSQL 服務器了，這些服務器可以與 pgpool-II 在同一臺主機上，也可以在獨立的主機上，如果你決定將所有服務器都放在同一臺主機上，必須為每個服務分配不同的埠，如果服務器被安置在不同的機器上，他們必須被正確以便可以通過網路接受 pgpool-II 的連接，

在本教程中，我們將三臺服務器放在與 pgpool-II 相同的機器上，分別給它們分配埠號 5432，5433，5434，要配置 pgpool-II，請如下編輯 pgpool.conf，

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

分別為 backend_hostname，backend_port，backend_weight 設定節點的主機名，埠號和負載均衡系數，在每個引數串的后面，必須通過添加從0開始（例如 0，1，2，…）的整數來指出節點編號，

backend_weight 引數都為 1 ，這意味著 SELECT 查詢被平均分配到三臺服務器上，

1.5. 啟動/停止 pgpool-II

要啟動 pgpool-II，在終端中執行以下命令，

$ pgpool

然而，以上的命令不列印日志資訊，因為 pgpool 脫離終端了，如果你想顯示 pgpool 日志資訊，你需要傳遞 -n 到 pgpool 命令，此時 pgpool-II 作為非守護行程模式運行，也就不會脫離終端了，

$ pgpool -n &

日志訊息會列印到終端，所以推薦使用如下的選項，

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

-d 選項啟用除錯資訊生成，

以上命令持續追加日志訊息到 /tmp/pgpool.log 中，如果你需要切換日志檔案，可以將日志傳遞到一個支持日志輪換功能的外部命令，例如，你可以使用 Apache2 帶的 rotatelogs 工具，

$ pgpool -n 2>&1 | /usr/local/apache2/bin/rotatelogs \
  -l -f /var/log/pgpool/pgpool.log.%A 86400 &

這將生成名稱類似于 “pgpool.log.Thursday” 的日志檔案，然后在每天午夜 00:00 輪換日志檔案，如果日志檔案已經存在，rotatelogs 將追加日志到這個檔案中，如果想在輪換前洗掉舊日志檔案，你可以使用 cron：

55 23 * * * /usr/bin/find /var/log/pgpool -type f -mtime +5 -exec /bin/rm -f '{}' \;

請注意 rotatelogs 在某些發行版中可能存在于 /usr/sbin/rotatelogs2 ，-f 選項讓 rotatelogs 在啟動的時候生成一個日志檔案，這個功能隨 apache 2.2.9 或更高版本提供，

而且 cronolog 對你可能有用，

$ pgpool -n 2>&1 | /usr/sbin/cronolog \
  --hardlink=/var/log/pgsql/pgpool.log \
  '/var/log/pgsql/%Y-%m-%d-pgpool.log' &

要停止 pgpool-II 行程，執行以下的命令，

$ pgpool stop

如果還有客戶端連接著， pgpool-II 等待它們斷開連接，然后才結束運行，如果你想強制關閉 pgpool-II ，執行以下的命令來代替之前的命令，

$ pgpool -m fast stop

2. 你的第一個復制

復制可以讓相同的資料拷貝到多個資料庫節點，

在本節中，我們將使用三個已經在“1. 讓我們開始吧！”小節中設定好了的資料庫節點，一步步建立資料庫復制系統，用于復制的示例資料將由 pgbench 基準測驗程式生成，

2.1. 配置復制

要啟用資料庫復制功能，需要設定 pgpool.conf 檔案中的 replication_mode 為 true ，

replication_mode = true

當 replication_mode 被設定為 true，pgpool-II 將發送一份接收到的查詢的拷貝到所有的資料庫節點，

當 load_balance_mode 被設定為 true，pgpool-II 將在資料庫節點之間分發 SELECT 查詢，

load_balance_mode = true

在本章中，我們將同時啟用 replication_mode 和 load_balance_mode ，

2.2. 檢查復制

要使 pgpool.conf 中的改動生效，必須重啟 pgpool-II ，請參考“1.5 啟動/停止 pgpool-II”小節，

在配置完 pgpool.conf 且重新啟動 pgpool-II 后，讓我們試試復制，檢查它是否作業正常，

首先，我們需要建立一個用于復制的資料庫，我們將給它命名為“bench_replication”，這個資料庫需要被建立在所有的節點中，在 pgpool-II 中使用 createdb 命令，資料庫將被在所有節點中建立，

$ createdb -p 9999 bench_replication

然后，使用 -i 選項執行 pgbench，-i 使用預設的表和資料初始化這個資料庫，

$ pgbench -i -p 9999 bench_replication

下表是通過 pgbench -i 建立的表和資料的摘要，如果在所有的節點中，列出的表和資料都被建立了，則說明復制運行正常，

表名	行數
branches	1
tellers	10
accounts	100000
history	0

讓我們使用一個簡單的 shell 腳本在所有節點中檢查以上內容，以下腳本將顯示所有的節點中 branches，tellers，accounts 和 history 表的行數，

$ 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. 你的第一個并行查詢

在并行查詢中，不同范圍的資料存盤在兩個或更多資料庫節點中，這叫做磁區，而且，即使在并行查詢模式中，你也可以在資料庫節點之間對一些表進行復制，

為了在 pgpool-II 中啟用并行查詢，你必須設定另一個叫做“系統資料庫”的資料庫（本系統資料庫為 pgpool-II 使用的用于存放資訊的資料庫），

系統資料庫包含用戶定義的用來確定資料將包含到哪個資料庫中的規則，另一個系統資料庫的用途是使用 dblink 將從資料庫節點發回的資料合并，

在本章，我們將使用我們在“1. 讓我們開始吧！”小節中設定好的三個資料庫節點，并帶領你一步步建立一個并行查詢資料庫系統，我們同樣會再次使用 pgbench 來創建示例資料，

3.1. 配置并行查詢

要啟用并行查詢功能，請設定 pgpool.conf 檔案中的parallel_mode 為 true ，

parallel_mode = true

僅僅設定 paralle_mode 為 true 不會自動啟動并行查詢，pgpool-II 需要系統資料庫和用于分發資料到資料庫節點的規則，

而且，系統資料庫使用的 dblink 需要連接到 pgpool-II，因此，需要設定 listen_addresses 以便 pgpool-II 接受這些連接，

listen_addresses = '*'

注意：進行了磁區的表不會進行復制，不過并行查詢和復制可以同時生效，

注意：你可以同時擁有磁區表和復制表，但是一個表不能同時被磁區和復制，因為磁區表和復制表的結構不同，“2. 你的第一個復制”小節中建立的“bench_replication”資料庫無法用于并行查詢模式，

replication_mode = true
load_balance_mode = false

或者

replication_mode = false
load_balance_mode = true

在本小節，我們將設定 parallel_mode 和 load_balance_mode 為 true，listen_addresses 為 '*'，replication_mode為 false ，

3.2. 配置系統資料庫

“系統資料庫”實際上就是一個普通的資料庫，只是必須在系統資料庫中安裝 dblink 函式和用于描述磁區規則的 dist_def 表，你可以讓系統資料庫存放于某個資料庫節點中，也可以通過級聯的方式配置 pgpool-II，讓多個資料庫節點擁有系統資料庫，

在本小節，我們將在 5432 埠的節點建立 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 = ''

實際上，以上為 pgpool.conf 的默認設定，現在，我們必須建立一個叫做“pgpool”的用戶，并建立一個屬主為“pgpool”的名為“pgpool”的資料庫，

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

3.2.1. 安裝 dblink

然后，我們必須在“pgpool”資料庫中安裝 dblink，dblink 是包含在 PostgreSQL 原始碼包 contrib 目錄中包含的一個工具，

要在你的系統中安裝 dblink，請執行以下命令，

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

在 dblink 被安裝到你的系統中后，我們將在“pgpool”資料庫中定義 dblink 函式，如果 PostgreSQL 被安裝到 /usr/local/pgsql，dblink.sql（一個包含函式定義的檔案）應該被安裝到了 /usr/local/pgsql/share/contrib，那么，執行以下的命令來定義 dblink 函式，

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

3.2.2. 定義 dist_def 表

在資料庫“pgpool”中定義一個“dist_def”表，用于保存磁區規則，在 pgpool-II 安裝后，你會得到一個 system_db.sql，它是一個可用于生成系統資料庫的 psql 腳本，

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

dist_def 表被建立到 pgpool_catalog 這個 schema 中，如果你配置 system_db_schema 使用了其他的 schema，你需要相應地編輯 system_db.sql，

“dist_def的定義如下，且表名不能被改變，

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)
);

“dist_def”中保存的 tuple 可以被分為兩類，

分發規則 (col_name, dist_def_func)
表的元資訊 (dbname, schema_name, table_name, col_list, type_list)

分發規則確定如何分發資料到特定節點，資料將依賴“col_name”的值來進行分發，“dist_def_func”是一個函式，它使用“col_name”列的值做引數，回傳一個指出資料將要存盤的資料庫節點 ID 的適當整數，

元資訊用于重寫查詢，并行查詢必須重寫查詢比便讓從后臺節點回傳的資料可以被合并為一個結果集，

3.2.2. 定義 replicate_def 表

如果你想在 SELECT 陳述句的并行模式中使用復制的表，你需要在一個叫做 replicate_def 的表中注冊這些表的資訊（復制規則），replicate_def 表在定義 dist_def 表的時候已經由 system_db.sql 創建，replicate_def 表的定義如下，

CREATE TABLE pgpool_catalog.replicate_def (
    dbname text, -- database name
    schema_name text, -- schema name
    table_name text, -- table name
    col_list text[] NOT NULL, -- list of column names
    type_list text[] NOT NULL, -- list of column types
    PRIMARY KEY (dbname, schema_name, table_name)
);

replicate_def 包含了表的元資料資訊 (dbname, schema_name, table_name, col_list, type_list)，

所有的查詢分析和查詢重寫程序都依賴于存盤于 dist_def 以及/或 replicate_def 表中的這些資訊（表，列以及型別），如果資訊不正確，分析和查詢重寫程序將導致錯誤的結果，

3.3. 定義分發規則

在本教材中，我們將定義規則用于分發 pgbench 的示例資料到三個資料庫節點中，示例資料將通過“pgbench -i -s 3”（例如比例因子為3）建立，在本小節，我們將建立一個叫“bench_parallel”的新資料庫，

在 pgpool-II 的原始碼中在，你可以在 sample 目錄中找到 dist_def_pgbench.sql 檔案，我們將使用這個示例檔案來為 pgbench 建立分發規則，在解壓后的 pgpool-II 的原始碼目錄中執行以下命令，

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

以下為 dist_def_pgbench.sql 的說明，

在 dist_def_pgbench.sql中，我們往“dist_def”表中插入一行，這里有一個為 accounts 表準備的分發函式，作為關鍵列，aid 被定義在每個 accounts 表中（也是主鍵），

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'
);

現在，我們必須為 accounts 表定義分發函式，注意你可以對不同的表使用相同的函式，而且，你可以使用 SQL 之外的語言定義函式（例如 PL/pgSQL，PL/Tcl 等等），

accounts 表在初始化時指定了比例因子為 3，aid 的值為 1 到 300000，函式被定義為使資料平均分布到三個資料庫節點中，

SQL 函式將定義為回傳資料庫節點的編號，

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;

3.4. 定義復制規則

復制規則是定義某個表是否被復制的規則，

以下表是 pgbench 生成的，branches 表和 tellers 表已經注冊，作為結果，accounts 表和使用 branches 表和 tellers 表的查詢已經可用，

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

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

在 sample 目錄中已經準備好了 replicate_def_pgbench.sql 檔案，在原始碼目錄中使用以下方法來定義一個復制規則，執行以下 psql 命令，

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

3.5. 檢查并行查詢

要使 pgpool.conf 中的改動生效，pgpool-II 必須重啟，請參考“1.5 啟動/停止 pgpool-II”小節，

在配置好 pgpool.conf 并重啟 pgpool-II 后，讓我們試試并查看并行查詢是否運行正常，

首先，我們需要建立一個用于分發的資料庫，我們將給它命名為“bench_parallel”，這個資料庫需要在所有節點上被建立，通過 pgpool-II 使用 createdb 命令，則資料庫將被獎勵在所有節點上，

$ createdb -p 9999 bench_parallel

然后，我們將使用 -i -s 3 選項執行 pgbench ，-i 選項使用預定義的表和資料初始化資料庫， -s 選項指出用于初始化的比例因子，

$ pgbench -i -s 3 -p 9999 bench_parallel

建立的表和資料在 “3.3. 定義分發規則”小節中列出來了，

檢查資料是否被正確分發的一個方法是通過 pgpool-II 和直接在后臺節點執行一個 SELECT 查詢，然后比較兩個結果，如果所有都配置正確，“bench_parallel”應該按以下樣子分布，

表名	行數
branches	3
tellers	30
accounts	300000
history	0

讓我們使用一段簡單的 shell 腳本來在所有的節點上和通過 pgpool-II 檢查以上的內容，以下腳本將使用 5432，5433,5434 和 9999 埠顯示 accounts 表中最小和最大的值，

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

入門教程

pgpool-II入門教程已發布，

pgpool-II用戶手冊

本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/pgpool_tutorial-zh_cn.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/353221.html

標籤：PostgreSQL

上一篇：[20211108]索引分裂塊清除日志增加(唯一索引)2.txt

下一篇：(二)《SQL進階教程》學習記錄--GROUP BY、PARTITION BY