开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 最佳实践 - 水平分库(基于plproxy)

简介:
+关注继续查看

背景

我一直以来都比较推荐plproxy这个PostgreSQL代理软件, 因为它小巧灵活好用, 效率高.

最近朋友邀请我给他们做个分布式的方案, 所以又把plproxy翻出来了.

本文讲一讲在单节点中如何快速的部署plproxy环境.

环境

PostgreSQL 9.3.1  
plproxy 2.x  

plrpoxy节点

hostaddr 172.16.3.150  
port 1921  
user proxy  
password proxy  
dbname proxy  
schema digoal  // 这个schema名和数据节点一致, 可以省去写plproxy language target的步骤.  

数据节点

hostaddr 172.16.3.150  
port 1921  
user digoal  // plproxy将使用digoal用户连接数据节点.  
password digoal  

dbname db0  
schema digoal  
dbname db1  
schema digoal  
dbname db2  
schema digoal  
dbname db3  
schema digoal  

部署plproxy

首先在http://git.postgresql.org/gitweb/?p=plproxy.git;a=summary下载plproxy.

tar -zxvf plproxy-d703683.tar.gz  
mv plproxy-d703683 /opt/soft_bak/postgresql-9.3.1/contrib  
cd /opt/soft_bak/postgresql-9.3.1/contrib/plproxy-d703683  
[root@db-172-16-3-150 plproxy-d703683]# export PATH=/home/pg93/pgsql9.3.1/bin:$PATH  
[root@db-172-16-3-150 plproxy-d703683]# which pg_config  
[root@db-172-16-3-150 plproxy-d703683]# gmake clean  
[root@db-172-16-3-150 plproxy-d703683]# gmake  
[root@db-172-16-3-150 plproxy-d703683]# gmake install  

创建proxy库, proxy角色, 在proxy库创建plproxy extension.

pg93@db-172-16-3-150-> psql  
psql (9.3.1)  
Type "help" for help.  
postgres=# create role proxy nosuperuser login encrypted password 'proxy';  
CREATE ROLE  
digoal=# create database proxy;  
CREATE DATABASE  
digoal=# \c proxy  
You are now connected to database "proxy" as user "postgres".  
proxy=# create extension plproxy;  
CREATE EXTENSION  

调整proxy库权限

proxy=# grant all on database proxy to proxy;  
GRANT  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "digoal".  

创建digoal schema, 目的是和数据节点的schema匹配, 这样的话可以省去在代理函数中写target强行指定schema.

proxy=> create schema digoal;  
CREATE SCHEMA  

创建节点数据库

proxy=> \c postgres postgres   
You are now connected to database "postgres" as user "postgres".   
postgres=# create role digoal nosuperuser login encrypted password 'digoal';   
postgres=# create database db0;  
postgres=# create database db1;  
postgres=# create database db2;  
postgres=# create database db3;  

调整权限, 赋予给后面将要给user mapping中配置的option user权限.

postgres=# grant all on database db0 to digoal;  
postgres=# grant all on database db1 to digoal;  
postgres=# grant all on database db2 to digoal;  
postgres=# grant all on database db3 to digoal;  

使用超级用户在proxy数据库中创建server.

proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=#   
CREATE SERVER cluster_srv1 FOREIGN DATA WRAPPER plproxy options   
(connection_lifetime '1800',  
p0 'dbname=db0 hostaddr=172.16.3.150 port=1921 application_name=test',  
p1 'dbname=db1 hostaddr=172.16.3.150 port=1921',  
p2 'dbname=db2 hostaddr=172.16.3.150 port=1921',  
p3 'dbname=db3 hostaddr=172.16.3.150 port=1921');  

创建server时可以使用libpq中的选项. 例如本例使用了application_name.

将server权限赋予给proxy用户.

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

配置proxy用户的连接cluster_srv1的选项.

proxy=# create user mapping for proxy server cluster_srv1 options (user 'digoal');  
CREATE USER MAPPING  

用户proxy连接到cluster_srv1时使用digoal用户连接, 这里不需要配置password, 因为我们将使用trust认证.

