战争与和平 思考能力

= U.R.T PgSQL 集群 开发环境安装 =

== 1. Machine ==

PL/Proxy
hostname:h08-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.152
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 254772 kB
Pg_Dir: /home/y/pgsql/data/
port = 5432

Node1:
hostname:h08-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.152
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 254772 kB
Pg_Dir: /usr/local/pgsql/data/
port = 5433

Node2:
hostname:h07-vm08.corp.cnb.yahoo.com
inet addr:10.62.245.136
Bcast:10.62.245.255
Mask:255.255.254.0
OS: Linux  2.6.9-42.ELsmp
CPU:Intel(R) Xeon(R) CPU  L5320  @ 1.86GHz
MemTotal: 514440 kB
Pg_Dir: /usr/local/pgsql/data/
port = 5433

== 2. Install ==

#在PL/Proxy、Node1、Node2节点上执行以下命令：

sudo rpm -ivh postgres_4e_alone-8.3.1.0.rpm
sudo rpm -ivh postgres_4e_contrib-8.3.0.1.rpm
sudo rpm -ivh pgbouncer_4e-1.0.0.0.rpm

== 3. Init ==

#在PL/Proxy、Node1、Node2节点上执行以下命令：

## Add Unix User
sudo adduser postgres
sudo mkdir /usr/local/pgsql/data
sudo chown postgres /usr/local/pgsql/data
sudo visudo
**Add "DEVEL ALL=(postgres) ALL" in the last line
**Apend "hebing" to "User_Alias DEVEL"

## Init DB and Start service
sudo -u postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
sudo -u postgres /usr/local/pgsql/bin/postgres -D  /usr/local/pgsql/data >logfile 2>&1 &

## Create DB and Use Local Connection
sudo -u postgres /usr/local/pgsql/bin/createdb URT

##检查数据库是否已经创建
sudo -u postgres /usr/local/pgsql/bin/psql -d URT
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop

#继续在PL/Proxy节点上执行以下命令(安装plproxy节点)：
sudo mkdir /home/y/pgsql/data
sudo chown postgres /home/y/pgsql/data
sudo -u postgres /usr/local/pgsql/bin/initdb -D /home/y/pgsql/data
sudo -u postgres  /usr/local/pgsql/bin/postgres -D /home/y/pgsql/data >logfile_1 2>&1 &
sudo -u postgres /usr/local/pgsql/bin/createdb URT
sudo -u postgres /usr/local/pgsql/bin/psql -d URT
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /home/y/pgsql/data stop

== 4. config ==

#在PL/Proxy、Node1、Node2节点上执行以下命令：

## 检查tcp连接端口是否已经配置，默认安装已经配置好
sudo vim /usr/local/pgsql/data/postgresql.conf
listen_addresses = '*'
port = 5433

## PgSQL是基于主机的认证(HBA："host-based authentication")
sudo vim /usr/local/pgsql/data/pg_hba.conf
## 根据需要添加IP地址、数据库和角色名。IP一般是前端机IP和本地IP，角色在上一步骤中已经添加
host    URT         postgres         10.62.0.1/16          trust

## 启动服务器
sudo -u postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data reload

#在PL/Proxy、Node1、Node2节点上执行以下命令：
sudo vim /home/y/pgsql/data/postgresql.conf
listen_addresses = '*'
port = 5432

sudo vim /home/y/pgsql/data/pg_hba.conf
## 根据需要添加IP地址、数据库和角色名。IP一般是前端机IP和本地IP，角色在上一步骤中已经添加
host    URT         postgres         10.62.0.1/16          trust

sudo -u postgres /usr/local/pgsql/bin/postgres -D /home/y/pgsql/data >logfile_1 2>&1 &
sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /home/y/pgsql/data reload

## 检查PL/Proxy节点是否可以访问Node1和Node2节点上的数据库,检查是否可以访问PL/Proxy节点
[hebing@h08-vm08 ~]$sudo -u postgres /usr/local/pgsql/bin/psql -d URT  -h h08-vm08.corp.cnb.yahoo.com -p 5433
[hebing@h08-vm08 ~]$sudo -u postgres /usr/local/pgsql/bin/psql -d URT  -h h07-vm08.corp.cnb.yahoo.com -p 5433
[hebing@h08-vm08 ~]$sudo -u postgres /usr/local/pgsql/bin/psql -d URT  -h h08-vm08.corp.cnb.yahoo.com -p 5432

== 5. Install plpgsql and plproxy ==

