ITPub博客

首页 > 应用开发 > IT综合 > Implement Postgres-XC 0.9.7 with 8 servers

Implement Postgres-XC 0.9.7 with 8 servers

IT综合 作者:victor1010 时间:2014-12-12 22:27:04 0 删除 编辑
http://blog.163.com/digoal@126/blog/static/16387704020121952051174/

Postgres-XC 于1月份发布了0.9.7版本, 基于PostgreSQL9.1.2版本修改而来. 手册目前还很不完整. 
Postgres-XC 主要的组件有gtm, gtm_standby, gtm_proxy, coordinator, datanode.
gtm 为分配GXID和管理PGXC MVCC的模块, 在一个CLUSTER中只能有一台主的gtm, gtm也将成为整个CLUSTER的瓶颈已经单点故障点.
gtm_standby 为gtm的备机.
gtm_proxy 为降低gtm压力而诞生的, 用于对coordinator节点提交的任务进行分组等操作. 机器中可以存在多个gtm_proxy.
coordinator 为管理全局的catalog信息和与应用程序交互的节点, 也是一个PostgreSQL数据库. 集群中可以存在多个coordinator.
datanode 也是一个PostgreSQL数据库, 用于存放大量的应用数据.

以下是一个简单的测试. 
# 测试环境
# 主机, 模块
	


host_datanode_1 datanode_1
host_datanode_2 datanode_2
host_datanode_3 datanode_3
host_coordinator_3 gtm-proxy, coordinator_3
host_coordinator_2 gtm-proxy, coordinator_2
host_coordinator_1 gtm-proxy, coordinator_1
host_gtm-standby gtm-standby
host_gtm gtm



# 端口
	


gtm, gtm-standby 4191
gtm-proxy 4192
coordinator, datanode 4193
coordinator_pooler_manager_port 4194



# 数据目录
	


gtm /pgxc_gtm/work
gtm-standby /pgxc_gtm/work
gtm-proxy /pgxc_gtm_proxy/work
coordinator /pgxcdata/data01/pg_root
datanode /pgxcdata/data01/pg_root



# 配置主机名, 不配置的话请使用DSN, 否则互联互通会有问题. 
	


/etc/hosts
10.10.1.212 db-host_gtm.sky-mobi.com db-host_gtm
10.10.1.89 db-host_gtm-standby.sky-mobi.com db-host_gtm-standby
10.10.1.63 db-host_coordinator_1.sky-mobi.com db-host_coordinator_1
10.10.1.55 db-host_coordinator_2.sky-mobi.com db-host_coordinator_2
10.10.1.236 db-host_coordinator_3.sky-mobi.com db-host_coordinator_3
10.10.1.235 db-host_datanode_3.sky-mobi.com db-host_datanode_3
10.10.1.227 db-host_datanode_2.sky-mobi.com db-host_datanode_2
10.10.1.207 db-host_datanode_1.sky-mobi.com db-host_datanode_1



# 安装, 与安装PostgreSQL的前提一致, 如也需要flex, bison等

	


useradd pgxc
tar -zxvf pgxc_v0.9.7.tar.gz
chown -R pgxc:pgxc pgxc
su - pgxc
vi .bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=4193
export PGDATA=/pgxcdata/data01/pg_root
export LANG=en_US.utf8
export PGHOME=/opt/pgxc0.9.7
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
cd pgxc
./configure --prefix=/opt/pgxc0.9.7 --with-pgport=4193 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety && gmake
su - root
cd pgxc
gmake install
mkdir $PGHOME/share/man
cp -r /opt/soft_bak/pgxc/doc-xc/src/sgml/man1 $PGHOME/share/man
cp -r /opt/soft_bak/pgxc/doc-xc/src/sgml/man3 $PGHOME/share/man
cp -r /opt/soft_bak/pgxc/doc-xc/src/sgml/man7 $PGHOME/share/man
su - pgxc
psql -V
psql (PostgreSQL) 9.1.2
contains support for command-line editing



# 配置gtm, 建议gtm为独占主机
# 配置文件必须放在工作目录, 所有GTM ID必须唯一, 从1开始, 所以gtm=1,gtm_standby=2.
	


mkdir -p /pgxc_gtm/work
cp /opt/pgxc0.9.7/share/postgresql/gtm.conf.sample /pgxc_gtm/work/gtm.conf
chown -R pgxc:pgxc /pgxc_gtm
vi /pgxc_gtm/work/gtm.conf
nodename = '1'
listen_addresses = '0.0.0.0'
port = 4191
startup = ACT
log_file = 'gtm.log'
log_min_messages = NOTICE


