一、环境需求:
Ubuntu10.04以上
PostgreSQL8.4以上(貌似这样才能搭配Ubuntu10.04)
pgAdminIII
windows下若需要pgAdminIII,也一定要与ubuntu服务端版本一致才行,
否则肯能连数据表都看不见。
二、Ubuntu的配置
1. 修改root账户的密码以使root账户生效
plproxy@ubuntu:~$ sudo passwd root
[sudo] password for plproxy:
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
2. 以root权限登录
plproxy@ubuntu:~$ su
Password:
三、安装postgresql-8.4.4-1-linux.bin
1. 安装postgresql-8.4.4-1-linux.bin遇到权限问题
root@ubuntu:/home/plproxy/Desktop# ./postgresql-8.4.4-1-linux.bin
bash: ./postgresql-8.4.4-1-linux.bin: Permission denied
2. 修改postgresql-8.4.4-1-linux.bin的只读权限
root@ubuntu:/home/plproxy/Desktop# sudo chmod 777 postgresql-8.4.4-1-linux.bin
3. 开始安装bin包
root@ubuntu:/home/plproxy/Desktop#
./postgresql-8.4.4-1-linux.bin
4. 界面显示安装路径修改
Installation Directory:
/etc/postgresql/8.4
3. 下一步,安装数据路径修改(默认)
Data Directory:
/etc/postgresql/8.4/data
4. 下一步,输入PostgreSQL登录密码
Password:
ok123456
5. 下一步,修改端口
Port:
5432
6. 下一步,修改语言
Locale:
c
7. 下一步,在template1数据库中安装pl/pgsql
x Install pl/pgsql in template1 database?
8. 下一步,安装Stack Builder
x Stack Builder may be used to download and install additional tools,
drivers and applications to complement your PostgreSQL installation.
四、安装和配置PostgeSQL
1. 需要切换到root权限
2. 安装pgadmin3
root@ubuntu:/home/plproxy# sudo apt-get install pgadmin3
...
Do you want to continue [Y/n]? Y
...
3. 去掉data目录下所有文件的只读权限
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data -R
4. 编辑postgresql.config,完成后关闭
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/data/postgresql.config
listen_addresses = 'localhost' => listen_addresses = '*'
5. 编辑pg_hba.conf,完成后关闭
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/data/pg_hba.conf
列表中的METHOD可以是这些值:"trust", "reject", "md5", "password", "gss", "sspi", "krb5",
"ident", "pam", "ldap" or "cert".
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/24 trust
# IPv6 local connections:
host all all ::1/128 md5
6. 重启postgresql服务
root@ubuntu:/home/plproxy# /etc/init.d/postgresql-8.4 restart
启动遇到问题:
waiting for server to start...............................................................could not start server
PostgreSQL 8.4 did not start in a timely fashion, please see /etc/postgresql/8.4/data/pg_log/startup.log for details
7. 查看启动日志
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/pg_log/startup.log
2011-06-10 08:45:53 GMT FATAL: data directory "/etc/postgresql/8.4/data" has group or world access
2011-06-10 08:45:53 GMT DETAIL: Permissions should be u=rwx (0700).
6. 根据启动日志查看原来是将权限设置成了777而不是700
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data
then step5
五、在pgAdminIII中配置信息
1. 打开pgAdminIII
2. 新建数据库:
Name: MyCluster
OID:
Owner: postgres
Encoding: SQL_ASCII
Template: postgres
Tablespace: <default tablespace>
Schema restriction:
Collation: C
Character type: C
Connection Limit: -1
Comment:
点击OK按钮,出现错误,原来是编码提示
ERROR: new encoding(EUC_CN) is incompatible with the
encoding of the template database(SQL_ASCII)
HINT: Use the same encoding as in the template database,
or use template0 as template.
modify the Template template0:
Template: template0
3. 代理结点是A计算机,安装plproxy
proxy is the PC A.
root@ubuntu:/home/plproxy# sudo apt-get install postgresql-8.4-plproxy
Do you want to continue [Y/n]? Y
4. 安装plproxy语言到数据库JiwanCluster目录中PostgreSQL的数据表中的pg_language表。
root@ubuntu:/home/plproxy# /etc/postgresql/8.4/bin/psql -U postgres -h 192.168.0.10 -f /usr/share/postgresql-8.4-plproxy/plproxy.sql JiwanCluster
plproxy.sql脚本出现有问题:
psql:/usr/share/postgresql-8.4-plproxy/plproxy.sql:4: ERROR: could not access file "$libdir/plproxy": No such file or directory
root@ubuntu:/home/plproxy# sudo gedit /usr/share/postgresql-8.4-plproxy/plproxy.sql
修改脚本中的$libdir的路径为绝对路径,因为尝试过配置环境变量,貌似这个找不到。
the name $libdir is the ".so" file exists.
modify the $libdir as absolute path, as following:
/usr/lib/postgresql/8.4/lib
重新执行第4步.
root@ubuntu:/home/plproxy# /etc/postgresql/8.4/bin/psql -U postgres -h 192.168.0.10 -f /usr/share/postgresql-8.4-plproxy/plproxy.sql JiwanCluster
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE FOREIGN DATA WRAPPER
这样就创建了如下的两个函数:plproxy_call_handler和plproxy_fdw_validator
5. 创建plpgsql语言
root@ubuntu:/home/plproxy# sudo -u postgres /etc/postgresql/8.4/bin/createlang plpgsql "JiwanCluster"
Password:
6. 进入shell脚本,访问数据库JiwanCluster:
root@ubuntu:/home/plproxy# sudo -u postgres /etc/postgresql/8.4/bin/psql -d "JiwanCluster"
Password:
psql (8.4.4)
Type "help" for help.
为数据库JiwanCluster创建模式
JiwanCluster=# create schema plproxy;
CREATE SCHEMA
7. 退出Shell
JiwanCluster=# \q
could not save history to file "/etc/postgresql/8.4/.psql_history": Permission denied
8. 打开主结点proxy数据库JiwanCluster SQL编辑器,创建3个函数:
--增加函数,执行语句:select dmlexec('insert into usertable values(0)')
CREATE OR REPLACE FUNCTION dmlexec(query text)
RETURNS SETOF integer AS
$BODY$
CLUSTER 'jiwan';
RUN ON ANY;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
--删除函数,select * from dqlexec('delete from usertable')
CREATE OR REPLACE FUNCTION ddlexec(query text)
RETURNS SETOF integer AS
$BODY$
CLUSTER 'jiwan';
RUN ON ALL;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
--查询函数,select * from dqlexec('select * from usertable order by id') as (id integer)
CREATE OR REPLACE FUNCTION dqlexec(query text)
RETURNS SETOF record AS
$BODY$
CLUSTER 'jiwan';
RUN ON ALL;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
9. 注意:创建函数出现字符异常,可以在第一行随机敲注释,由pgAdminIII版本号不同造成的,尽量采用英文做注释
10. 出现pg_ctl提示错误
pg_ctl: could not send stop signal (PID: 926): No such process
是手动删除了这个文件,就会报下面的错误:
pg_ctl: PID file "/etc/PostgreSQL/8.4/data/postmaster.pid" does not exist
Is server running?
如果存在这个文件,打开可以看到如下3行:
--------------------------------------
926
/etc/PostgreSQL/8.4/data
5432001 0
--------------------------------------
这三行的含义:
第一行标识PID值,第二行标识这个postmaster.pid存储的位置,第三行什么意思?
但讨论这个毫无意义,postmaster.pid里边出现的三行仅仅是记录而已。
重启服务还报了个错误,需要查看/etc/PostSQL/8.4/data/pg_log/Startup.log
打开一看,才知道又是这个错误
2011-06-10 08:45:53 GMT FATAL: data directory "/etc/postgresql/8.4/data" has group or world access
2011-06-10 08:45:53 GMT DETAIL: Permissions should be u=rwx (0700).
修改data属性为700重启服务竟然就好了。
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data
11. 最重要的一点:节点数量必须是2的幂次方,即一个主结点+2的幂次方个次结点
即服务器配置如下:
服务器proxy->服务器A
->服务器B
但不能是奇数副结点
服务器proxy->服务器A
->服务器B
->服务器C
Ubuntu10.04以上
PostgreSQL8.4以上(貌似这样才能搭配Ubuntu10.04)
pgAdminIII
windows下若需要pgAdminIII,也一定要与ubuntu服务端版本一致才行,
否则肯能连数据表都看不见。
二、Ubuntu的配置
1. 修改root账户的密码以使root账户生效
plproxy@ubuntu:~$ sudo passwd root
[sudo] password for plproxy:
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
2. 以root权限登录
plproxy@ubuntu:~$ su
Password:
三、安装postgresql-8.4.4-1-linux.bin
1. 安装postgresql-8.4.4-1-linux.bin遇到权限问题
root@ubuntu:/home/plproxy/Desktop# ./postgresql-8.4.4-1-linux.bin
bash: ./postgresql-8.4.4-1-linux.bin: Permission denied
2. 修改postgresql-8.4.4-1-linux.bin的只读权限
root@ubuntu:/home/plproxy/Desktop# sudo chmod 777 postgresql-8.4.4-1-linux.bin
3. 开始安装bin包
root@ubuntu:/home/plproxy/Desktop#
./postgresql-8.4.4-1-linux.bin
4. 界面显示安装路径修改
Installation Directory:
/etc/postgresql/8.4
3. 下一步,安装数据路径修改(默认)
Data Directory:
/etc/postgresql/8.4/data
4. 下一步,输入PostgreSQL登录密码
Password:
ok123456
5. 下一步,修改端口
Port:
5432
6. 下一步,修改语言
Locale:
c
7. 下一步,在template1数据库中安装pl/pgsql
x Install pl/pgsql in template1 database?
8. 下一步,安装Stack Builder
x Stack Builder may be used to download and install additional tools,
drivers and applications to complement your PostgreSQL installation.
四、安装和配置PostgeSQL
1. 需要切换到root权限
2. 安装pgadmin3
root@ubuntu:/home/plproxy# sudo apt-get install pgadmin3
...
Do you want to continue [Y/n]? Y
...
3. 去掉data目录下所有文件的只读权限
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data -R
4. 编辑postgresql.config,完成后关闭
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/data/postgresql.config
listen_addresses = 'localhost' => listen_addresses = '*'
5. 编辑pg_hba.conf,完成后关闭
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/data/pg_hba.conf
列表中的METHOD可以是这些值:"trust", "reject", "md5", "password", "gss", "sspi", "krb5",
"ident", "pam", "ldap" or "cert".
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/24 trust
# IPv6 local connections:
host all all ::1/128 md5
6. 重启postgresql服务
root@ubuntu:/home/plproxy# /etc/init.d/postgresql-8.4 restart
启动遇到问题:
waiting for server to start...............................................................could not start server
PostgreSQL 8.4 did not start in a timely fashion, please see /etc/postgresql/8.4/data/pg_log/startup.log for details
7. 查看启动日志
root@ubuntu:/home/plproxy# sudo gedit /etc/postgresql/8.4/pg_log/startup.log
2011-06-10 08:45:53 GMT FATAL: data directory "/etc/postgresql/8.4/data" has group or world access
2011-06-10 08:45:53 GMT DETAIL: Permissions should be u=rwx (0700).
6. 根据启动日志查看原来是将权限设置成了777而不是700
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data
then step5
五、在pgAdminIII中配置信息
1. 打开pgAdminIII
2. 新建数据库:
Name: MyCluster
OID:
Owner: postgres
Encoding: SQL_ASCII
Template: postgres
Tablespace: <default tablespace>
Schema restriction:
Collation: C
Character type: C
Connection Limit: -1
Comment:
点击OK按钮,出现错误,原来是编码提示
ERROR: new encoding(EUC_CN) is incompatible with the
encoding of the template database(SQL_ASCII)
HINT: Use the same encoding as in the template database,
or use template0 as template.
modify the Template template0:
Template: template0
3. 代理结点是A计算机,安装plproxy
proxy is the PC A.
root@ubuntu:/home/plproxy# sudo apt-get install postgresql-8.4-plproxy
Do you want to continue [Y/n]? Y
4. 安装plproxy语言到数据库JiwanCluster目录中PostgreSQL的数据表中的pg_language表。
root@ubuntu:/home/plproxy# /etc/postgresql/8.4/bin/psql -U postgres -h 192.168.0.10 -f /usr/share/postgresql-8.4-plproxy/plproxy.sql JiwanCluster
plproxy.sql脚本出现有问题:
psql:/usr/share/postgresql-8.4-plproxy/plproxy.sql:4: ERROR: could not access file "$libdir/plproxy": No such file or directory
root@ubuntu:/home/plproxy# sudo gedit /usr/share/postgresql-8.4-plproxy/plproxy.sql
修改脚本中的$libdir的路径为绝对路径,因为尝试过配置环境变量,貌似这个找不到。
the name $libdir is the ".so" file exists.
modify the $libdir as absolute path, as following:
/usr/lib/postgresql/8.4/lib
重新执行第4步.
root@ubuntu:/home/plproxy# /etc/postgresql/8.4/bin/psql -U postgres -h 192.168.0.10 -f /usr/share/postgresql-8.4-plproxy/plproxy.sql JiwanCluster
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE FOREIGN DATA WRAPPER
这样就创建了如下的两个函数:plproxy_call_handler和plproxy_fdw_validator
5. 创建plpgsql语言
root@ubuntu:/home/plproxy# sudo -u postgres /etc/postgresql/8.4/bin/createlang plpgsql "JiwanCluster"
Password:
6. 进入shell脚本,访问数据库JiwanCluster:
root@ubuntu:/home/plproxy# sudo -u postgres /etc/postgresql/8.4/bin/psql -d "JiwanCluster"
Password:
psql (8.4.4)
Type "help" for help.
为数据库JiwanCluster创建模式
JiwanCluster=# create schema plproxy;
CREATE SCHEMA
7. 退出Shell
JiwanCluster=# \q
could not save history to file "/etc/postgresql/8.4/.psql_history": Permission denied
8. 打开主结点proxy数据库JiwanCluster SQL编辑器,创建3个函数:
--增加函数,执行语句:select dmlexec('insert into usertable values(0)')
CREATE OR REPLACE FUNCTION dmlexec(query text)
RETURNS SETOF integer AS
$BODY$
CLUSTER 'jiwan';
RUN ON ANY;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
--删除函数,select * from dqlexec('delete from usertable')
CREATE OR REPLACE FUNCTION ddlexec(query text)
RETURNS SETOF integer AS
$BODY$
CLUSTER 'jiwan';
RUN ON ALL;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
--查询函数,select * from dqlexec('select * from usertable order by id') as (id integer)
CREATE OR REPLACE FUNCTION dqlexec(query text)
RETURNS SETOF record AS
$BODY$
CLUSTER 'jiwan';
RUN ON ALL;
$BODY$
LANGUAGE 'plproxy' VOLATILE
COST 100
ROWS 1000;
9. 注意:创建函数出现字符异常,可以在第一行随机敲注释,由pgAdminIII版本号不同造成的,尽量采用英文做注释
10. 出现pg_ctl提示错误
pg_ctl: could not send stop signal (PID: 926): No such process
是手动删除了这个文件,就会报下面的错误:
pg_ctl: PID file "/etc/PostgreSQL/8.4/data/postmaster.pid" does not exist
Is server running?
如果存在这个文件,打开可以看到如下3行:
--------------------------------------
926
/etc/PostgreSQL/8.4/data
5432001 0
--------------------------------------
这三行的含义:
第一行标识PID值,第二行标识这个postmaster.pid存储的位置,第三行什么意思?
但讨论这个毫无意义,postmaster.pid里边出现的三行仅仅是记录而已。
重启服务还报了个错误,需要查看/etc/PostSQL/8.4/data/pg_log/Startup.log
打开一看,才知道又是这个错误
2011-06-10 08:45:53 GMT FATAL: data directory "/etc/postgresql/8.4/data" has group or world access
2011-06-10 08:45:53 GMT DETAIL: Permissions should be u=rwx (0700).
修改data属性为700重启服务竟然就好了。
root@ubuntu:/home/plproxy# sudo chmod 700 /etc/postgresql/8.4/data
11. 最重要的一点:节点数量必须是2的幂次方,即一个主结点+2的幂次方个次结点
即服务器配置如下:
服务器proxy->服务器A
->服务器B
但不能是奇数副结点
服务器proxy->服务器A
->服务器B
->服务器C