PostgreSQL database cluster and PL/proxy configuration Installation Guide

 

Author: icyriver
Source: http://icyriver.net /? P = 176

 

 

PL/ProxyAndPostgreSQLThe structure relationship of the cluster can be clearly expressed. If you are not familiar with pL/proxy and PostgreSQL clusters, you can seeSkype plans for PostgreSQL to scale to 1 billion usersThis article.

The following operations are performed on three different machines. The machine names of the plproxy node are p1, and those of the database node are D1 and D2. The machine hardware configuration is as follows, while Linux-4.2, postgresql-8.3.0 and plproxy-2.0.4 are required, the installation process of pgbouncer is omitted.

Plproxy node:
Hostname: p1
Inet ADDR: 10.0.0.1
OS: Linux 2.6.9-42. elsmp
CPU: Intel (r) Xeon (r) CPU l5320 @ 1.86 GHz
Memtotal: 514440 KB

Node1 node:
Hostname: d1
Inet ADDR: 10.0.0.2
OS: Linux 2.6.9-42. elsmp
CPU: Intel (r) Xeon (r) CPU l5320 @ 1.86 GHz
Memtotal: 254772 KB

Node2 node:
Hostname: D2
Inet ADDR: 10.0.0.3
OS: Linux 2.6.9-42. elsmp
CPU: Intel (r) Xeon (r) CPU l5320 @ 1.86 GHz
Memtotal: 254772 KB

1. Install postgresql-8.3.0 on P1, D1, D2 and create a urtcluster Database

# Compile and install
Gunzip postgresql-8.3.0.tar.gz
Tar xf postgresql-8.3.0.tar
CD postgresql-8.3.0
./Configure-Prefix =/home/y/pgsql-with-perl
Gmake
Gmake check
Sudo gmake install

# Add UNIX user
Sudo adduser Postgres
Sudo mkdir/home/y/pgsql/Data
Sudo chown S/home/y/pgsql/Data

# Init dB and start service
Sudo-u Postgres/home/y/pgsql/bin/initdb-D/home/y/pgsql/Data
Sudo-u Postgres/home/y/pgsql/bin/Postgres-D/home/y/pgsql/data> logfile 2> & 1 &

# Create dB and use local connection
Sudo-u Postgres/home/y/pgsql/bin/createdb urtcluster

# Check whether the database has been created
Sudo-u Postgres/home/y/pgsql/bin/Psql-D urtcluster

# D1 and D2 must allow P1 access
# Edit PostgreSQL. conf to open the TCP connection Port
Sudo Vim/home/y/pgsql/data/PostgreSQL. conf
Listen_addresses = '*'
Port = 5432

# Add authentication for ipvs users
Sudo Vim/home/y/pgsql/data/pg_assist.conf
Host urtcluster ipvs 10.0.0.0/16 Trust

# Restarting the server
Sudo-u Postgres/home/y/pgsql/bin/pg_ctl-D/home/y/pgsql/Data stop
Sudo-u Postgres/home/y/pgsql/bin/Postgres-D/home/y/pgsql/data> logfile 2> & 1 &
Sudo-u Postgres/home/y/pgsql/bin/pg_ctl-D/home/y/pgsql/Data reload

2. Install plproxy-2.0.4 on p1

# Check whether the/home/y/pgsql/bin directory exists in the $ PATH variable. If not, modify your directory. in the bash_profile file, add/home/y/pgsql/bin to the path.
Echo $ path

Gunzip plproxy-2.0.4.tar.gz
Tar xf plproxy-2.0.4.tar
CD plproxy-2.0.4
Gmake
Sudo gmake install

# Create a plproxy
Sudo-u Postgres/home/y/pgsql/bin/Psql-F
/Home/y/pgsql/share/contrib/plproxy. SQL urtcluster

3. Install plpgsql on P1, D1, and D2

Sudo-u Postgres/home/y/pgsql/bin/createlang plpgsql urtcluster

4. Create a schema on p1

