PL proxy Cluster Sample configuration process

Look at the pl/proxy, a lot of documents, as a reference bar, reprinted two of them, this is one

Reference:

Http://plproxy.projects.pgfoundry.org/doc/tutorial.html
http://www.tbdata.org/archives/723
Http://bbs.chinaunix.net/thread-1859285-1-1.html
Http://blog.chinaunix.net/uid-15145533-id-2775843.html
Http://blog.chinaunix.net/uid-15145533-id-2775844.html
http://blog.163.com/digoal@126/blog/static/1638770402010411113114315/
http://blog.163.com/digoal@126/blog/static/163877040201192535630895/
Http://bbs.chinaunix.net/thread-1859285-1-1.html
Http://blog.chinaunix.net/uid-15145533-id-2775824.html

The following is transferred from: http://www.blogjava.net/Ilovby/archive/2013/01/28/394849.html

0. Reference materials
1, http://plproxy.projects.pgfoundry.org/doc/tutorial.html
2, http://www.tbdata.org/archives/723
First, the Origin
Originally Taobao this blog has made it very clear, but it needs three virtual machines, er, their own virtual machine completely unfamiliar, plus hard enough, do not want to toss, and then find a stand-alone version of the PL proxy method, everywhere did not find, and then only to see the official document, is the first reference to the found that in fact can be built in a Linux 3 database can be simulated.
Ii. Basic Information
1, Linux version: Ubuntu 12.10
2, PostgreSQL version: 9.1
3, PL proxy version: Postgresql-9.1-plproxy
4, PostgreSQL has been able to use the
5. The following methods of executing SQL statements in pgadmin3
(1) Open Query tool (hold ctrl+e) to open
(2) Paste the SQL statement in Query tool
(3) Press F5 to execute the
(4) Implementation success or failure will be a reminder, very good.
Third, the configuration process
1, emacs/etc/postgresql/9.1/main/pg_hba.conf
-> Change Line: Host all All 127.0.0.1/32 MD5
-> modified as: Host all All 127.0.0.1/32 Trust
2, the creation of three databases, respectively, is proxy,database0,database1, I used the creation tool is pgadmin3, the creation of the database of the SQL code is as follows (the proxy is replaced by the DATABASE0, Database1 can be used separately)
--Database:proxy

--DROP DATABASE proxy;

CREATE DATABASE Proxy
With OWNER = Postgres
ENCODING = ' UTF8 '
Tablespace = Pg_default
Lc_collate = ' en_US. UTF-8 '
Lc_ctype = ' en_US. UTF-8 '
CONNECTION LIMIT =-1; 3. Enter the following command in a shell such as bash or zsh
Psql-u postgres-h 127.0.0.1-f/usr/share/postgresql/9.1/extension/plproxy--2.4.0.sql Proxy
4, select the proxy database in Pgadmin3, open Query tool, enter the following SQL statement and press F5 to execute
Create language Plpgsql;
The result is
Error:language "Plpgsql" already exists

Error **********

Error:language "Plpgsql" already exists
SQL state:42710
Prove that this step is not necessary to do, in fact, there are
5, select the proxy database in Pgadmin3, open Query tool, enter the following statement and execute
Create Schema Plproxy
6, in pgadmin3 Select the proxy database plproxy mode (just created), open Query tool, enter the following statements to establish 3 functions and execute
(1) Establish the Plproxy.get_cluster_config function create OR REPLACE function Plproxy.get_cluster_config (in cluster_name text, Out key text, out Val text)
RETURNS Setof Record as
$BODY $
BEGIN
Key: = ' statement_timeout '; -that is, assign a value to a key variable, and assign a value of ' statement_timeout '
Val: = 60; is to assign a value to the Val variable and assign a value of 60
return to NEXT;
return;
End;
$BODY $
LANGUAGE Plpgsql VOLATILE
Cost 100
ROWS 1000;
ALTER FUNCTION plproxy.get_cluster_config (text)
OWNER to Postgres;
(2) Establish plproxy.get_cluster_partitions function
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions (cluster_name text)
RETURNS Setof text as
$BODY $
BEGIN
IF cluster_name = ' Testcluster ' THEN--cluster_name is the name of the cluster
Return NEXT ' Dbname=database0 host=127.0.0.1 '; --Database name and IP address of database node
Return NEXT ' Dbname=database1 host=127.0.0.1 '; --Database name and IP address of database node
return;
End IF;
RAISE EXCEPTION ' Unknown cluster '; --If the cluster name does not exist, throw an exception, which is handled inside the database and will eventually be written to the log. ' Unknown cluster ' is an error message
End;
$BODY $
LANGUAGE Plpgsql VOLATILE
Cost 100
ROWS 1000;
ALTER FUNCTION plproxy.get_cluster_partitions (text)
OWNER to Postgres;
(3) Establish plproxy.get_cluster_version function
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version (cluster_name text)
RETURNS Integer AS
$BODY $
BEGIN
IF cluster_name = ' Testcluster ' THEN
return 1;
End IF;
RAISE EXCEPTION ' Unknown cluster ';
End;
$BODY $
LANGUAGE Plpgsql VOLATILE
Cost 100;
ALTER FUNCTION plproxy.get_cluster_version (text)
OWNER to Postgres;

