Postgres-xl 手动安装(在安装的过程中注意关闭防火墙)

Postgres-xl手动安装(在安装的过程中注意关闭防火墙)
下文中除了su -pgxl其它都是root在安装

• 创建用户
  为每台主机创建用户 pgxl
  # useradd pgxl
  # passwd pgxl
  # 输入密码
  修改机器名分别为 gtm,cd1,cd2
  gtm:
  vi /etc/sysconfig/network
  修改hostname=gtm
  vi /etc/hosts

改为gtm

各节点执行
cat /etc/hosts
192.168.127.130 gtm
192.168.127.131 cd1
192.168.127.132 cd2

各节点执行
service iptables stop

主节点
* pgxc_ctl免密码运行
ssh-keygen -t rsa (in ~/.ssh目录下)
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
若不是在本地安装，需要把该文件的内容添加到其他需要安装的机器的authorized_keys文件中。
gtm
scp authorized_keys pgxl@cd1:/home/pgxl/.ssh/
scp authorized_keys pgxl@cd2:/home/pgxl/.ssh/
cd1
scp authorized_keys pgxl@gtm:/home/pgxl/.ssh/
scp authorized_keys pgxl@cd2:/home/pgxl/.ssh/
cd2
scp authorized_keys pgxl@gtm:/home/pgxl/.ssh/
scp authorized_keys pgxl@cd1:/home/pgxl/.ssh/

最后的结果就是 gtm,cd1,cd2 节点的 authorized_keys 都一样是：
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAq8VqiukqchjbviyAPMVnpu8cA8EoH7Y4I4AFucVpjqD1/dbA3HovpwloD42IBzOw5Nto+gPqRgtI32i5RKF+ZlyK8XIH/sH2MLzb/nglYHzrM8GerHoR/w9cPyzkVJf3wRwA5aM7yqOS8qD+3TKBK1egpPbXuvxcNOxck2tQMqa0rJuyp99/0QF5IvWdyjhCh/VUx7YnH0jFkNj17wOiq3Lt4Qk/0iKW6Rey6SJiINMLk+IiVld3Crlee8vWOfpjp5ZgCuWl1/dXFcoVuzFodXsDj2WPtn2LS/55HlPwnzUqFZch2B8QrON2+z3AaRypp1Y2gmcZmbE9hzTKuIxk3w== root@cd1
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAu+YS1Ojw5ek0/jRVuB4t8tTrHr7ufPpTlZtQzbJUT7q3YWGVn+Y45FgVaB3jiQ2LheiCoTYMdzUNXqeTEUomqe3tUKiwUn8cJCvZDoW1M3DwB/cwi9SflDHbgE326l4FxcA1g9nbHljTiKPhXurV4nDeRi7PgZiw1v8BhCW3NPosbYvD3kpo79jzCtfozk1SOu9hKFI0W/VkwR1JaLTYWPffuCbw/hWLjvt27QHLt1YjT/0hsfVx6latLDrSpp8kqysslLmsD2IWB3cr3RBm8BLVea7WHelh2BkMON3iJdiBG2i7RKEbjXx7qIRmmPfRA9Nd0f3CQjdx05IYjmSYcQ== root@gtm
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAzr735z5GjRBQr8980wugx7zhIRFGY9+1Rm9Kd0ySaRcbpAjMF7+0hOMZZvHMdvq3LSv+R1zWAprKPsqaPBaM4V6kmAI8Qqn0f8O9m8OaLA+h/SrUOsGrpwNPISPiEIcv1HQjW6iJcwIrkXTS2OaRX03xddVsQq7P0hWy5ths/ANz5s2zcUTdmQbto8kMcYXbha7dXTgn9KV4veTe/WWgs7N7ug/sjYUQSSV/Ke2/htWMPZczzgJr3t4j2V25RweXTtDHyqnGwGfJeRcNMNQyu7YUNB+iDwkHFlDR3okHGHzOs085DRGxwcKCmOkujsG7rDjGd4UgKftWnht6bV/A8Q== root@cd2