启动gtm
	

gtm_ctl start -S gtm -D /pgxc_gtm/work -l /pgxc_gtm/work/gtm_ctl.log

# 查看启动状态
	


gtm_ctl status -S gtm -D /pgxc_gtm/work
pid: 2219
data: /pgxc_gtm/work
active: 1



配置gtm standby
	


mkdir -p /pgxc_gtm/work
cp /opt/pgxc0.9.7/share/postgresql/gtm.conf.sample /pgxc_gtm/work/gtm.conf
chown -R pgxc:pgxc /pgxc_gtm
vi /pgxc_gtm/work/gtm.conf
nodename = '2'
listen_addresses = '0.0.0.0'
port = 4191
startup = STANDBY
active_host = 'db-host_gtm'
active_port = 4191
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = NOTICE


启动gtm standby
	

gtm_ctl start -S gtm -D /pgxc_gtm/work -l /pgxc_gtm/work/gtm_ctl.log

# 查看状态
	


gtm_ctl status -S gtm -D /pgxc_gtm/work
pid: 30694
data: /pgxc_gtm/work
active: 0



# 配置gtm proxy, 所有GTM-Proxy ID必须唯一, 从1开始,
# 建议每个coordinator配置一个gtm_proxy, 并将gtm_proxy和coordinator放在一台主机上
	


mkdir -p /pgxc_gtm_proxy/work
cp /opt/pgxc0.9.7/share/postgresql/gtm_proxy.conf.sample /pgxc_gtm_proxy/work/gtm_proxy.conf
chown -R pgxc:pgxc /pgxc_gtm_proxy
vi /pgxc_gtm_proxy/work/gtm_proxy.conf
nodename = '1'
listen_addresses = '0.0.0.0'
port = 4192
worker_threads = 2
gtm_host = 'db-host_gtm'
gtm_port = 4191
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm_proxy.log'
log_min_messages = NOTICE


启动gtm proxy
	

gtm_ctl start -S gtm_proxy -D /pgxc_gtm_proxy/work -l /pgxc_gtm_proxy/work/gtm_ctl.log

# 查看状态
	

ps -ewf|grep gtm_proxy


# 建议datanode和coordinate的数量相同,但是datanode和coordinate不要跑在同一台主机上,因为创建表空间时datanode和coordinate使用相同的目录.
# 配置datanode, 初始化3台datanode
	


initdb --nodename datanode_1 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5
initdb --nodename datanode_2 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5
initdb --nodename datanode_3 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5


# 其中datanode_1节点的postgresql.conf配置
	


listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
port = 4193                             # (change requires restart)
max_connections = 2000                  # (change requires restart) # 大于或等于所有coordinator配置的max_pool_size的总和
superuser_reserved_connections = 13     # (change requires restart)
password_encryption = on
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10               # TCP_KEEPCNT;
shared_buffers = 1024MB                 # min 128kB
max_prepared_transactions = 2000                # zero disables the feature
maintenance_work_mem = 512MB            # min 1MB
max_stack_depth = 8MB                   # min 100kB
vacuum_cost_delay = 10ms                # 0-100 milliseconds
vacuum_cost_limit = 500         # 1-10000 credits
synchronous_commit = off                # synchronization level; on, off, or local
wal_sync_method = fdatasync             # the default is the first option
wal_writer_delay = 10ms         # 1-10000 milliseconds
checkpoint_segments = 128               # in logfile segments, min 1, 16MB each
checkpoint_timeout = 15min              # range 30s-1h
random_page_cost = 2.0                  # same scale as above
effective_cache_size = 20480MB
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_directory = '/var/applog/pg_log'            # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600                    # creation mode for log files,
log_truncate_on_rotation = on           # If on, an existing log file with the
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 10MB                # Automatic rotation of logfiles will
log_checkpoints = on
autovacuum = on                 # Enable autovacuum subprocess?  'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
default_text_search_config = 'pg_catalog.english'
gtm_host = 'db-host_gtm'                    # Host name or address of GTM
gtm_port = 4191                 # Port of GTM
pgxc_node_name = 'datanode_1'                    # Coordinator or Datanode name
strict_statement_checking = on          # Forbid PG-XC-unsafe SQL


# 所有节点的pg_hba.conf配置,允许coordinator和datanode节点trust访问
	


