如何使用RDS的水平分割外掛程式 - ApsaraDB RDS

PL/Proxy外掛程式包含CLUSTER模式和CONNECT模式，可以協助您用不同方式訪問資料庫。

前提條件

請確保執行個體大版本滿足要求，本外掛程式具體支援的執行個體大版本，請參見支援外掛程式列表。
如執行個體大版本已滿足要求，但仍提示不支援，請升級核心小版本，具體操作，請參見升級核心小版本。

背景資訊

PL/Proxy外掛程式包含如下兩種模式：

CLUSTER模式
支援資料庫水平分割和SQL複製。
CONNECT模式
支援將SQL請求路由到指定的資料庫。

更多PL/Proxy外掛程式使用方法請參見PL/Proxy。

注意事項

相同VPC內的PostgreSQL執行個體可以直接跨庫操作。
不同VPC內的PostgreSQL執行個體可以通過本VPC內的ECS執行個體進行連接埠跳轉，實現跨庫操作。
代理節點後端的資料節點數必須是2的N次方。

測試環境

選擇一個資料庫執行個體作為代理節點，另外兩個資料庫執行個體作為資料節點。詳細資料如下。

IP	節點類型	資料庫名	使用者名稱
100.xx.xx.136	代理節點	postgres	postgres
100.xx.xx.72	資料節點	pl_db0	postgres
11.xx.xx.9	資料節點	pl_db1	postgres

建立PL/Proxy外掛程式

建立PL/Proxy外掛程式命令如下：

create extension plproxy

建立PL/Proxy叢集

說明

CONNECT模式不需要進行本操作。

建立PL/Proxy叢集，指定串連的子節點的資料庫名、IP地址和連接埠，樣本如下：