有时需要
chmod 600 authorized_keys
chmod 700 ~/.ssh/

1、节点规划

192.168.127.130: gtm
192.168.127.131: cd1 dn1
192.168.127.132: cd2 dn2

2、root用户分别在130.131.132节点上安装

编译安装依赖包 (确保yum 资源库已配置好 ，/etc/yum.repos.d/)
yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl perl-devel perl-ExtUtils* OpenIPMI-tools openjade libxslt docbook-style-dsssl

下载压缩包：
cd /home/dw/soft
wget http://jaist.dl.sourceforge.net/project/postgres-xl/Releases/Version_9.2rc/postgres-xl-v9.2-src.tar.gz
tar -zxvf postgres-xl-v9.2-src.tar.gz
cd /home/dw/soft/postgres-xl

因为是测试, 所以加了debug功能, 生产环境建议去掉–enable-dtrace –enable-debug –enable-cassert这几个选项.
./configure –prefix=/home/pgxl/pgxl9.2 (pgxl9.2文件夹要提前创建好，作为软件的安装路径)

(–with-pgport=11921 –with-perl –with-tcl –with-python –with-openssl –with-pam –without-ldap –with-libxml –with-libxslt –enable-thread-safety –enable-debug –enable-cassert
)
with语句：
–with-pgport=NUMBER
把 NUMBER 设置为服务器和客户端的缺省端口．缺省是 5432． 这个端口可以在以后设置，不过如果你在这里声明，那么 服务器和客户端将有相同的编译好了的缺省值．这样会方便些．

–with-CXX
制作C++支持库．

–with-perl
制作 Perl 接口模块．Perl 接口将被安装到 Perl 模块通常的 安装位置（典型的位置是 /usr/lib/perl）， 所以你必须要有 root 权限来执行安装步骤（参阅 step 4）．使用这个选项你需要安装 Perl 5．

–with-Python
制作 Python 接口模块．你需要 root 权限将 Python 模块安装到它的 缺省位置 （/usr/lib/pythonx.y）． 要使用这个选项，你必须已经安装了 Python 并且你的系统必须支持 共享库．如果你想制作一个新的完整的二进制解释器，你只能自己 手工制作．

–with-tcl
制作那些需要 Tcl/Tk 的部件， 就是 libpgtcl，pgtclsh，pgtksh，pgaccess和PL/Tcl． 你也可以看看下面的 –without-tk．

–without-tk
如果你同时声明了 –with-tcl 和这个选项，那么那些 需要 Tk（也就是 pgtksh 和pgaccess）的程序将被排除在外．

postgres-xl 目录下 configure –prefix=/home/pgxl/pgxl9.2 指定安装路径
不指定prefix，则可执行文件默认放在/usr/local/bin，库文件默认放在/usr/local/lib，配置文件默认放在/usr/local/etc。其它的资源文件放在/usr/local/share。你要卸载这个程序，要么在原来的make目录下用一次make uninstall（前提是make文件指定过uninstall）,要么去上述目录里面把相关的文件一个个手工删掉。
指定prefix，直接删掉一个文件夹就够了。

问题：
‘/usr/bin/perl’ /bin/collateindex.pl -f -g -i ‘bookindex’ -o bookindex.sgml HTML.index
Can’t open perl script “/bin/collateindex.pl”: No such file or directory
make[4]: * [bookindex.sgml] Error 2
make[4]: Leaving directory /root/postgres-xl/doc-xc/src/sgml'
make[3]: *** [sql_help.h] Error 2
make[3]: Leaving directory/root/postgres-xl/src/bin/psql’
make[2]: * [all-psql-recurse] Error 2
make[2]: Leaving directory /root/postgres-xl/src/bin'
make[1]: *** [all-bin-recurse] Error 2
make[1]: Leaving directory/root/postgres-xl/src’
make: * [all-src-recurse] Error 2

解决方式：

# yum install docbook-style-dsssl
# find / -name collateindex.pl
/usr/bin/collateindex.pl

