pg叢集配置 http://keyvalue.net/linux%E5%B9%B3%E5%8F%B0%E6%8A%80%E6%9C%AF/%E8%BD%ACpg%E9%9B%86%E7%BE%A4%E

== 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);