Implement Postgres-XC 0.9.7 with 8 servers

  • victor1010

    2014-12-12 22:27:04
  • IT综合
  • 转载
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
请使用浏览器的分享功能分享到微信等