PostgreSQL集群 http://icyriver.net/?p=176

朴灿烈づ我的快乐病毒、 2022-08-07 01:47 21阅读 0赞

[PL/Proxy][PL_Proxy] 和[PostgreSQL][] 集群的结构关系可以用下图清楚地表示，对PL/Proxy和PostgreSQL集群还不太了解的朋友可以看[Skype Plans for PostgreSQL to Scale to 1 Billion Users][] 这篇文章。

[![pgsql cluster][] ][pgsql cluster_]

以下操作是在三台不同机器上执行的情况，其中plproxy节点的机器名是P1，数据库节点的机器名分别是D1和D2。机器硬件配置如下，同时需要Linux-4.2、postgresql-8.3.0和plproxy-2.0.4，pgbouncer的安装过程略去。

plproxy节点:  
hostname: P1  
inet addr:10.0.0.1  
OS: Linux 2.6.9-42.ELsmp  
CPU:Intel(R) Xeon(R) CPU L5320 @ 1.86GHz  
MemTotal: 514440 kB

node1节点:  
hostname:D1  
inet addr:10.0.0.2  
OS: Linux 2.6.9-42.ELsmp  
CPU:Intel(R) Xeon(R) CPU L5320 @ 1.86GHz  
MemTotal: 254772 kB

node2节点:  
hostname:D2  
inet addr:10.0.0.3  
OS: Linux 2.6.9-42.ELsmp  
CPU:Intel(R) Xeon(R) CPU L5320 @ 1.86GHz  
MemTotal: 254772 kB

1. 在P1, D1,D2上安装postgresql-8.3.0，并创建URTCluster数据库

\#\# 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 postgres /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

\#\#检查数据库是否已经创建  
sudo -u postgres /home/y/pgsql/bin/psql -d URTCluster

\#D1,D2必须允许P1访问  
\#编辑postgresql.conf,打开tcp连接端口  
sudo vim /home/y/pgsql/data/postgresql.conf  
listen\_addresses = ‘\*’  
port = 5432

\#添加postgres用户的认证  
sudo vim /home/y/pgsql/data/pg\_hba.conf  
host URTCluster postgres 10.0.0.0/16 trust

\# 重起服务器  
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. 在P1上安装plproxy-2.0.4

\#检查$PATH变量里是否有/home/y/pgsql/bin目录，如果没有，修改你的.bash\_profile文件，添加/home/y/pgsql/bin到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

\#创建plproxy  
sudo -u postgres /home/y/pgsql/bin/psql -f  
/home/y/pgsql/share/contrib/plproxy.sql URTCluster

3. 在P1, D1,D2上安装plpgsql

sudo -u postgres /home/y/pgsql/bin/createlang plpgsql URTCluster

4. 在P1上创建schema

sudo -u postgres /home/y/pgsql/bin/psql -d URTCluster  
URTCluster=\# create schema plproxy;

5. 在P1上初始化设置

\#plproxy的配置是通过三个函数（过程）实现的，这三个函数的标准模版如下：

\#这个函数是让plproxy可以找到对应的集群  
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;

\#这个函数是plproxy用于判断是否给前端返回已经cache过的结果用的  
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;

\#这个函数是获取不同的集群的配置  
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;

\#把这三个函数放在一个URTClusterInit.sql文件里，并执行  
sudo -u postgres /home/y/pgsql/bin/psql -f URTClusterInit.sql -d URTCluster -h 10.0.0.1

6. 在D1，D2节点上设置

\#给每个数据库节点都创建一张表users  
CREATE TABLE users (  
username text,  
email text  
);

\#给每个数据库节点都创建一个插入函数  
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;

\#把函数保存在 URTClusterNodesInit\_1.sql文件里，并执行  
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. 在P1节点上设置

\#在 plproxy 节点上创建一个同名的插入函数，用于进行集群检索  
CREATE OR REPLACE FUNCTION insert\_user(i\_username text, i\_emailaddress text)  
RETURNS integer AS $$  
CLUSTER ‘URTCluster’;  
RUN ON hashtext(i\_username);  
$$ LANGUAGE plproxy;

\#在 plproxy 节点上创建一个查询函数，用于进行集群检索  
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;

\#把函数保存在 URTClusterProxyExec.sql文件里，并执行  
sudo -u postgres /home/y/pgsql/bin/psql -f URTClusterProxyExec\_1.sql -h 10.0.0.1 -d URTCluster

8. 在P1上测试结果

sudo -u postgres /home/y/pgsql/bin/psql -d URTCluster

SELECT insert\_user(’Sven’,’sven@somewhere.com’);  
\#被保存到D2， 可以用select hashtext(’Sven’) & 1验证，被hash到 partition 1

SELECT insert\_user(’Marko’, ‘marko@somewhere.com’);  
\#被保存到D2， 可以用select hashtext(’Marko’) & 1验证，被hash到 partition 1

SELECT insert\_user(’Steve’,’steve@somewhere.cm’);  
\#被保存到D1， 可以用select hashtext(’Steve’) & 1验证，，被hash到 partition 0