make
make install
;

3、配置

设置环境变量：

# su - pgxl
$vi .bash_profile export PGHOME=/home/pgxl/pgxl9.2 export PGUSER=postgres export LD_LIBRARY_PATH=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH

在192.168.127.130上执行：
初始化
在gtm节点上执行：
root
mkdir -p /data/pgxl/data_gtm
chown -R pgxl:pgxl /data/pgxl/data_gtm/
su - pgxl
initgtm -D /data/pgxl/data_gtm/ -Z gtm

gtm配置
gtm：
[postgres@gtm ~]$ vi /data/pgxl/data_gtm/gtm.conf
nodename = ‘gtm’
listen_addresses = ‘*’
port = 13921
startup = ACT
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = ‘gtm.log’
log_min_messages = WARNING

在192.168.127.131上执行：

root
mkdir -p /data/pgxl/data_coord
mkdir -p /data/pgxl/data_datanode

chown -R pgxl:pgxl /data/pgxl/data_coord
chown -R pgxl:pgxl /data/pgxl/data_datanode

su - pgxl
initdb -D /data/pgxl/data_coord/ –nodename cd1 -E UTF8 -U postgres -W
initdb -D /data/pgxl/data_datanode/ –nodename dn1 -E UTF8 -U postgres -W

data_coord1配置

[postgres@node1 ~]$ vi /data/pgxl/data_coord/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 11921 # (change requires restart)
max_connections = 100 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = ‘.’ # (change requires restart)
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 = 16 # zero disables the feature
maintenance_work_mem = 512MB # min 1MB
vacuum_cost_delay = 10ms # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
bgwriter_delay = 10ms # 10-10000ms between rounds
synchronous_commit = on # synchronization level;
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
network_byte_cost = 0.001 # same scale as above
remote_query_cost = 100.0 # same scale as above
effective_cache_size = 1024MB
log_destination = ‘csvlog’ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_statement = ‘ddl’ # none, ddl, mod, all
log_timezone = ‘PRC’
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = ‘iso, mdy’
timezone = ‘PRC’
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’
pooler_port = 15921 # Pool Manager TCP port
max_pool_size = 1600 # Maximum pool size
pool_conn_keepalive = 60 # Close connections if they are idle
pool_maintenance_timeout = 30 # Launch maintenance routine if pooler
max_coordinators = 16 # Maximum number of Coordinators
max_datanodes = 16 # Maximum number of Datanodes
gtm_host = ‘192.168.127.130’ –‘localhost’ # Host name or address of GTM 如果不要代理 换成GTM的IP
gtm_port = 13921 # Port of GTM（如果不要Proxy,改为GTM的端口号）
sequence_range = 100
pgxc_node_name = ‘cd1’ # 需要配置

[postgres@node1 ~]$ vi /data/pgxl/data_coord/pg_hba.conf
[添加]
host all all 127.0.0.1/32 trust #表示本机
host all all 192.168.127.0/24 trust #表示10.10.100.1~10.10.100.255 全段IP 可以无需密码访问
host all all 0.0.0.0/0 md5 #表示所有IP 都可以以密码登陆访问

data_node1配置

[postgres@node1 ~]$ vi /data/pgxl/data_datanode/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 12921 # (change requires restart)
max_connections = 400 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = ‘.’ # (change requires restart)
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10 # TCP_KEEPCNT;
shared_buffers = 2048MB # min 128kB
maintenance_work_mem = 512MB # min 1MB
vacuum_cost_delay = 10ms # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
bgwriter_delay = 10ms # 10-10000ms between rounds
shared_queues = 64 # min 16
shared_queue_size = 2048kB # min 16KB
synchronous_commit = off # synchronization level;
wal_buffers = 16384kB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
network_byte_cost = 0.001 # same scale as above
remote_query_cost = 100.0 # same scale as above
effective_cache_size = 1024MB
log_destination = ‘csvlog’ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_statement = ‘ddl’ # none, ddl, mod, all
log_timezone = ‘PRC’
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = ‘iso, mdy’
timezone = ‘PRC’
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’
pooler_port = 16921 # Pool Manager TCP port
max_pool_size = 100 # Maximum pool size
pool_conn_keepalive = 60 # Close connections if they are idle
pool_maintenance_timeout = 30 # Launch maintenance routine if pooler
max_coordinators = 16 # Maximum number of Coordinators
max_datanodes = 16 # Maximum number of Datanodes
gtm_host = ‘192.168.127.130’ #’localhost’ # Host name or address of GTM 如果不要代理 换成GTM的IP
gtm_port = 13921 #14921 # Port of GTM（如果不要Proxy,改为GTM的端口号）
pgxc_node_name = ‘dn1’ # 需要配置