host all all 10.10.1.63/32 trust
host all all 10.10.1.55/32 trust
host all all 10.10.1.236/32 trust
host all all 10.10.1.235/32 trust
host all all 10.10.1.227/32 trust
host all all 10.10.1.207/32 trust
host all all 0.0.0.0/0 md5


# 启动
	

pg_ctl start -D /pgxcdata/data01/pg_root -Z datanode



配置coordinate
	


initdb --nodename coordinator_1 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5
initdb --nodename coordinator_2 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5
initdb --nodename coordinator_3 -D /pgxcdata/data01/pg_root -E UTF8 --locale=C -W -A md5


# 其中coordinator_1节点的postgresql.conf配置
	


listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
port = 4193                             # (change requires restart)
max_connections = 1000                  # (change requires restart)  # 指应用最多可以与coordinator建立多少个连接
superuser_reserved_connections = 13     # (change requires restart)
password_encryption = on
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10               # TCP_KEEPCNT;
shared_buffers = 1024MB                 # min 128kB
max_prepared_transactions = 1000                # zero disables the feature
maintenance_work_mem = 512MB            # min 1MB
max_stack_depth = 8MB                   # min 100kB
vacuum_cost_delay = 10ms                # 0-100 milliseconds
vacuum_cost_limit = 500         # 1-10000 credits
synchronous_commit = off                # synchronization level; on, off, or local
wal_sync_method = fdatasync             # the default is the first option
wal_writer_delay = 10ms         # 1-10000 milliseconds
checkpoint_segments = 128               # in logfile segments, min 1, 16MB each
checkpoint_timeout = 15min              # range 30s-1h
random_page_cost = 2.0                  # same scale as above
effective_cache_size = 20480MB
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_directory = '/var/applog/pg_log'            # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0600                    # creation mode for log files,
log_truncate_on_rotation = on           # If on, an existing log file with the
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 10MB                # Automatic rotation of logfiles will
log_checkpoints = on
autovacuum = on                 # Enable autovacuum subprocess?  'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
default_text_search_config = 'pg_catalog.english'
gtm_host = 'db-host_gtm'                    # Host name or address of GTM
gtm_port = 4191                 # Port of GTM
pooler_port = 4194                     # Pool Manager TCP port
min_pool_size = 1                      # Initial pool size  # 指coordinator和datanode建立的连接
max_pool_size = 100                    # Maximum pool size  # 指coordinator和datanode建立的连接
persistent_datanode_connections = on   # Set persistent connection mode for pooler
pgxc_node_name = 'coordinator_1'                    # Coordinator or Datanode name
strict_statement_checking = on          # Forbid PG-XC-unsafe SQL


# 所有节点的pg_hba.conf配置,允许coordinator和datanode节点trust访问
	


host all all 10.10.1.63/32 trust
host all all 10.10.1.55/32 trust
host all all 10.10.1.236/32 trust
host all all 10.10.1.235/32 trust
host all all 10.10.1.227/32 trust
host all all 10.10.1.207/32 trust
host all all 0.0.0.0/0 md5


# 启动
	

pg_ctl start -D /pgxcdata/data01/pg_root -Z coordinator


# 配置node信息, 在所有coordinator执行
	


psql -d template1 -U pgxc
drop node coordinator_1;
drop node coordinator_2;
drop node coordinator_3;
drop node datanode_1;
drop node datanode_2;
drop node datanode_3;
create node coordinator_1 with (type='coordinator', host='db-host_coordinator_1', port=4193);
create node coordinator_2 with (type='coordinator', host='db-host_coordinator_2', port=4193);
create node coordinator_3 with (type='coordinator', host='db-host_coordinator_3', port=4193);
create node datanode_1 with (type='datanode', host='db-host_datanode_1', port=4193, PRIMARY, PREFERRED);
create node datanode_2 with (type='datanode', host='db-host_datanode_2', port=4193, PREFERRED);
create node datanode_3 with (type='datanode', host='db-host_datanode_3', port=4193, PREFERRED);
alter node coordinator_1 with (host='db-host_coordinator_1', port=4193);
alter node coordinator_2 with (host='db-host_coordinator_2', port=4193);
alter node coordinator_3 with (host='db-host_coordinator_3', port=4193);
alter node datanode_1 with (type='datanode', host='db-host_datanode_1', port=4193, PRIMARY, PREFERRED);
alter node datanode_2 with (type='datanode', host='db-host_datanode_2', port=4193, PREFERRED);
alter node datanode_3 with (type='datanode', host='db-host_datanode_3', port=4193, PREFERRED);