修改数据节点的pg_hba.conf

从proxy节点使用digoal用户连接数据库db0, db1, db2, db3使用trust认证.

vi $PGDATA/pg_hba.conf  
host db0 digoal 172.16.3.150/32 trust  
host db1 digoal 172.16.3.150/32 trust  
host db2 digoal 172.16.3.150/32 trust  
host db3 digoal 172.16.3.150/32 trust  
pg_ctl reload  

在plproxy节点创建代理函数

使用超级用户创建plproxy函数, 然后把函数权限赋予给proxy权限.

proxy=# CREATE OR REPLACE FUNCTION digoal.dy(sql text)                    
 RETURNS SETOF record  
 LANGUAGE plproxy  
 STRICT  
AS $function$  
  cluster 'cluster_srv1';  
  run on all;  
$function$;  
proxy=# grant execute on function digoal.dy(text) to proxy;  
GRANT  

在数据节点创建实体函数

proxy=# \c db0 digoal  
db0=#   
CREATE OR REPLACE FUNCTION digoal.dy(sql text)  
 RETURNS SETOF record  
 LANGUAGE plpgsql  
 STRICT  
AS $function$  
  declare  
  rec record;  
  begin  
    for rec in execute sql loop  
      return next rec;  
    end loop;  
    return;  
  end;  
$function$;  
db0=# \c db1 digoal  
...  
db1=# \c db2 digoal  
...  
db2=# \c db3 digoal  
...  

在proxy节点中就可以访问数据节点了。

例如查询这个动态SQL.

proxy=> select * from digoal.dy('select count(*) from pg_class') as t(i int8);  
  i    
-----  
 293  
 293  
 293  
 293  
(4 rows)  
proxy=> select sum(i) from digoal.dy('select count(*) from pg_class') as t(i int8);  
 sum    
------  
 1172  
(1 row)  

plproxy节点测试

一. 修改foreign server测试, 观察连接将重置.

前面那个会话不要断开, 在另一个会话中观察proxy发起的连接到数据节点的连接.

postgres=# select * from pg_stat_activity where usename='digoal';  
 datid | datname | pid  | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_sta  
rt         | xact_start |          query_start          |         state_change          | waiting | state |                        q  
uery                          
-------+---------+------+----------+---------+------------------+--------------+-----------------+-------------+--------------------  
-----------+------------+-------------------------------+-------------------------------+---------+-------+-------------------------  
----------------------------  
 91246 | db0     | 8171 |    91250 | digoal  | test             | 172.16.3.150 |                 |       47937 | 2013-11-22 17:23:26  
.138425+08 |            | 2013-11-22 17:27:05.539286+08 | 2013-11-22 17:27:05.539745+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91247 | db1     | 8172 |    91250 | digoal  |                  | 172.16.3.150 |                 |       47938 | 2013-11-22 17:23:26  
.138688+08 |            | 2013-11-22 17:27:05.53938+08  | 2013-11-22 17:27:05.539874+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91248 | db2     | 8173 |    91250 | digoal  |                  | 172.16.3.150 |                 |       47939 | 2013-11-22 17:23:26  
.138957+08 |            | 2013-11-22 17:27:05.53938+08  | 2013-11-22 17:27:05.539841+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91249 | db3     | 8174 |    91250 | digoal  |                  | 172.16.3.150 |                 |       47940 | 2013-11-22 17:23:26  
.139178+08 |            | 2013-11-22 17:27:05.539366+08 | 2013-11-22 17:27:05.539793+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
(4 rows)  

再次在proxy的同一会话中查询时, 这些会话会复用, 不会断开. 前面已经讲了plproxy是使用长连接的.

如果修改了server, 那么这些连接会断开, 重新连接. 所以不需要担心修改server带来的连接cache问题.

postgres=# \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# alter server cluster_srv1 options (set p1 'dbname=db1 hostaddr=172.16.3.150 port=1921 application_name=abc');  
ALTER SERVER  

再次在proxy的同一会话中查询后, 我们发现4个连接都变了, 说明alter server后, 如果再次发起plproxy函数的查询请求, 那么proxy会重置连接.

