plproxy
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
plproxy | 2.11.0 | OLAP | BSD 0-Clause | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 2520 | plproxy | No | Yes | No | Yes | No | No | - |
| Related | citus dblink postgres_fdw pg_partman odbc_fdw jdbc_fdw citus_columnar columnar |
|---|
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | PGDG | 2.11.0 | 1817161514 | plproxy | - |
| RPM | PGDG | 2.11.0 | 1817161514 | plproxy_$v | - |
| DEB | PGDG | 2.11.0 | 1817161514 | postgresql-$v-plproxy | - |
Install
You can install plproxy directly. First, make sure the PGDG repository is added and enabled:
pig repo add pgdg -u # Add PGDG repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install plproxy; # Install for current active PG version
pig ext install -y plproxy -v 18 # PG 18
pig ext install -y plproxy -v 17 # PG 17
pig ext install -y plproxy -v 16 # PG 16
pig ext install -y plproxy -v 15 # PG 15
pig ext install -y plproxy -v 14 # PG 14
dnf install -y plproxy_18 # PG 18
dnf install -y plproxy_17 # PG 17
dnf install -y plproxy_16 # PG 16
dnf install -y plproxy_15 # PG 15
dnf install -y plproxy_14 # PG 14
apt install -y postgresql-18-plproxy # PG 18
apt install -y postgresql-17-plproxy # PG 17
apt install -y postgresql-16-plproxy # PG 16
apt install -y postgresql-15-plproxy # PG 15
apt install -y postgresql-14-plproxy # PG 14
Create Extension:
CREATE EXTENSION plproxy;
Usage
plproxy: Database partitioning implemented as procedural language
PL/Proxy is a PostgreSQL procedural language handler that enables remote procedure calls between PostgreSQL databases, with optional sharding.
Create the Extension
CREATE EXTENSION plproxy;
Language Statements
PL/Proxy functions use four types of statements:
Cluster selection – connect to a pre-configured cluster:
CREATE FUNCTION get_user(i_id int) RETURNS SETOF users AS $$
CLUSTER 'mycluster';
RUN ON i_id;
$$ LANGUAGE plproxy;
Direct connection – use a connection string:
CREATE FUNCTION get_config(key text) RETURNS text AS $$
CONNECT 'host=remotehost dbname=config';
SELECT val FROM config WHERE key = $1;
$$ LANGUAGE plproxy;
Execution Modes
RUN ON hash – route to a specific partition based on a hash:
CREATE FUNCTION get_user_settings(i_username text) RETURNS SETOF user_settings AS $$
RUN ON namehash(i_username);
$$ LANGUAGE plproxy;
RUN ON ALL – execute on all databases in parallel:
CREATE FUNCTION get_all_counts() RETURNS SETOF record AS $$
RUN ON ALL;
SELECT count(*) FROM users;
$$ LANGUAGE plproxy;
RUN ON ANY – randomly select a server:
CREATE FUNCTION get_random_quote() RETURNS text AS $$
RUN ON ANY;
SELECT quote FROM quotes ORDER BY random() LIMIT 1;
$$ LANGUAGE plproxy;
Cluster Configuration
Clusters are configured using SQL/MED (Management of External Data):
CREATE SERVER mycluster FOREIGN DATA WRAPPER plproxy
OPTIONS (
connection_lifetime '1800',
p0 'host=node0 dbname=mydb',
p1 'host=node1 dbname=mydb',
p2 'host=node2 dbname=mydb',
p3 'host=node3 dbname=mydb'
);
CREATE USER MAPPING FOR CURRENT_USER
SERVER mycluster
OPTIONS (user 'proxy_user', password 'secret');
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.