The PL/Proxy plug-in supports CLUSTER and CONNECT modes to access databases.

Prerequisites

The instance runs one of the following PostgreSQL versions:
  • PostgreSQL 12 (kernel version 20200421 and later)
  • PostgreSQL 11 (kernel version 20200402 and later)
Note To view the kernel version, perform the following steps: Log on to the ApsaraDB for RDS console, find the target RDS instance, and navigate to the Basic Information page. Then, in the Configuration Information section, check whether the Upgrade Minor Version button exists. If the button exists, click it to view the kernel version. If the button does not exist, it indicates that you are already using the latest kernel version. For more information, see Upgrade the kernel version of an ApsaraDB RDS for PostgreSQL instance.
Upgrade the kernel of PostgreSQL

Background information

The PL/Proxy plug-in supports the following modes:

  • CLUSTER

    Supports horizontal splitting of databases and SQL replication.

  • CONNECT

    Supports SQL requests routing to specified databases.

For more information about the use of the PL/Proxy plug-in, visit PL/Proxy.

Precautions

  • You can directly manage tables across these PostgreSQL instances that reside in the same VPC.
  • An ECS instance in the VPC where the PostgreSQL instance resides can redirect access requests between instances. This allows you to manage tables across instances.
  • The number of data nodes at the proxy node backend must be 2 to the power of n.

Test environment

Select an instance as the proxy node and another two instances as the data nodes. The following table describes the instance details.

IP Node type Database name Username
100.xx.xx.136 Proxy node postgres postgres
100.xx.xx.72 Data node pl_db0 postgres
11.xx.xx.9 Data node pl_db1 postgres

Create a PL/Proxy plug-in

Execute the following statement to create a PL/Proxy plug-in:

create extension plproxy

Create a PL/Proxy cluster

Note You can skip this operation when you use the CONNECT mode.
  1. Execute the following statement to create a PL/Proxy cluster and specify the database names, IP addresses, and ports of the child node to be connected:
    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
  2. Execute the following statement to grant permissions to the postgres user:
    postgres=# grant usage on FOREIGN server cluster_srv1 to postgres;
    GRANT 
  3. Execute the following statement to create a user mapping:
    postgres=> create user mapping for postgres server cluster_srv1 options (user 'postgres');
    CREATE USER MAPPING

Create a test table

Execute the following statement to create a test table in each data node:

create table users(userid int, name text);

Test in CLUSTER mode

To test horizontal data splitting, follow these steps:

  1. Execute the following statements to create an insert function for each data node:
    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
  2. Execute the following statements to create an insert function with the same name for the proxy node:
    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
  3. Execute the following statements to create a function for the proxy node. This allows you to obtain user data.
    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
  4. Execute the following statements to insert 10 test records in the proxy node:
    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');
  5. The insert function contains the RUN ON ANY statement to randomly insert data into two data nodes. Execute the following statements to view data of each data node:
    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)
    Note The query results indicate that 10 data records are distributed among different data nodes. The uneven distribution is based on the minimum data volume.
  6. The function to obtain user data contains the RUN ON ALL statement to return the query results from both data nodes. Execute the following statement to execute the function on the proxy node:
    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)

To test SQL replication, follow these steps:

  1. Execute the following statements to create a function for each node to truncate the users table:
    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
  2. Execute the truncate function on the proxy node:
    postgres=> SELECT TRUNC_USER();
     trunc_user
    ------------
              1
              1
    (2 rows)
  3. Execute the following statements to create an insert function for the proxy node:
    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. Execute the following statements to insert four test records into the proxy node:
    SELECT insert_user_2(1004, 'lottu');
    SELECT insert_user_2(1005, 'rax');
    SELECT insert_user_2(1006, 'ak');
    SELECT insert_user_2(1007, 'jack');
  5. Execute the following statements to view data in each data node:
    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)
    Note The data is the same in each data node. This indicates that data is replicated.
  6. When you query data from the proxy node, you can execute the RUN ON ANY statement to read data from all data nodes. Execute the following statements to query data:
    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)

Test in CONNECT mode

When you use the CONNECT mode, you can execute the following statements to access other instances from the proxy node:

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)