proxy=> select sum(i) from digoal.dy('select count(*) from pg_class') as t(i int8);  
 sum    
------  
 1172  
(1 row)  

在另一会话的查询结果 :

proxy=# select * from pg_stat_activity where usename='digoal';  
 datid | datname | pid  | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_sta  
rt         | xact_start |          query_start          |         state_change          | waiting | state |                        q  
uery                          
-------+---------+------+----------+---------+------------------+--------------+-----------------+-------------+--------------------  
-----------+------------+-------------------------------+-------------------------------+---------+-------+-------------------------  
----------------------------  
 91246 | db0     | 8245 |    91250 | digoal  | test             | 172.16.3.150 |                 |       47941 | 2013-11-22 17:30:36  
.933077+08 |            | 2013-11-22 17:30:36.936784+08 | 2013-11-22 17:30:36.938837+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91248 | db2     | 8247 |    91250 | digoal  |                  | 172.16.3.150 |                 |       47943 | 2013-11-22 17:30:36  
.933502+08 |            | 2013-11-22 17:30:36.936783+08 | 2013-11-22 17:30:36.938981+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91249 | db3     | 8248 |    91250 | digoal  |                  | 172.16.3.150 |                 |       47944 | 2013-11-22 17:30:36  
.933731+08 |            | 2013-11-22 17:30:36.937147+08 | 2013-11-22 17:30:36.939015+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
 91247 | db1     | 8246 |    91250 | digoal  | abc              | 172.16.3.150 |                 |       47942 | 2013-11-22 17:30:36  
.933288+08 |            | 2013-11-22 17:30:36.93757+08  | 2013-11-22 17:30:36.939299+08 | f       | idle  | select i::int8 from digo  
al.dy($1::text) as (i int8)  
(4 rows)  

二. run on 的几种形式, 表示数据路由方法.

在数据节点创建测试表.

proxy=# \c db0 digoal  
db0=> create table t(id int);  
CREATE TABLE  
db0=> \c db1  
You are now connected to database "db1" as user "digoal".  
db1=> create table t(id int);  
CREATE TABLE  
db1=> \c db2  
You are now connected to database "db2" as user "digoal".  
db2=> create table t(id int);  
CREATE TABLE  
db2=> \c db3  
You are now connected to database "db3" as user "digoal".  
db3=> create table t(id int);  
CREATE TABLE  

在数据节点创建插入数据的实体函数, 每个节点返回不一样的数字.

\c db0 digoal  
db0=> create or replace function digoal.f_test4() returns int as $$  
declare  
begin  
insert into t(id) values (1);  
return 0;  
end;  
$$ language plpgsql strict;  
db1=> create or replace function digoal.f_test4() returns int as $$  
declare  
begin  
insert into t(id) values (1);  
return 1;  
end;  
$$ language plpgsql strict;  
db2=> create or replace function digoal.f_test4() returns int as $$  
declare  
begin  
insert into t(id) values (1);  
return 2;  
end;  
$$ language plpgsql strict;  
db3=> create or replace function digoal.f_test4() returns int as $$  
declare  
begin  
insert into t(id) values (1);  
return 3;  
end;  
$$ language plpgsql strict;  

在proxy节点创建代理函数, 并且将执行权限赋予给proxy用户.

proxy=> \c proxy postgres  
create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on 0;   -- 在指定的数据节点上运行, 本例可以设置为03, 顺序和创建的server中的配置顺序一致. p0, p1, p2, p3  
$$ language plproxy strict;  
proxy=# grant execute on function digoal.f_test4() to proxy;  
GRANT  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "proxy".  
proxy=> select * from digoal.f_test4();  
 f_test4   
---------  
       0  
(1 row)  

如果run on 的数字改成0-3以外的数字, 运行时将报错, 符合预期.

proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on 4;    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "proxy".  
proxy=> select * from digoal.f_test4();  
ERROR:  PL/Proxy function digoal.f_test4(0): part number out of range  

run on any表示随机的选择一个数据节点运行.

proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on any;    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "proxy".  
proxy=> select * from digoal.f_test4();  
 f_test4   
---------  
       0  
(1 row)  

proxy=> select * from digoal.f_test4();  
 f_test4   
---------  
       3  
(1 row)  

proxy=> select * from digoal.f_test4();  
 f_test4   
---------  
       2  
(1 row)  

proxy=> select * from digoal.f_test4();  
 f_test4   
---------  
       3  
(1 row)  

run on function() 则使用函数结果的hash值计算得到运行节点.

proxy=> create or replace function digoal.f(int) returns int as $$  
select $1;  
$$ language sql strict;  
CREATE FUNCTION  
proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on digoal.f(10);    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=> select digoal.f_test4();  
 f_test4   
---------  
       2  
(1 row)  
proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on digoal.f(11);    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "proxy".  
proxy=> select digoal.f_test4();  
 f_test4   
---------  
       3  
(1 row)  
proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on digoal.f(-11);    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=# \c proxy proxy  
You are now connected to database "proxy" as user "proxy".  
proxy=> select digoal.f_test4();  
 f_test4   
---------  
       1  
(1 row)  

run on all表示所有数据节点运行. 代理函数必须使用returns setof返回.

proxy=> \c proxy postgres  
You are now connected to database "proxy" as user "postgres".  
proxy=# create or replace function digoal.f_test4() returns int as $$  
cluster 'cluster_srv1';  
run on all;              
$$ language plproxy strict;  
ERROR:  PL/Proxy function digoal.f_test4(0): RUN ON ALL requires set-returning function  
proxy=# drop function digoal.f_test4();  
DROP FUNCTION  
proxy=# create or replace function digoal.f_test4() returns setof int as $$  
cluster 'cluster_srv1';  
run on all;    
$$ language plproxy strict;  
CREATE FUNCTION  
proxy=# grant execute on function digoal.f_test4() to proxy;  
GRANT  
proxy=# \c proxy proxy   
You are now connected to database "proxy" as user "proxy".  
proxy=> select digoal.f_test4();  
 f_test4   
---------  
       0  
       1  
       2  
       3  
(4 rows)  

注意事项

1. 设计时需要注意
plproxy函数所在的schema尽量和数据节点上实际函数的schema一致.

否则需要在plproxy函数中使用target指定 schema.functionname;

2. 数据节点的个数请保持2^n,

这么做有利于后期的节点扩展, 例如2个节点扩展到4个节点时, 数据不需要发生跨节点的重分布.

例如

mod(x,2)=0 那么mod(x,4)=02  
mod(x,2)=1 那么mod(x,4)=13  

比较适合位运算的分布算法.

当然我们也可以使用一致性哈希的设计思路,参考

《一致性哈希在分布式数据库中的应用探索》
https://yq.aliyun.com/articles/57954

3. 如果业务为短连接的形式, 那么需要1层连接池, 在应用程序和plproxy数据库之间. 而不是plproxy和数据节点之间.

在应用程序和plproxy之间加连接池后, 其实对于plproxy来说就是长连接了, 所以在plproxy和数据节点之间也就不需要连接池了.

4. 长连接不需要连接池, 因为plproxy和数据节点之间的连接是长连接.

5. plproxy语法非常简洁,而且函数调用彻底避免了事务的问题

connect, cluster, run, select, split, target.

6. 关于连接密码

出于安全考虑, 建议在任何配置中不要出现明文密码, 所以最好是plproxy服务器到数据节点是trust验证, 保护好plproxy即可.

假设plproxy在172.16.3.2上. 数据节点有4个, 库名和用户名都为digoal. 那么在4个节点上配置pg_hba.conf如下.

   node0  
   host digoal digoal 172.16.3.2/32 trust  
   node1  
   host digoal digoal 172.16.3.2/32 trust  
   node2  
   host digoal digoal 172.16.3.2/32 trust  
   node3  
   host digoal digoal 172.16.3.2/32 trust  