postgres=# CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy
postgres-# OPTIONS (
postgres(#         connection_lifetime '1800',
postgres(#         disable_binary '1',
postgres(#         p0 'dbname=pl_db0 host=100.xxx.xxx.72 port=5678',
postgres(#         p1 'dbname=pl_db1 host=11.xxx.xxx.9 port=5678'
postgres(#         );
CREATE SERVER

為postgres使用者賦予許可權，樣本如下：

postgres=# grant usage on FOREIGN server cluster_srv1 to postgres;
GRANT

建立使用者映射，樣本如下：

postgres=> create user mapping for postgres server cluster_srv1 options (user 'postgres');
CREATE USER MAPPING

建立測試表

在每個資料節點建立測試表（代理節點不需要建立），樣本如下：

create table users(userid int, name text);

CLUSTER模式測試

資料水平分割測試步驟如下：

在每個資料節點建立插入函數，樣本如下：

pl_db0=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
pl_db0-> RETURNS integer AS $$
pl_db0$>        INSERT INTO users (userid, name) VALUES ($1,$2);
pl_db0$>        SELECT 1;
pl_db0$> $$ LANGUAGE SQL;
CREATE FUNCTION

pl_db1=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
pl_db1-> RETURNS integer AS $$
pl_db1$>        INSERT INTO users (userid, name) VALUES ($1,$2);
pl_db1$>        SELECT 1;
pl_db1$> $$ LANGUAGE SQL;
CREATE FUNCTION

在代理節點建立同名的插入函數，樣本如下：

postgres=> CREATE OR REPLACE FUNCTION insert_user(i_id int, i_name text)
postgres-> RETURNS integer AS $$
postgres$>     CLUSTER 'cluster_srv1';
postgres$>     RUN ON ANY;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

在代理節點建立讀取函數，樣本如下：

postgres=> CREATE OR REPLACE FUNCTION get_user_name()
postgres-> RETURNS TABLE(userid int, name text) AS $$
postgres$>     CLUSTER 'cluster_srv1';
postgres$>     RUN ON ALL ;
postgres$> SELECT userid,name FROM users;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

在代理節點插入10條測試記錄，樣本如下：

SELECT insert_user(1001, 'Sven');
SELECT insert_user(1002, 'Marko');
SELECT insert_user(1003, 'Steve');
SELECT insert_user(1004, 'lottu');
SELECT insert_user(1005, 'rax');
SELECT insert_user(1006, 'ak');
SELECT insert_user(1007, 'jack');
SELECT insert_user(1008, 'molica');
SELECT insert_user(1009, 'pg');
SELECT insert_user(1010, 'oracle');

由於插入函數執行的是RUN ON ANY，即插入資料時隨機選取資料節點，查看每個資料節點的資料如下：

pl_db0=> select * from users;
 userid |  name
--------+--------
   1001 | Sven
   1003 | Steve
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
   1008 | molica
   1009 | pg
(8 rows)

pl_db1=> select * from users;
 userid |  name
--------+--------
   1002 | Marko
   1010 | oracle
(2 rows)

說明

通過查詢可以發現10條資料分布在不同資料節點，由於10條資料太少，導致分布不均勻。

在代理節點執行讀取函數，由於執行的是RUN ON ALL，即代理節點返回所有資料節點查詢結果，樣本如下：

postgres=> SELECT USERID,NAME FROM GET_USER_NAME();
 userid |  name
--------+--------
   1001 | Sven
   1003 | Steve
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
   1008 | molica
   1009 | pg
   1002 | Marko
   1010 | oracle
(10 rows)

SQL複製測試步驟如下：

在各個節點建立清理函數用於清理表users資料，樣本如下：

pl_db0=> CREATE OR REPLACE FUNCTION trunc_user()
pl_db0-> RETURNS integer AS $$
pl_db0$>        truncate table users;
pl_db0$>        SELECT 1;
pl_db0$> $$ LANGUAGE SQL;
CREATE FUNCTION

pl_db1=> CREATE OR REPLACE FUNCTION trunc_user()
pl_db1-> RETURNS integer AS $$
pl_db1$>        truncate table users;
pl_db1$>        SELECT 1;
pl_db1$> $$ LANGUAGE SQL;
CREATE FUNCTION

postgres=> CREATE OR REPLACE FUNCTION trunc_user()
postgres-> RETURNS SETOF integer AS $$
postgres$>     CLUSTER 'cluster_srv1';
postgres$>     RUN ON ALL;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

在代理節點執行清理函數，樣本如下：

postgres=> SELECT TRUNC_USER();
 trunc_user
------------
          1
          1
(2 rows)

在代理節點建立插入函數，樣本如下：

postgres=> CREATE OR REPLACE FUNCTION insert_user_2(i_id int, i_name text)
postgres-> RETURNS SETOF integer AS $$
postgres$>     CLUSTER 'cluster_srv1';
postgres$>     RUN ON ALL;
postgres$> TARGET insert_user;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

在代理節點插入4條測試記錄，樣本如下：

SELECT insert_user_2(1004, 'lottu');
SELECT insert_user_2(1005, 'rax');
SELECT insert_user_2(1006, 'ak');
SELECT insert_user_2(1007, 'jack');

查看每個資料節點的資料，樣本如下：

pl_db0=> select * from users;
 userid | name
--------+-------
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
(4 rows)

pl_db1=> select * from users;
 userid | name
--------+-------
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
(4 rows)

說明

每個資料節點的資料都一樣，說明資料複製成功。

在代理節點查詢時，只需要執行RUN ON ANY，即在任意一個資料節點讀取資料即可，樣本如下：

postgres=> CREATE OR REPLACE FUNCTION get_user_name_2()
postgres-> RETURNS TABLE(userid int, name text) AS $$
postgres$>     CLUSTER 'cluster_srv1';
postgres$>     RUN ON ANY ;
postgres$> SELECT userid,name FROM users;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

postgres=> SELECT USERID,NAME FROM GET_USER_NAME_2();
 userid | name
--------+-------
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
(4 rows)

CONNECT模式測試

使用CONNECT模式時，代理節點可以直接跨執行個體訪問，樣本如下：

postgres=> CREATE OR REPLACE FUNCTION get_user_name_3()
postgres-> RETURNS TABLE(userid int, name text) AS $$
postgres$>     CONNECT 'dbname=pl_db0 host=100.81.137.72 port=56789';
postgres$> SELECT userid,name FROM users;
postgres$> $$ LANGUAGE plproxy;
CREATE FUNCTION

postgres=> SELECT USERID,NAME FROM GET_USER_NAME_3();
 userid | name
--------+-------
   1004 | lottu
   1005 | rax
   1006 | ak
   1007 | jack
(4 rows)