[postgres@node1 ~]$ vi /data/pgxl/data_datanode/pg_hba.conf
[添加]
host all all 127.0.0.1/32 trust
host all all 192.168.127.0/24 trust
host all all 0.0.0.0/0 md5

在192.168.127.132上执行：

root
mkdir -p /data/pgxl/data_coord;
mkdir -p /data/pgxl/data_datanode;

chown -R pgxl:pgxl /data/pgxl/data_coord;
chown -R pgxl:pgxl /data/pgxl/data_datanode;

su - pgxl
initdb -D /data/pgxl/data_coord/ –nodename cd2 -E UTF8 -U postgres -W
initdb -D /data/pgxl/data_datanode/ –nodename dn2 -E UTF8 -U postgres -W

data_coord2配置

[postgres@node2 ~]$ vi /data/pgxl/data_coord/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 11921 # (change requires restart)
max_connections = 100 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = ‘.’ # (change requires restart)
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 = 16 # zero disables the feature
maintenance_work_mem = 512MB # min 1MB
vacuum_cost_delay = 10ms # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
bgwriter_delay = 10ms # 10-10000ms between rounds
synchronous_commit = on # synchronization level;
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
network_byte_cost = 0.001 # same scale as above
remote_query_cost = 100.0 # same scale as above
effective_cache_size = 1024MB
log_destination = ‘csvlog’ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_statement = ‘ddl’ # none, ddl, mod, all
log_timezone = ‘PRC’
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = ‘iso, mdy’
timezone = ‘PRC’
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’
pooler_port = 15921 # Pool Manager TCP port
max_pool_size = 1600 # Maximum pool size
pool_conn_keepalive = 60 # Close connections if they are idle
pool_maintenance_timeout = 30 # Launch maintenance routine if pooler
max_coordinators = 16 # Maximum number of Coordinators
max_datanodes = 16 # Maximum number of Datanodes
gtm_host =’192.168.127.130’ #’localhost’ # Host name or address of GTM 不要代理 换成GTM的IP
gtm_port = 13921 #14921 # Port of GTM（如果不要Proxy,改为GTM的端口号）
sequence_range = 100
pgxc_node_name = ‘cd2’ #需要配置

[postgres@node2 ~]$ vi /data/pgxl/data_coord/pg_hba.conf
[添加]
host all all 127.0.0.1/32 trust
host all all 192.168.127.0/24 trust
host all all 0.0.0.0/0 md5

data_node2配置

[postgres@node1 ~]$ vi /data/pgxl/data_datanode/postgresql.conf

listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 12921 # (change requires restart)
max_connections = 400 # (change requires restart)
superuser_reserved_connections = 13 # (change requires restart)
unix_socket_directory = ‘.’ # (change requires restart)
tcp_keepalives_idle = 60 # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10 # TCP_KEEPCNT;
shared_buffers = 2048MB # min 128kB
maintenance_work_mem = 512MB # min 1MB
vacuum_cost_delay = 10ms # 0-100 milliseconds
vacuum_cost_limit = 10000 # 1-10000 credits
bgwriter_delay = 10ms # 10-10000ms between rounds
shared_queues = 64 # min 16
shared_queue_size = 2048kB # min 16KB
synchronous_commit = off # synchronization level;
wal_buffers = 16384kB # min 32kB, -1 sets based on shared_buffers
wal_writer_delay = 10ms # 1-10000 milliseconds
checkpoint_segments = 128 # in logfile segments, min 1, 16MB each
network_byte_cost = 0.001 # same scale as above
remote_query_cost = 100.0 # same scale as above
effective_cache_size = 1024MB
log_destination = ‘csvlog’ # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_statement = ‘ddl’ # none, ddl, mod, all
log_timezone = ‘PRC’
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = ‘iso, mdy’
timezone = ‘PRC’
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’
pooler_port = 16921 # Pool Manager TCP port
max_pool_size = 100 # Maximum pool size
pool_conn_keepalive = 60 # Close connections if they are idle
pool_maintenance_timeout = 30 # Launch maintenance routine if pooler
max_coordinators = 16 # Maximum number of Coordinators
max_datanodes = 16 # Maximum number of Datanodes
gtm_host =’192.168.127.130’ #’localhost’ # Host name or address of GTM 不要代理 直接用GTM的IP
gtm_port =13921 #14921 # Port of GTM
pgxc_node_name = ‘dn2’ # 需要配置（如果不要Proxy,改为GTM的端口号）

[postgres@node1 ~]$ vi /data/pgxl/data_datanode/pg_hba.conf
[添加]
host all all 127.0.0.1/32 trust
host all all 192.168.127.0/24 trust
host all all 0.0.0.0/0 md5

4、启动

启动集群的顺序:

gtm
gtm_ctl start -Z gtm -D /data/pgxl/data_gtm/

datanode
pg_ctl start -Z datanode -D /data/pgxl/data_datanode/
pg_ctl start -Z datanode -D /data/pgxl/data_datanode/

coordinator
pg_ctl start -Z coordinator -D /data/pgxl/data_coord/
pg_ctl start -Z coordinator -D /data/pgxl/data_coord/

启动gtm
[postgres@gtm ~]$ gtm_ctl start -Z gtm -D /data/pgxl/data_gtm/
server starting

[postgres@gtm ~]$ tail /data/pgxl/data_gtm/gtm.log
1:140169430058752:2014-06-12 07:51:17.652 CST -LOG: Starting GTM server at (*:6666) – control file /opt/pgxl/data_gtm/gtm.control
LOCATION: main, main.c:601
1:140169430058752:2014-06-12 07:51:17.652 CST -LOG: Restoring last GXID to 10000

LOCATION: GTM_RestoreTxnInfo, gtm_txn.c:2673
1:140169430058752:2014-06-12 07:51:17.652 CST -LOG: Started to run as GTM-Active.
LOCATION: main, main.c:682

启动datanode
node1：
[postgres@node1 ~]$ pg_ctl start -Z datanode -D /data/pgxl/data_datanode/

node2：
[postgres@node2 ~]$ pg_ctl start -Z datanode -D /data/pgxl/data_datanode/

启动coordinator
node1：
[postgres@node1 ~]$ pg_ctl start -Z coordinator -D /data/pgxl/data_coord/

node2：
[postgres@node2 ~]$ pg_ctl start -Z coordinator -D /data/pgxl/data_coord/

5、注册节点

查看当前节点信息：（注意一定要在coord，data 节点上全部注册） 注意各节点的coordinator,datanode 的默认值是5432,localhost需要更改成实际设置的
psql -p 11921 -U postgres -d postgres -h 192.168.127.131

select * from pgxc_node;

在coord1 执行
psql -p 11921 -U postgres -d postgres -h 192.168.127.131

CREATE NODE cd2 WITH (TYPE=’coordinator’,HOST=’192.168.127.132’,PORT=11921);
CREATE NODE dn1 WITH (TYPE=’datanode’,HOST=’192.168.127.131’,PORT=12921);
CREATE NODE dn2 WITH (TYPE=’datanode’,HOST=’192.168.127.132’,PORT=12921);
ALTER NODE cd1 WITH (TYPE=’coordinator’,HOST=’192.168.127.131’,PORT=11921);
select pgxc_pool_reload();