SELECT get\_user\_email(’Sven’);  
SELECT get\_user\_email(’Marko’);  
SELECT get\_user\_email(’Steve’);

[icyriver][] , [internet][] , [postgresql][] , [programming][] , [technique][]

--------------------

« [PostgreSQL 数据库集群和PL/Proxy配置安装指南][PostgreSQL _PL_Proxy]

[四川汶川地震][Link 1] »

--------------------

### [PostgreSQL和Slony-I的安装和配置][PostgreSQL_Slony-I] ###

Published by [wesley.he][] May 12th, 2008 in [icyriver][icyriver 1] and [technique][technique 1] . ![user.png][] 浏览次数: 13775 Tags: [icyriver][] , [internet][] , [postgresql][] , [programming][] , [technique][] .

随着[Mysql][] 被[Sun][] 收购，不少web开发者和架构师开始关注PostgreSQL。的确，[PostgreSQL][] 和[Slony-I][] 、[PL/Proxy][PL_Proxy] 、[Pgbouncer][] 已经可以为我们提供一套比较完整的企业级数据库存储解决方案，其web架构如下图所示：

[![read\_only\_cluster][read_only_cluster] ][read_only_cluster_read_only_cluster_]

其中，[PostgreSQL][] 和[PL/Proxy][PL_Proxy] 的安装和配置已经在上一篇Blog介绍过，下面是[Slony-I][] 的安装配置简明指南，实现主副数据库的同步。后面我会再介绍[Pgbouncer][] 的安装和配置

1. 主副数据库机器

Master:  
hostname: M\_DB  
inet addr:10.0.0.11  
OS: Linux 2.6.9-42.ELsmp  
CPU:Intel(R) Xeon(R) CPU L5320 @ 1.86GHz  
MemTotal: 254772 kB  
PgSQL: postgresql-8.3.0

Slave:  
hostname:S\_DB  
inet addr:10.0.0.12  
OS: Linux 2.6.9-42.ELsmp  
CPU:Intel(R) Xeon(R) CPU L5320 @ 1.86GHz  
MemTotal: 514440 kB  
PgSQL: postgresql-8.3.0

\#在M\_DB和S\_DB上安装postgresql-8.3.0, 安装和配置过程参见我的[上一篇Blog][PostgreSQL _PL_Proxy] ，确保超级用户是postgres，数据库名是URT。

\#检查M\_DB和S\_DB上的超级用户postgres是否可以访问对方的机器

\#分别在M\_DB和S\_DB上执行  
sudo -u postgres /home/y/pgsql/bin/createlang plpgsql URT

\#分别在M\_DB和S\_DB上的URT数据库里创建相同的表accounts。

2. 安装Slony-I

\#分别在M\_DB和S\_DB上安装Slony-I  
tar xfj slony1-1.2.13.tar.bz2  
cd slony1-1.2.13  
./configure –with-pgconfigdir=/home/y/pgsql/bin  
gmake all  
sudo gmake install

3. Slony Config

创建urt\_replica\_init.sh文件:  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#!/bin/sh

SLONIK=/home/y/pgsql/bin/slonik  
\#slonik可执行文件位置

CLUSTER=URT  
\#你的集群的名称

SET\_ID=1  
\#你的复制集的名称

MASTER=1  
\#主服务器ID

HOST1=M\_DB  
\#源库IP或主机名

DBNAME1=URT  
\#需要复制的源数据库

SLONY\_USER=postgres  
\#源库数据库超级用户名

SLAVE=2  
\#从服务器ID

HOST2=S\_DB  
\#目的库IP或主机名

DBNAME2=URT  
\#需要复制的目的数据库

PGBENCH\_USER=postgres  
\#目的库用户名

$SLONIK <<\_EOF\_

\#这句是定义集群名  
cluster name = $CLUSTER;

\#这两句是定义复制节点  
node $MASTER admin conninfo = ‘dbname=$DBNAME1 host=$HOST1 user=$SLONY\_USER ‘;  
node $SLAVE admin conninfo = ‘dbname=$DBNAME2 host=$HOST2 user=$PGBENCH\_USER ‘;

\#初始化集群和主节点，id从1开始，如果只有一个集群，那么肯定是1  
\#comment里可以写一些自己的注释，随意  
init cluster ( id = $MASTER, comment = ‘Primary Node’ );

\#下面是从节点  
store node ( id = $SLAVE, comment = ‘Slave Node’ );

\#配置主从两个节点的连接信息，就是告诉Slave服务器如何来访问Master服务器  
\#下面是主节点的连接参数  
store path ( server = $MASTER, client = $SLAVE,  
conninfo = ‘dbname=$DBNAME1 host=$HOST1 user=$SLONY\_USER ‘);

\#下面是从节点的连接参数  
store path ( server = $SLAVE, client = $MASTER,  
conninfo = ‘dbname=$DBNAME2 host=$HOST2 user=$PGBENCH\_USER ‘);

