『AskWan』

Pages: 7/59

2 3 4 5 6 7 8 9 10 11

[ View by Articles | List ]

PostgreSQL的mulitmaster replication解决方案Cybercluster

[

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...
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

感受：

优点：
1.架设简单。
2.支持负载均衡。
3.原理易懂，应用不需要做任何变更。
4.可扩展性比较好,各类型节点可以很容易的增加到集群中。

缺点：
1.文档其少，相关资料也少，要使用它，真得慎重
2.恢复的时候，Replication Manager节点配置文件中cluster db node位置得手动修改，希望高版本改进！

性能：
没有时间做测试，暂不评价！

DataBase | Comments(0) | Trackbacks(0) | Reads(643)

"There is no public key available for the following key IDs" 问题

[

2009/06/08 23:53 | by askwan ]

OS: debian 4.0 etch
此问题比较普遍故记录之且供参考
一般都是在apt-get update 后出错：

W: There is no public key available for the following key IDs:
9AA38DCD55BE302B
W: You may want to run apt-get update to correct these problems

按照提示运行apt-get update这个指令很遗憾无论运行多少次都无济于事

为演示这一过程先下apt-key指令 import当前的keys
# apt-key update

gpg: key 2D230C5F: "Debian Archive Automatic Signing Key (2006) <ftpmaster@debian.org>" not changed
gpg: key 6070D3A1: "Debian Archive Automatic Signing Key (4.0/etch) <ftpmaster@debian.org>" not changed
gpg: key ADB11277: "Etch Stable Release Key <debian-release@lists.debian.org>" not changed
gpg: key BBE55AB3: "Debian-Volatile Archive Automatic Signing Key (4.0/etch)" not changed
gpg: Total number processed: 4
gpg: unchanged: 4

当前系统有4个keys

增加上面列出的key
gpg --keyserver wwwkeys.eu.pgp.net --recv-keys  9AA38DCD55BE302B && apt-key add /root/.gnupg/pubring.gpg

再来看看系统中的trusted gpg keys信息
# apt-key list
/etc/apt/trusted.gpg
--------------------
pub   1024D/2D230C5F 2006-01-03 [expired: 2007-02-07]
uid                  Debian Archive Automatic Signing Key (2006)

pub   1024D/6070D3A1 2006-11-20 [expires: 2009-07-01]
uid                  Debian Archive Automatic Signing Key (4.0/etch)

pub   1024D/ADB11277 2006-09-17
uid                  Etch Stable Release Key

pub   1024D/BBE55AB3 2007-03-31 [expires: 2010-03-30]
uid                  Debian-Volatile Archive Automatic Signing Key (4.0/etch)
sub   2048g/36CA98F3 2007-03-31 [expires: 2010-03-30]

pub   4096R/55BE302B 2009-01-27 [expires: 2012-12-31]
uid                  Debian Archive Automatic Signing Key (5.0/lenny)

OK 5个最后一行正是刚才加入的
再次apt-get update 就应该没有问题了

#apt-get update

Get:1 http://security.debian.org etch/updates Release.gpg [1032B]
Hit http://security.debian.org etch/updates Release
Ign http://security.debian.org etch/updates/main Packages/DiffIndex
Ign http://security.debian.org etch/updates/contrib Packages/DiffIndex
Ign http://security.debian.org etch/updates/main Sources/DiffIndex
Ign http://security.debian.org etch/updates/contrib Sources/DiffIndex
Hit http://security.debian.org etch/updates/main Packages
Hit http://security.debian.org etch/updates/contrib Packages
Hit http://security.debian.org etch/updates/main Sources
Hit http://security.debian.org etch/updates/contrib Sources
Fetched 1B in 2s (0B/s)
Reading package lists... Done

还有一种情况就是 key过期的问题那么这时候可以一个个先删除系统中的key
比如
apt-key list
apt-key del 55BE302B
apt-key del BBE55AB3
...
然后删除debian-archive-keyring包
dpkg –purge debian-archive-keyring
再次重新安装
apt-get install debian-archive-keyring
基本上都能解决这个问题

---------------END---------------

Tags: debian , public , key

Oprating System | Comments(1) | Trackbacks(0) | Reads(654)

imp导入数据到ORACLE遭遇ORA-12899错误

[

2009/05/26 23:32 | by askwan ]

错如信息：

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "JRGAZX"."DTLMB"."CC" (actual: 66, maximum: 50)

初步断定是字符集问题，中文在UTF-8里占3个字节，ZHS16GBK里占2个字节,而源dmp文件字符集是ZHS16GBK的库到出来的，现在要导入到目标字符集为UFT-8的库里。

Quotation

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK ;
ALTER DATABASE CHARACTER SET ZHS16GBK
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

报字符集不兼容，此时下INTERNAL_USE指令不对字符集超集进行检查：

Quotation

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP

再次用imp导入，就没有问题了。

oracle@server$ imp jrgazx/jrgazx file=/opt/jrgazx1/jrgazx1.dmp fromuser=jrgazx TABLES=CWFXZB,CXQK,DQBGYYPLSJB,DQTZSM,DSYP,DTLMB

Quotation

Import: Release 10.2.0.1.0 - Production on Wed May 27 01:22:54 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing JRGAZX's objects into JRGAZX
. importing JRGAZX's objects into JRGAZX
. . importing table                       "CWFXZB"      57197 rows imported
. . importing table                         "CXQK"       8471 rows imported
. . importing table                  "DQBGYYPLSJB"      30593 rows imported
. . importing table                       "DQTZSM"      11173 rows imported
. . importing table                         "DSYP"       4906 rows imported
. . importing table                        "DTLMB"     390372 rows imported
Import terminated successfully without warnings.

---------END-----------

Tags: oracle , ora-12899 , imp , utf-8 , zhs16gbk

DataBase | Comments(0) | Trackbacks(0) | Reads(1262)

Pages: 7/59

2 3 4 5 6 7 8 9 10 11

[ View by Articles | List ]

PostgreSQL的mulitmaster replication解决方案Cybercluster

"There is no public key available for the following key IDs" 问题

imp导入数据到ORACLE遭遇ORA-12899错误

Search

Categories

Latest Entries

Links

Archive

Statistics

Misc