imp导入数据到ORACLE遭遇ORA-12899错误
change mysql data directory
PostgreSQL的mulitmaster replication解决方案Cybercluster
[
2009/07/24 10:50 | by askwan ]
2009/07/24 10:50 | by askwan ]
简介：
Cybercluster是一种Postgresql数据库的mulitmaster replication solution, shared-nothing构架，基于BSD协议发放，由奥地利一家做基于postgresql数据库解决方案和服务的公司团队维护开发，cybercluster整体架构设计基于古老的pgcluster。
因工作原因，小小研究了一番。
四类节点：
LoadBlancer 节点 ：负责给客户端应用请求做负载均衡，在一个架构中是可选的节点
Cluster DB 节点：存储数据的节点，在一个架构中是必须的节点，只少要一个（这不废话吗，呵呵）
Replication Manager 节点： 负责分析和判断应用请求以及同步数据的节点
Monitor 节点 ：负责监控架构中各个cluster 节点
对于读请求，看下图
通过LoadBlancer 节点分配到负载最小的一个cluster db 节点
对于写请求，看下图
如果是写等修改数据库内容请求，则Replication Manager节点会连接每一个activer的 cluster db都执行同一操作
这个flash比较形象 ，http://www.postgresql.at/flash/cybercluster.swf
从这个意义上说，这种同步方式属于 SQL Statement类型的 ，而不是Row level型。
环境搭建：
这个可以参看软件包内说明，比较简单。
故障恢复：
cybercluster提供了三种cluster db node 故障恢复方式，例如，启动cluster db node 节点，指定
1. pg_ctl -D /usr/local/pgsql/data start -o "-R"
2. pg_ctl -D /usr/local/pgsql/data start -o "-u"
3. pg_ctl -D /usr/local/pgsql/data start -o "-U"
前两种方式属于cold recovery,依赖于架构配置好ssh + rsync 无密码访问，简言之，通过rsync来做文件级别的copy达到恢复数据的目地，这个比较特别
如下我做实验的恢复过程：
后一种则是基于逻辑意义上的恢复，
如下我做的是实验的恢复过程：
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
1st restore from pg_dump 1st exec:[/usr/local/pgsql/bin/pg_dumpall -i -o -c -h cluster1 -p 5432 -U postgres | /usr/local/pgsql/bin/psql -p 5432 template1]
You are now connected to database "postgres".
ERROR: current user cannot be dropped
STATEMENT: DROP ROLE postgres;
ERROR: current user cannot be dropped
ERROR: role "postgres" already exists
STATEMENT: CREATE ROLE postgres;
ERROR: role "postgres" already exists
ALTER ROLE
DROP DATABASE
CREATE DATABASE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "askwan01".
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "postgres".
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "template1".
COMMENT
REVOKE
REVOKE
GRANT
GRANT
->OK
2nd restore from pg_dump 2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres postgres | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d postgres]
.2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres askwan01 | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d askwan01]
.->OK
2nd recovery successed
OK! The data synchronization with Master DB was finished.
能清楚的看到这两个之间的区别。
监控节点:
没有什么好说的，做的非常简陋
感受：
优点：
1.架设简单。
2.支持负载均衡。
3.原理易懂，应用不需要做任何变更。
4.可扩展性比较好,各类型节点可以很容易的增加到集群中。
缺点：
1.文档其少，相关资料也少，要使用它，真得慎重
2.恢复的时候，Replication Manager节点配置文件中cluster db node位置得手动修改，希望高版本改进！
性能：
没有时间做测试，暂不评价！
Cybercluster是一种Postgresql数据库的mulitmaster replication solution, shared-nothing构架，基于BSD协议发放，由奥地利一家做基于postgresql数据库解决方案和服务的公司团队维护开发，cybercluster整体架构设计基于古老的pgcluster。
因工作原因，小小研究了一番。
四类节点：
LoadBlancer 节点 ：负责给客户端应用请求做负载均衡，在一个架构中是可选的节点
Cluster DB 节点：存储数据的节点，在一个架构中是必须的节点，只少要一个（这不废话吗，呵呵）
Replication Manager 节点： 负责分析和判断应用请求以及同步数据的节点
Monitor 节点 ：负责监控架构中各个cluster 节点
对于读请求，看下图
通过LoadBlancer 节点分配到负载最小的一个cluster db 节点
对于写请求，看下图
如果是写等修改数据库内容请求，则Replication Manager节点会连接每一个activer的 cluster db都执行同一操作
这个flash比较形象 ，http://www.postgresql.at/flash/cybercluster.swf
从这个意义上说，这种同步方式属于 SQL Statement类型的 ，而不是Row level型。
环境搭建：
这个可以参看软件包内说明，比较简单。
故障恢复：
cybercluster提供了三种cluster db node 故障恢复方式，例如，启动cluster db node 节点，指定
1. pg_ctl -D /usr/local/pgsql/data start -o "-R"
2. pg_ctl -D /usr/local/pgsql/data start -o "-u"
3. pg_ctl -D /usr/local/pgsql/data start -o "-U"
前两种方式属于cold recovery,依赖于架构配置好ssh + rsync 无密码访问，简言之，通过rsync来做文件级别的copy达到恢复数据的目地，这个比较特别
如下我做实验的恢复过程：
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
1st recovery step of [global] directory...OK
1st recovery step of [base] directory...OK
1st recovery step of [pg_clog] directory...OK
1st recovery step of [pg_xlog] directory...OK
1st sync_table_space OK
2nd recovery step of [global] directory...OK
2nd recovery step of [base] directory...OK
2nd recovery step of [pg_clog] directory...OK
2nd recovery step of [pg_xlog] directory...OK
2nd sync_table_space OK
2nd recovery successed
LOG: database system was interrupted; last known up at 2009-07-24 10:02:05 CST
LOG: database system was not properly shut down; automatic recovery in progress
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
OK! The data synchronization with Master DB was finished.
LOG: redo starts at 0/4FC8A0
LOG: record with zero length at 0/522AE4
LOG: redo done at 0/522AB8
LOG: last completed transaction was at log time 2009-07-24 10:01:53.911028+08
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
Please wait until a data synchronization finishes from Master DB...
1st recovery step of [global] directory...OK
1st recovery step of [base] directory...OK
1st recovery step of [pg_clog] directory...OK
1st recovery step of [pg_xlog] directory...OK
1st sync_table_space OK
2nd recovery step of [global] directory...OK
2nd recovery step of [base] directory...OK
2nd recovery step of [pg_clog] directory...OK
2nd recovery step of [pg_xlog] directory...OK
2nd sync_table_space OK
2nd recovery successed
LOG: database system was interrupted; last known up at 2009-07-24 10:02:05 CST
LOG: database system was not properly shut down; automatic recovery in progress
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
OK! The data synchronization with Master DB was finished.
LOG: redo starts at 0/4FC8A0
LOG: record with zero length at 0/522AE4
LOG: redo done at 0/522AB8
LOG: last completed transaction was at log time 2009-07-24 10:01:53.911028+08
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
后一种则是基于逻辑意义上的恢复，
如下我做的是实验的恢复过程：
Start in recovery mode!
Please wait until a data synchronization finishes from Master DB...
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
1st restore from pg_dump 1st exec:[/usr/local/pgsql/bin/pg_dumpall -i -o -c -h cluster1 -p 5432 -U postgres | /usr/local/pgsql/bin/psql -p 5432 template1]
You are now connected to database "postgres".
ERROR: current user cannot be dropped
STATEMENT: DROP ROLE postgres;
ERROR: current user cannot be dropped
ERROR: role "postgres" already exists
STATEMENT: CREATE ROLE postgres;
ERROR: role "postgres" already exists
ALTER ROLE
DROP DATABASE
CREATE DATABASE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "askwan01".
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "postgres".
REVOKE
REVOKE
GRANT
GRANT
You are now connected to database "template1".
COMMENT
REVOKE
REVOKE
GRANT
GRANT
->OK
2nd restore from pg_dump 2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres postgres | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d postgres]
.2nd exec:[/usr/local/pgsql/bin/pg_dump -i -Fc -o -b -h cluster1 -p 5432 -U postgres askwan01 | /usr/local/pgsql/bin/pg_restore -i -c -p 5432 -d askwan01]
.->OK
2nd recovery successed
OK! The data synchronization with Master DB was finished.
能清楚的看到这两个之间的区别。
监控节点:
没有什么好说的，做的非常简陋
postgres@testpg1$ pgrplmon rep1 9000
RPLMonitor v1.0
DB server #1
Hostname: cluster1
Resolved host: 192.168.233.144
Port: 5432
# of started transactions: 0
# of COMMITed transactions: 0
# of ROLLBACKed transactions: 0
# of INSERT statements: 13
# of UPDATE statements: 0
# of DELETE statements: 0
# of DDL statements: 6
DB server #2
Hostname: cluster2
Resolved host: 192.168.233.145
Port: 5432
# of started transactions: 0
# of COMMITed transactions: 0
# of ROLLBACKed transactions: 0
# of INSERT statements: 0
# of UPDATE statements: 0
# of DELETE statements: 0
# of DDL statements: 0
RPLMonitor v1.0
DB server #1
Hostname: cluster1
Resolved host: 192.168.233.144
Port: 5432
# of started transactions: 0
# of COMMITed transactions: 0
# of ROLLBACKed transactions: 0
# of INSERT statements: 13
# of UPDATE statements: 0
# of DELETE statements: 0
# of DDL statements: 6
DB server #2
Hostname: cluster2
Resolved host: 192.168.233.145
Port: 5432
# of started transactions: 0
# of COMMITed transactions: 0
# of ROLLBACKed transactions: 0
# of INSERT statements: 0
# of UPDATE statements: 0
# of DELETE statements: 0
# of DDL statements: 0
感受：
优点：
1.架设简单。
2.支持负载均衡。
3.原理易懂，应用不需要做任何变更。
4.可扩展性比较好,各类型节点可以很容易的增加到集群中。
缺点：
1.文档其少，相关资料也少，要使用它，真得慎重
2.恢复的时候，Replication Manager节点配置文件中cluster db node位置得手动修改，希望高版本改进！
性能：
没有时间做测试，暂不评价！
相关日志
use Pgpool-II and HeartBeat realize high availability of postgresql
Postgresql Warm Standby Testing
use pgFouine analyse postgresql slow queries
use DBD::Pg to connect PostgreSQL database
Mysql replication Error "Slave_SQL_Running:No"
use Pgpool-II and HeartBeat realize high availability of postgresql
Postgresql Warm Standby Testing
use pgFouine analyse postgresql slow queries
use DBD::Pg to connect PostgreSQL database
Mysql replication Error "Slave_SQL_Running:No"
作者：askwan@『AskWan』
地址：http://www.askwan.com/PostgreSQL_mulitmaster_replication_Cybercluster/
可以转载,转载时请以链接形式注明作者和原始出处及本声明！
.text:''):(d.getSelection?d.getSelection():'');void(keyit=window.open('http://my.poco.cn/fav/storeIt.php?t='+escape(d.title)+'&u='+escape(d.location.href)+'&c='+escape(t)+'&img=http://www.h-strong.com/blog/logo.gif','keyit','scrollbars=no,width=475,height=575,status=no,resizable=yes'));keyit.focus();){kind=logo}