# 建库测试, 断开所有coordinator的template1库的连接, 连接到任意一台coordinator, 创建digoal库
	


create database digoal;
\c digoal digoal
digoal=# create table user_info_hash(id int primary key,firstname text,lastname text,info text) distribute by hash(id) to node datanode_1,datanode_2,datanode_3;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "user_info_hash_pkey" for table "user_info_hash"
CREATE TABLE
digoal=# create table user_info_modulo(id int primary key,firstname text,lastname text,info text) distribute by modulo(id) to node datanode_1,datanode_2,datanode_3;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "user_info_modulo_pkey" for table "user_info_modulo"
CREATE TABLE
digoal=# create table user_info_rr(id int,firstname text,lastname text,info text) distribute by round robin to node datanode_1,datanode_2,datanode_3;
CREATE TABLE
digoal=# create table user_info_replica(id int primary key,firstname text,lastname text,info text) distribute by replication to node datanode_1,datanode_2,datanode_3;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "user_info_replica_pkey" for table "user_info_replica"
CREATE TABLE



# 插入数据测试 : 
	


digoal=# insert into user_info_hash select generate_series(1,10000),'zhou','digoal','DBA';
INSERT 0 10000
Time: 5260.994 ms
digoal=# insert into user_info_modulo select generate_series(1,10000),'zhou','digoal','DBA';
INSERT 0 10000
Time: 5305.979 ms
digoal=# insert into user_info_rr select generate_series(1,10000),'zhou','digoal','DBA';
INSERT 0 10000
Time: 5124.162 ms
digoal=# insert into user_info_replica select generate_series(1,10000),'zhou','digoal','DBA';



# 其中user_info_hash, user_info_modulo, user_info_rr表的数据分布在3个datanode中, 3个datanode合起来是1W条。
# 而user_info_replica的数据在3个datanode中每个节点一样, 都有1W条。

# Postgres-XC 相比PostgreSQL新增的catalog
	


 pg_catalog | pgxc_class                      | table | pgxc
 pg_catalog | pgxc_group                      | table | pgxc
 pg_catalog | pgxc_node                       | table | pgxc
 pg_catalog | pgxc_prepared_xacts             | view  | pgxc



例如可以通过pgxc_class在coordinator节点查询到pgxc创建的对象信息.
	


digoal=# select * from pgxc_class;
 pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets |     nodeoids      
---------+---------------+----------+-----------------+---------------+-------------------
   16422 | H             |        1 |               1 |          4096 | 16405 16406 16407


通过pg_node查询节点信息 : 
	


digoal=# select * from pgxc_node;
   node_name   | node_type | node_port |     node_host      | nodeis_primary | nodeis_preferred 
---------------+-----------+-----------+--------------------+----------------+------------------
 coordinator_1 | C         |      4193 | host_coordinator_1 | f              | f
 datanode_1    | D         |      4193 | host_datanode_1    | t              | t
 datanode_2    | D         |      4193 | host_datanode_2    | f              | t
 datanode_3    | D         |      4193 | host_datanode_3    | f              | t
 coordinator_2 | C         |      4193 | host_coordinator_2 | f              | f
 coordinator_3 | C         |      4193 | host_coordinator_3 | f              | f



# Postgres-XC 相比PostgreSQL新增的语法
alter node 修改node信息
create barrier 用于创建PGXC全局一致性备份的关键命令.
create node 创建node
create node group 创建node分组
drop node 删除node
drop node group 删除node分组

【小结】
1. 目前Postgres-XC还不太成熟, 操作性不好, 维护麻烦.
2. create barrier . 这个对于一个分布式数据库来说可以说是非常重要的, 全局一致的备份和还原对于企业级应用来说是特别需要考虑的. 相信PGXC未来会得到企业的认可.

【参考】
http://postgres-xc.sourceforge.net/
http://postgres-xc.sourceforge.net/docs/0_9_7/
手册 : Overview of Postgres-XC Internals

【其他】
# 安装时的报错
1. gmake world 报错
/bin/collateindex.pl 不存在
下载collateindex.pl到指定目录后
gmake install-world 报错
'/usr/bin/perl' /bin/collateindex.pl -f -g -i 'bookindex' -o bookindex.sgml HTML.index
collateindex.pl: file "HTML.index" does not exist

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8102208/viewspace-1366198/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-29

  • 博文量
    296
  • 访问量
    566853