PL/Proxy和PostgreSQL集群的结构关系可以用下图清楚地表示
下面是详细的安装方法：
以下操作是在三台不同机器上执行的情况，其中plproxy节点的机器名是P1，数据库节点的机器名分别是D1和D2。机器硬件配置如下，同时需要Linux-4.2、postgresql-8.3.0和plproxy-2.0.4，pgbouncer的安装过程略去。plproxy节点:hostname:P1inetaddr:10.0.0.1OS:Linux2.6.9-42.ELsmpCPU:Intel(R)Xeon(R)CPUL5320@1.86GHzMemTotal:514440kBnode1节点:hostname:D1inetaddr:10.0.0.2OS:Linux2.6.9-42.ELsmpCPU:Intel(R)Xeon(R)CPUL5320@1.86GHzMemTotal:254772kBnode2节点:hostname:D2inetaddr:10.0.0.3OS:Linux2.6.9-42.ELsmpCPU:Intel(R)Xeon(R)CPUL5320@1.86GHzMemTotal:254772kB1.在P1,D1,D2上安装postgresql-8.3.0，并创建URTCluster数据库##Compileandinstallgunzippostgresql-8.3.0.tar.gztarxfpostgresql-8.3.0.tarcdpostgresql-8.3.0./configure–prefix=/home/y/pgsql–with-perlgmakegmakechecksudogmakeinstall##AddUnixUsersudoadduserpostgressudomkdir/home/y/pgsql/datasudochownpostgres/home/y/pgsql/data##InitDBandStartservicesudo-upostgres/home/y/pgsql/bin/initdb-D/home/y/pgsql/datasudo-upostgres/home/y/pgsql/bin/postgres-D/home/y/pgsql/data>logfile2>&1&##CreateDBandUseLocalConnectionsudo-upostgres/home/y/pgsql/bin/createdbURTCluster##检查数据库是否已经创建sudo-upostgres/home/y/pgsql/bin/psql-dURTCluster#D1,D2必须允许P1访问#编辑postgresql.conf,打开tcp连接端口sudovim/home/y/pgsql/data/postgresql.conflisten_addresses=‘*’port=5432#添加postgres用户的认证sudovim/home/y/pgsql/data/pg_hba.confhostURTClusterpostgres10.0.0.0/16trust#重起服务器sudo-upostgres/home/y/pgsql/bin/pg_ctl-D/home/y/pgsql/datastopsudo-upostgres/home/y/pgsql/bin/postgres-D/home/y/pgsql/data>logfile2>&1&sudo-upostgres/home/y/pgsql/bin/pg_ctl-D/home/y/pgsql/datareload2.在P1上安装plproxy-2.0.4#检查$PATH变量里是否有/home/y/pgsql/bin目录，如果没有，修改你的.bash_profile文件，添加/home/y/pgsql/bin到path里。echo$PATHgunzipplproxy-2.0.4.tar.gztarxfplproxy-2.0.4.tarcdplproxy-2.0.4gmakesudogmakeinstall#创建plproxysudo-upostgres/home/y/pgsql/bin/psql-f/home/y/pgsql/share/contrib/plproxy.sqlURTCluster3.在P1,D1,D2上安装plpgsqlsudo-upostgres/home/y/pgsql/bin/createlangplpgsqlURTCluster4.在P1上创建schemasudo-upostgres/home/y/pgsql/bin/psql-dURTClusterURTCluster=#createschemaplproxy;5.在P1上初始化设置#plproxy的配置是通过三个函数（过程）实现的，这三个函数的标准模版如下：#这个函数是让plproxy可以找到对应的集群CREATEORREPLACEFUNCTIONplproxy.get_cluster_partitions(cluster_nametext)RETURNSSETOFtextAS$$BEGINIFcluster_name=’URTCluster’THENRETURNNEXT‘dbname=URTClusterhost=10.0.0.2′;RETURNNEXT‘dbname=URTClusterhost=10.0.0.3′;RETURN;ENDIF;RAISEEXCEPTION‘Unknowncluster’;END;$$LANGUAGEplpgsql;#这个函数是plproxy用于判断是否给前端返回已经cache过的结果用的CREATEORREPLACEFUNCTIONplproxy.get_cluster_version(cluster_nametext)RETURNSint4AS$$BEGINIFcluster_name=‘URTCluster’THENRETURN1;ENDIF;RAISEEXCEPTION‘Unknowncluster’;END;$$LANGUAGEplpgsql;#这个函数是获取不同的集群的配置createorreplacefunctionplproxy.get_cluster_config(cluster_nametext,outkeytext,outvaltext)returnssetofrecordas$$beginkey:=’statement_timeout’;val:=60;returnnext;return;end;$$languageplpgsql;#把这三个函数放在一个URTClusterInit.sql文件里，并执行sudo-upostgres/home/y/pgsql/bin/psql-fURTClusterInit.sql-dURTCluster-h10.0.0.16.在D1，D2节点上设置#给每个数据库节点都创建一张表usersCREATETABLEusers(usernametext,emailtext);#给每个数据库节点都创建一个插入函数CREATEORREPLACEFUNCTIONinsert_user(i_usernametext,i_emailaddresstext)RETURNSintegerAS$$INSERTINTOusers(username,email)VALUES($1,$2);SELECT1;$$LANGUAGESQL;#把函数保存在URTClusterNodesInit_1.sql文件里，并执行sudo-upostgres/home/y/pgsql/bin/psql-fURTClusterNodesInit_1.sql-h10.0.0.2-dURTClustersudo-upostgres/home/y/pgsql/bin/psql-fURTClusterNodesInit_1.sql-h10.0.0.3-dURTCluster7.在P1节点上设置#在plproxy节点上创建一个同名的插入函数，用于进行集群检索CREATEORREPLACEFUNCTIONinsert_user(i_usernametext,i_emailaddresstext)RETURNSintegerAS$$CLUSTER‘URTCluster’;RUNONhashtext(i_username);$$LANGUAGEplproxy;#在plproxy节点上创建一个查询函数，用于进行集群检索CREATEORREPLACEFUNCTIONget_user_email(i_usernametext)RETURNStextAS$$CLUSTER‘URTCluster’;RUNONhashtext(i_username);SELECTemailFROMusersWHEREusername=i_username;$$LANGUAGEplproxy;#把函数保存在URTClusterProxyExec.sql文件里，并执行sudo-upostgres/home/y/pgsql/bin/psql-fURTClusterProxyExec_1.sql-h10.0.0.1-dURTCluster8.在P1上测试结果sudo-upostgres/home/y/pgsql/bin/psql-dURTClusterSELECTinsert_user(’Sven’,’sven@somewhere.com’);#被保存到D2，可以用selecthashtext(’Sven’)&1验证，被hash到partition1SELECTinsert_user(’Marko’,‘marko@somewhere.com’);#被保存到D2，可以用selecthashtext(’Marko’)&1验证，被hash到partition1SELECTinsert_user(’Steve’,’steve@somewhere.cm’);#被保存到D1，可以用selecthashtext(’Steve’)&1验证，，被hash到partition0SELECTget_user_email(’Sven’);SELECTget_user_email(’Marko’);SELECTget_user_email(’Steve’);
评论