#在plproxy、Node1、Node2节点上安装plpgsql，包里面已经安装了。
#如果没有安装plpgsql，可以参考以下步骤：
sudo -u postgres /usr/local/pgsql/bin/createlang plpgsql URT -p 5433 

#在plproxy、Node1、Node2节点上安装plproxy，包里面已经安装了。
#如果没有安装plporxy，可以参考以下步骤：
#检查是否有/usr/local/pgsql/bin目录，如果没有，修改你的.bash_profile文件，添加/usr/local/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
sudo -u postgres /usr/local/pgsql/bin/psql -f /usr/local/pgsql/share/contrib/plproxy.sql URT

== 6. Config cluster in plproxy ==

#创建一个plproxy schema，在plproxy里配置集群，注意要连接pgbouncer；放在一个sql脚本里;
sudo -u postgres /usr/local/pgsql/bin/psql -d URT  -p 5432 -f cluster.init.sql

== 7. Config DB Node ==

#在Node1、Node2节点上添加操作函数(这里只写了几个简单的函数,需要添加URT的业务逻辑函数)
sudo -u postgres /usr/local/pgsql/bin/psql -d URT -p 5433 -f node.sql

== 8. Config plproxy ==

#在plproxy节点上添加操作函数(这里只写了几个简单的函数,需要添加URT的业务逻辑函数)
sudo -u postgres /usr/local/pgsql/bin/psql -d URT -p 5432 -f plproxy.sql

== 9. Config pgbouncer ==

#在plproxy节点上执行以下操作

#在plproxy节点上安装pgbouncer，包里面已经安装了。
#如果没有安装pgbouncer，可以参考以下步骤：
./configure --prefix=/usr/local --with-libevent=/prefix
make
sudo make install

#修改pgbouncer.ini文件
sudo vim /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.ini
#添加一下内容
#######################
Node1 = host=10.62.245.152 port=5433 user=postgres dbname=URT
Node2 = host=10.62.245.136 port=5433 user=postgres dbname=URT
listen_addr = 127.0.0.1
listen_port = 6543
auth_file = /usr/local/pgsql/share/doc/pgbouncer/users.txt
logfile = /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.log
pidfile = /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.pid
admin_users = user2, someadmin, otheradmin,postgres
stats_users = stats, root,postgres
#########################

#创建users.txt文件
sudo vim /usr/local/pgsql/share/doc/pgbouncer/users.txt
#添加 "postgres" ""

#start pgbouncer
sudo /usr/local/pgsql/bin/pgbouncer -d /usr/local/pgsql/share/doc/pgbouncer/pgbouncer.ini

#测试
sudo -u postgres /usr/local/pgsql/bin/psql -p 6543 -d pgbouncer -U postgres
pgbouncer=# show databases;

sudo -u postgres /usr/local/pgsql/bin/psql -h 127.0.0.1 -p 6543 -d Node1 -U postgres
sudo -u postgres /usr/local/pgsql/bin/psql -h 127.0.0.1 -p 6543 -d Node2 -U postgres

== 10. 导入ltree测试数据 ==

#在plproxy、Node1、Node2节点上安装ltree类型，包里面已经安装了。
#如果没有,可以参考一下步骤
#先安装 postgresql-8.3.0, 安装目录会保存在配置文件里。然后ltree模块：
cd postgresql-8.3.0/contrib/ltree/
make
sudo make install
#安装ltree类型
sudo -u postgres /home/y/pgsql/bin/psql -d URTCluster -f /home/y/pgsql/sha
re/contrib/ltree.sql
#重新载入配置
sudo -u postgres /home/y/pgsql/bin/pg_ctl -D /home/y/pgsql/data reload

#在Node1、Node2节点上执行数据导入操作
wget http://www.sai.msu.su/~megera/postgres/gist/ltree/dmozltree-eng.sql.gz
gunzip dmozltree-eng.sql.gz
sudo -u postgres /usr/local/pgsql/bin/psql -d URT -f dmozltree-eng.sql -p 5433

== 11. 测试 ==

#在plproxy节点上执行
sudo -u postgres /usr/local/pgsql/bin/psql -d URT -p 5432

URT=# SELECT  * from public.xquery('select * from dmoz limit 1 offset 10;') as dmoz(id int4, name text, path ltree);

URT=# SELECT  * from public.xquery('select path from dmoz where path ~ ''Top.Adult.Arts.Animation.*{1}'';') as dmoz(id int4, name text, path ltree);