7. run 详解:

   run on <NR>, <NR>是数字常量, 范围是0 到 nodes-1; 例如有4个节点 run on 0; (run on 4则报错).  
   run on ANY,   
   run on function(...), 这里用到的函数返回结果必须是int2, int4 或 int8.   
   run on ALL, 这种的plproxy函数必须是returns setof..., 实体函数没有setof的要求.  

8. 一个plproxy中只能出现一条connect语句, 符合预期, 否则报错.

digoal=# create or replace function f_test3() returns setof int8 as $$  
  connect 'hostaddr=172.16.3.150 dbname=db0 user=digoal port=1921';    
  connect 'hostaddr=172.16.3.150 dbname=db1 user=digoal port=1921';                                
  select count(*) from pg_class;  
$$ language plproxy strict;  
ERROR:  PL/Proxy function postgres.f_test3(0): Compile error at line 2: Only one CONNECT statement allowed  

9. 不要把plproxy语言的权限赋予给普通用户, 因为开放了trust认证, 如果再开放plproxy语言的权限是非常危险的.

正确的做法是使用超级用户创建plproxy函数, 然后把函数的执行权限赋予给普通用户.

千万不要这样省事 :

update pg_language set lanpltrusted='t' where lanname='plproxy';  

10. 如果有全局唯一的序列需求, 可以将序列的步调调整一下, 每个数据节点使用不同的初始值.
例如

db0=# create sequence seq1 increment by 4 start with 0;  
CREATE SEQUENCE  
db1=# create sequence seq1 increment by 4 start with 1;  
db2=# create sequence seq1 increment by 4 start with 2;  
db3=# create sequence seq1 increment by 4 start with 3;  

考虑到扩容, 可以将步调调比较大, 例如1024. 那么可以容纳1024个节点.

参考

1. http://kaiv.wordpress.com/2007/07/27/postgresql-cluster-partitioning-with-plproxy-part-i/

2. http://kaiv.wordpress.com/2007/09/02/postgresql-cluster-partitioning-with-plproxy-part-ii/

3. http://blog.163.com/digoal@126/blog/static/163877040201041111304328/

4. http://blog.163.com/digoal@126/blog/static/1638770402010411113114315/

5. http://blog.163.com/digoal@126/blog/static/163877040201192535630895/

6. http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING

7. http://git.postgresql.org/gitweb/?p=plproxy.git;a=summary

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
淘系数据模型治理最佳实践
本次分享题目为淘系数据模型治理,主要介绍过去一年淘系数据治理工作的一些总结。
107 0
备份工具pg_dump的使用《postgres》
备份工具pg_dump的使用《postgres》
11 0
【走进RDS】之RDS PostgreSQL索引推荐原理及最佳实践
很多开发人员都知道索引对于数据库的查询性能至关重要,一个好的索引能使数据库的性能提升成千上万倍。但给数据库加索引是一项相对专业的工作,需要对数据库的运行原理有一定了解。同时,加了索引有没有性能提升、性能提升了多少,这些都是加索引前就想知道的。这项繁杂的工作有没有更好的方案呢?有!就是今天重磅推出的索引推荐。
170 0
再谈PostgreSQL的膨胀和vacuum机制及最佳实践
作者介绍 朱贤文,成都文武信息技术有限公司创始人,PostgreSQL中国用户会核心组成员,熟悉数据库,存储和集群技术; 成都文武信息技术有限公司是PostgreSQL和GreenPlum数据库服务的专业厂商,主要产品是ECOX集群管理系统和Hunghu Cloud,专门运行数据库的私有云系统,带高端存储功能。公司总部位于天府软件园。公司网站:w3.ww-it.cn 写本文的原因 这两天有两篇专门介绍PostgreSQL的vacuum机制的技术文章,得到了比较热烈和正面的反馈,让用户可以比较清楚地理解和使用这个特性。 我个人觉得有点小遗憾:这两篇文章没有跳出技术的角度,分析为什么会有这
56 0
PostgreSQL基于错误XML外部实体攻击
最近国外安全人员发现了一个PostgreSQL(所有版本)基于错误XML外部实体执行的高危漏洞。
888 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载