\#设置复制中角色，主节点是原始提供者，从节点是接受者  
store listen ( origin = $MASTER, provider = 1, receiver = 2 );  
store listen ( origin = $SLAVE, provider = 2, receiver = 1 );

\#创建一个复制集，id也是从1开始  
create set ( id = $SET\_ID, origin = $MASTER, comment = ‘All pgbench tables’ );

\#向自己的复制集种添加表，每个需要复制的表添加一条set命令，id从1开始，逐次递加，步进为1；  
\#fully qualified name是表的全称：模式名.表名  
\#这里的复制集id需要和前面创建的复制集id一致  
set add table ( set id = $SET\_ID, origin = $MASTER,  
id = 1, fully qualified name = ‘public.accounts’,  
comment = ‘Table accounts’ );

\_EOF\_  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\#在M\_DB或者S\_DB上执行  
./urt\_replica\_init.sh

4. Slony Start

创建Master.slon文件:  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
cluster\_name=”URT”  
conn\_info=”dbname=URT host=M\_DB user=postgres”  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

创建Slave.slon文件:  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
cluster\_name=”URT”  
conn\_info=”dbname=URT host=S\_DB user=postgres”  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\#在M\_DB上执行  
/home/y/pgsql/bin/slon -f master.slon >> master.log &

\#在S\_DB上执行  
/home/y/pgsql/bin/slon -f slave.slon >> slave.log &

5. Slony Subscribe

创建urt\_replica\_subscribe.sh文件:  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
\#!/bin/sh

SLONIK=/home/y/pgsql/bin/slonik  
\#slonik可执行文件位置

CLUSTER=URT  
\#你的集群的名称

SET\_ID=1  
\#你的复制集的名称

MASTER=1  
\#主服务器ID

HOST1=M\_DB  
\#源库IP或主机名

DBNAME1=URT  
\#需要复制的源数据库

SLONY\_USER=postgres  
\#源库数据库超级用户名

SLAVE=2  
\#从服务器ID

HOST2=S\_DB  
\#目的库IP或主机名

DBNAME2=URT  
\#需要复制的目的数据库

PGBENCH\_USER=postgres  
\#目的库用户名

$SLONIK <<\_EOF\_

\#这句是定义集群名  
cluster name = $CLUSTER;

\#这两句是定义复制节点  
node $MASTER admin conninfo = ‘dbname=$DBNAME1 host=$HOST1 user=$SLONY\_USER’;  
node $SLAVE admin conninfo = ‘dbname=$DBNAME2 host=$HOST2 user=$PGBENCH\_USER ‘;

\#提交复制集  
subscribe set ( id = $SET\_ID, provider = $MASTER, receiver = $SLAVE, forward = no);  
\_EOF\_  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

\#在M\_DB或者S\_DB上执行  
./urt\_replica\_subscribe.sh

6. 测试  
修改M\_DB上URT数据里的accounts表，S\_DB上的accounts表也会随之改变

下图是Slony-I内部架构，可以看到Slony-I用到了很多线程来实现事件和数据的传递和分发。

[![slony][] ][slony_]

[icyriver][] , [internet][] , [postgresql][] , [programming][] , [technique][]

[PL_Proxy]: http://pgfoundry.org/projects/plproxy/
[PostgreSQL]: http://www.postgresql.org/
[Skype Plans for PostgreSQL to Scale to 1 Billion Users]: http://highscalability.com/skype-plans-postgresql-scale-1-billion-users
[pgsql cluster]: http://farm3.static.flickr.com/2299/2479244875_a53f87a5e3_o.jpg
[pgsql cluster_]: http://www.flickr.com/photos/icyriver/2479244875/
[icyriver]: http://icyriver.net/tag/icyriver
[internet]: http://icyriver.net/tag/internet
[postgresql]: http://icyriver.net/tag/postgresql
[programming]: http://icyriver.net/tag/programming
[technique]: http://icyriver.net/tag/technique
[PostgreSQL _PL_Proxy]: http://icyriver.net/?p=176
[Link 1]: http://icyriver.net/?p=178
[PostgreSQL_Slony-I]: http://icyriver.net/?p=177
[wesley.he]: http://icyriver.net/?author=1
[icyriver 1]: http://icyriver.net/?cat=1
[technique 1]: http://icyriver.net/?cat=6
[user.png]: /images/20220805/799c373d77c741ce8d15f5caa3a6e30c.png
[Mysql]: http://www.mysql.com/
[Sun]: http://www.sun.com/
[Slony-I]: http://pgfoundry.org/projects/slony1
[Pgbouncer]: http://pgfoundry.org/projects/pgbouncer
[read_only_cluster]: http://farm4.static.flickr.com/3196/2480279882_16ecb3d443_o.jpg
[read_only_cluster_read_only_cluster_]: http://www.flickr.com/photos/icyriver/2480279882/
[slony]: http://farm3.static.flickr.com/2065/2479640150_e8a6ce34d7.jpg
[slony_]: http://www.flickr.com/photos/icyriver/2479640150/