7, select the Proxy database in pgadmin3 public mode (by default), open Query tool, enter the following statements to establish 3 functions and execute
(1) Establish public.ddlexec (sql_request text) function
CREATE OR REPLACE FUNCTION ddlexec (query text)
RETURNS Setof Integer AS
$BODY $
CLUSTER ' Testcluster ';
RUN on all;
$BODY $
LANGUAGE Plproxy VOLATILE
Cost 100
ROWS 1000;
ALTER FUNCTION ddlexec (text)
OWNER to Postgres; (2) Establish the public.dmlexec (sql_request text) function, create OR REPLACE function dmlexec (query text)
RETURNS Setof Integer AS
$BODY $
CLUSTER ' Testcluster ';
RUN on any;
$BODY $
LANGUAGE Plproxy VOLATILE
Cost 100
ROWS 1000;
ALTER FUNCTION dmlexec (text)
OWNER to Postgres;
(3) Establish public.dqlexec (sql_request text) function
CREATE OR REPLACE FUNCTION dqlexec (query text)
RETURNS Setof Record as
$BODY $
CLUSTER ' Testcluster ';
RUN on all;
$BODY $
LANGUAGE Plproxy VOLATILE
Cost 100
ROWS 1000;
ALTER FUNCTION dqlexec (text)
OWNER to Postgres;
8, select the DATABASE0 and Database1 database in pgadmin3, open Query tool, enter the following SQL statement and press F5 to execute
Create language Plpgsql;
If you already have this language, it will be the same as the fourth step above the error, regardless, continue to go down on the line.
9, select the DATABASE0 and DATABASE1 database in pgadmin3 public mode (by default), open Query tool, enter the following statements to establish 3 functions and execute
(1) Establish public.ddlexec (sql_request text) function
CREATE OR REPLACE FUNCTION ddlexec (query text)
RETURNS Integer AS
$BODY $
Declare
RET integer;
Begin
Execute query;
return 1;
End;
$BODY $
LANGUAGE ' Plpgsql ' VOLATILE
Cost 100; (2) Establish public.dmlexec (sql_request text) function
CREATE OR REPLACE FUNCTION dmlexec (query text)
RETURNS Integer AS
$BODY $
Declare
RET integer;
Begin
Execute query;
return 1;
End;
$BODY $
LANGUAGE ' Plpgsql ' VOLATILE
Cost 100;
(3) Establish public.dqlexec (sql_request text) function
CREATE OR REPLACE FUNCTION dqlexec (query text)
RETURNS Setof Record as
$BODY $
Declare
RET record;
Begin
For RET in execute query loop
return next ret;
End Loop;
return;
End;
$BODY $
LANGUAGE ' Plpgsql ' VOLATILE
Cost 100
ROWS 1000; 10, so far, the cluster was created successfully, now can through proxy this node to the DATABASE0 and database1 to build tables, insert data, query data operations
(1) CREATE TABLE usertable
Select the proxy database in Pgadmin3, open Query tool, enter the following statement to establish the usertable and execute
Select Ddlexec (' CREATE TABLE usertable (ID integer) '); The above statement is executed, in the DATABASE0 and DATABASE1 database public mode should have the usertable this table, if not this table, that is the previous step error.
(2) inserting data in the Usertable table
Select the proxy database in Pgadmin3, open Query tool, enter the following statement to establish the usertable and execute
Select Dmlexec (' INSERT into usertable values (0) ');
Select Dmlexec (' INSERT into usertable values (1) ');
Select Dmlexec (' INSERT into usertable values (2) ');
Select Dmlexec (' INSERT into usertable values (3) ');
Select Dmlexec (' INSERT into usertable values (4) ');
Select Dmlexec (' INSERT into usertable values (5) ');
Select Dmlexec (' INSERT into usertable values (6) ');
Select Dmlexec (' INSERT into usertable values (7) ');
Select Dmlexec (' INSERT into usertable values (8) ');
Select Dmlexec (' INSERT into usertable values (9) ');
Select Dmlexec (' INSERT into usertable values (10) '); After the above statement is executed, you can see that there are 10 rows of data in the Usertable table of the two databases Database0 and Database1.
(3) Search the data in the Usertable table
Select the proxy database in Pgadmin3, open Query tool, enter the following statement to establish the usertable and execute
SELECT * FROM Dqlexec (' select * from Usertable ') as (id integer); Get the result is
3
4
5
6
7
8
10
0
1
2
9
All right, here we go.