The PL/Proxy plug-in allows you to access your ApsaraDB RDS instance in CLUSTER or
CONNECT mode.
Prerequisites
Your RDS instance runs one of the following PostgreSQL versions:
- PostgreSQL 14
- PostgreSQL 13
- PostgreSQL 12 (with a minor engine version of 20200421 or later)
- PostgreSQL 11 (with a minor engine version of 20200402 or later)
Note If you want to view the minor engine version, perform the following steps: Log on
to the ApsaraDB RDS console, find your RDS instance and navigate to the
Basic Information page. In the
Configuration Information section of the page, check whether the
Upgrade Minor Version button exists. If the button exists, you can click it to view and update the minor
engine version. If the button does not exist, you are using the latest minor engine
version. For more information, see
Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
Background information
The PL/Proxy plug-in supports the following modes:
For more information about how to use the PL/Proxy plug-in, visit PL/Proxy.
Precautions
- You can directly manage tables across RDS instances that reside in the same virtual
private cloud (VPC).
- An Elastic Compute Service (ECS) instance that resides in the same VPC as your RDS
instance can serve as a proxy to redirect access requests for your RDS instance. This
allows you to manage tables across RDS instances that reside in different VPCs.
- The number of data nodes that are served by the proxy node must be 2 to the power
of n.
Test environment
Select an RDS instance as the proxy node and another two RDS instances as the data
nodes. The following table provides details about the three RDS instances.
IP address |
Node type |
Instance 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 If you use the CONNECT mode, you can skip the operations that are described in this
section.
- Create a PL/Proxy cluster and specify the names, IP addresses, and ports of the RDS
instances that you want to connect as data nodes in the cluster. Example:
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
- Grant the permissions on the created PL/Proxy cluster to the postgres user. Example:
postgres=# grant usage on FOREIGN server cluster_srv1 to postgres;
GRANT
- Create a user mapping. Example:
postgres=> create user mapping for postgres server cluster_srv1 options (user 'postgres');
CREATE USER MAPPING
Create a test table
Create a test table named users on each data node. Example:
create table users(userid int, name text);
Test the CLUSTER mode
To test horizontal sharding, perform the following steps:
- Create a function that is used to insert data on each data node. Example:
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
- Create a function that is used to insert data on the proxy node. This function has
the same name as the function that is used to insert data on each data node. Example:
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
- Create a function that is used to read data on the proxy node. Example:
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
- Insert 10 test records on the proxy node. Example:
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');
- View the data on each data node. The function that is used to insert data contains
the RUN ON ANY statement. This statement randomly inserts data into either data node.
Therefore, you may find the following data on the two data nodes:
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 the 10 data records are unevenly distributed between
the two data nodes.
- Invoke the function that is used to read data on the proxy node. This function contains
the RUN ON ALL statement that reads data from both data nodes. Example:
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, perform the following steps:
- Create a function that is used to truncate the users table on each node. Example:
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
- Invoke the function that is used to truncate data on the proxy node. Example:
postgres=> SELECT TRUNC_USER();
trunc_user
------------
1
1
(2 rows)
- Create a function that is used to insert data on the proxy node. Example:
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
- Insert four test records into the proxy node. Example:
SELECT insert_user_2(1004, 'lottu');
SELECT insert_user_2(1005, 'rax');
SELECT insert_user_2(1006, 'ak');
SELECT insert_user_2(1007, 'jack');
- View the data on each data node. Example:
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 on each data node. This indicates that SQL replication is successful.
- Query data on the proxy node. You need only to execute the RUN ON ANY statement that
randomly reads data from either data node. Example:
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 the CONNECT mode
When you use the CONNECT mode, you can access other RDS instances from the proxy node.
Examples:
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)