在coord2 执行
psql -p 11921 -U postgres -d postgres -h 192.168.127.132
CREATE NODE cd1 WITH (TYPE=’coordinator’,HOST=’192.168.127.131’,PORT=11921);
CREATE NODE dn1 WITH (TYPE=’datanode’,HOST=’192.168.127.131’,PORT=12921);
CREATE NODE dn2 WITH (TYPE=’datanode’,HOST=’192.168.127.132’,PORT=12921);
ALTER NODE cd2 WITH (TYPE=’coordinator’,HOST=’192.168.127.132’,PORT=11921);
select pgxc_pool_reload();

在data1 执行
psql -p 12921 -U postgres -d postgres -h 192.168.127.131
CREATE NODE cd2 WITH (TYPE=’coordinator’,HOST=’192.168.127.132’,PORT=11921);
CREATE NODE cd1 WITH (TYPE=’coordinator’,HOST=’192.168.127.131’,PORT=11921);
ALTER NODE dn1 WITH (TYPE=’datanode’,HOST=’192.168.127.131’,PORT=12921);
CREATE NODE dn2 WITH (TYPE=’datanode’,HOST=’192.168.127.132’,PORT=12921);
select pgxc_pool_reload();

在data2 执行
psql -p 12921 -U postgres -d postgres -h 192.168.127.132
CREATE NODE cd1 WITH (TYPE=’coordinator’,HOST=’192.168.127.131’,PORT=11921);
CREATE NODE dn1 WITH (TYPE=’datanode’,HOST=’192.168.127.131’,PORT=12921);
ALTER NODE dn2 WITH (TYPE=’datanode’,HOST=’192.168.127.132’,PORT=12921);
CREATE NODE cd2 WITH (TYPE=’coordinator’,HOST=’192.168.127.132’,PORT=11921);
select pgxc_pool_reload();

6、创建数据组 #建group要在两个coordinator上都执行一次

192.168.127.131上: (出现机器之间连不上一般都是防火墙没关闭)

psql -p 11921 -U postgres -d postgres -h 192.168.127.131

create node group gp1 with (dn1,dn2);

192.168.127.132上:

psql -p 11921 -U postgres postgres -h 192.168.127.132

create node group gp1 with (dn1,dn2);

1. 创建数据库只需要在一个coordinator上创建

create database test_xl;

\c test_xl

create table userinfo(id serial8 primary key, info text, crt_time timestamp) distribute by hash(id) to node(dn1,dn2);#这一行不是必须的

insert into userinfo values(1,’a’,now());

insert into userinfo values(2,’b’,now());

insert into userinfo values(3,’c’,now());

查看全部数据

select * from userinfo;

查看datanode1上的数据

execute direct on (dn1)

$$select * from userinfo$$ ;

查看datanode2上的数据

execute direct on (dn2)

$$select * from userinfo$$ ;

8、关闭集群的顺序

coordinator
pg_ctl stop -Z coordinator -D /data/pgxl/data_coord/
pg_ctl stop -Z coordinator -D /data/pgxl/data_coord/
datanode
pg_ctl stop -Z datanode -D /data/pgxl/data_datanode/
pg_ctl stop -Z datanode -D /data/pgxl/data_datanode/

gtm
gtm_ctl stop -Z gtm -D /home/pgxl/data_gtm/

9、问题汇总

Q1：

test=# create database pgxc;
LOG: Will fall back to local snapshot for XID = 19764, source = 0,
gxmin = 0, autovac launch = 0, autovac = 0, normProcMode = 0, postEnv = 1
ERROR: node “coord1_19791” does not exist
STATEMENT: SET global_session TO coord1_19791;
ERROR: node “coord1_19791” does not exist
STATEMENT: SET global_session TO coord1_19791;
CREATE DATABASE

解决：没有在全部节点上注册

Q3：
can not connect to GTM no route to host
防火墙未关
解决：service iptables stop