Sudo-u Postgres/home/y/pgsql/bin/Psql-D urtcluster
Urtcluster = # create schema plproxy;

5. initialize settings on p1

# Plproxy configuration is implemented through three functions (procedures). The standard templates for these three functions are as follows:

# This function allows plproxy to find the corresponding cluster
Create or replace function plproxy. get_cluster_partitions (cluster_name text)
Returns setof text as $
Begin
If cluster_name = 'urtcluster' then
Return next 'dbname = urtcluster host = 10.0.0.2 ′;
Return next 'dbname = urtcluster host = 10.0.0.3 ′;
Return;
End if;
Raise exception 'unknown cluster ';
End;
$ Language plpgsql;

# This function is used by plproxy to determine whether to return cached results to the front-end.
Create or replace function plproxy. get_cluster_version (cluster_name text)
Returns int4 as $
Begin
If cluster_name = 'urtcluster' then
Return 1;
End if;
Raise exception 'unknown cluster ';
End;
$ Language plpgsql;

# This function obtains the configurations of different clusters.
Create or replace function plproxy. get_cluster_config (cluster_name text, out key text, out Val text)
Returns setof record as $
Begin
Key: = 'Statement _ timeout ';
VAL: = 60;
Return next;
Return;
End;
$ Language plpgsql;

# Put the three functions in a urtclusterinit. SQL file and execute
Sudo-u Postgres/home/y/pgsql/bin/Psql-F urtclusterinit. SQL-D urtcluster-H 10.0.0.1

6. Set on D1 and D2 nodes

# Create a table users for each database Node
Create Table users (
Username text,
Email text
);

# Create an insert function for each database Node
Create or replace function insert_user (I _username text, I _emailaddress text)
Returns Integer as $
Insert into users (username, email) values ($1, $2 );
Select 1;
$ Language SQL;

# Save the function in the urtclusternodesinit_1. SQL file and execute
Sudo-u Postgres/home/y/pgsql/bin/Psql-F urtclusternodesinit_1. SQL-H 10.0.0.2-D urtcluster
Sudo-u Postgres/home/y/pgsql/bin/Psql-F urtclusternodesinit_1. SQL-H 10.0.0.3-D urtcluster

7. Set on P1 Node

# Create an insert function with the same name on the plproxy node for cluster search
Create or replace function insert_user (I _username text, I _emailaddress text)
Returns Integer as $
Cluster 'urtcluster ';
Run on hashtext (I _username );
$ Language plproxy;

# Create a query function on the plproxy node for cluster search
Create or replace function get_user_email (I _username text)
Returns text as $
Cluster 'urtcluster ';
Run on hashtext (I _username );
Select email from users where username = I _username;
$ Language plproxy;

# Save the function in the urtclusterproxyexec. SQL file and execute
Sudo-u Postgres/home/y/pgsql/bin/Psql-F urtclusterproxyexec_1. SQL-H 10.0.0.1-D urtcluster

8. Test results on p1

Sudo-u Postgres/home/y/pgsql/bin/Psql-D urtcluster

Select insert_user ('sven', 'sven @ somewhere.com ');
# It is saved to D2 and can be verified using select hashtext ('sven') & 1. It is hashed to partition 1.

Select insert_user ('marko ', 'marko @ somewhere.com ');
# It is saved to D2 and can be verified using select hashtext ('marko ') & 1. It is hashed to partition 1.

Select insert_user ('Steve ', 'Steve @ somewhere. CM ');
# It is saved to D1 and can be verified using select hashtext ('Steve ') & 1. It is hashed to partition 0.

Select get_user_email ('sven ');
Select get_user_email ('marko ');
Select get_user_email ('Steve ');

 

 

References:
Http://www.pgsqldb.org/mwiki/index.php/%E9%85%8D%E7%BD% AE %E4%B8%80%E4%B8%AA%E4%BD%BF%E7%94%A8plproxy%E7%9A%84PostgreSQL%E6%95%B0%E6%8D% AE %E5%BA%93%E9%9B%86%E7%BE%A4