Postgre-XC搭建

下载地址: http://sourceforge.net/projects/postgres-xc

1.安装依赖包

yum -y install zliblg-dev libreadine6-dev bison flex libperl-devel  gcc perl-ExtUtils-Embed readline-devel zlib-devel  python python-devel

2.安装  

./configure --prefix=/usr/local/pgxc --with-perl --with-python make  make install

3.环境变量：

export PATH=/usr/local/pgxc/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgxc/lib:LD_LIBRARY_PATH

4. 配置 postgres-XC集群

主机名	ip	角色	端口	nodename	数据目录
gtm	56.56.56.95	Gtm	6666	one	/data/gtm
gtm_standby	56.56.56.96	Gtm备库	6666	two	/data/gtm_standby
cd1	56.56.56.97	Coordinator	5432	co1	/data/coordinator
		Datanode	5433	dn1	/data/pgdata
		Gtm Proxy	6666	gtmpoxy01	/data/gtm_proxy
cd2	56.56.56.98	Coordinator	5432	co2	/data/coordinator
		Datanode	5433	dn2	/data/pgdata
		Gtm Proxy	6666	gtmpoxy02	/data/gtm_proxy
cd3	56.56.56.99	Coordinator	5432	co3	/data/coordinator
		Datanode	5433	dn3	/data/pgdata
		Gtm Proxy	6666	gtmpoxy03	/data/gtm_proxy

5. 5安装 Postgres-XC，五台机器都要安装

6. 5台机器上都配置hosts

56.56.56.95  gtm 56.56.56.96  gtm_standby 56.56.56.97  cd1 56.56.56.98  cd2 56.56.56.99  cd3

7.95 和 96 GTM 机器 创建对应的数据目录

mkdir  -p  /data/gtm mkdir -p /data/gtm_standby

97 、98 、99 机器创建对应的数据目录

mkdir -p /data/coordinator mkdir -p /data/pgdata mkdir -p /data/gtm_proxy

8. 创建用户和配置环境变量(5台机器都要执行)

groupadd pgxc useradd pgxc -g pgxc passwd pgxc [root@gtm gtm]# chown -R pgxc:pgxc /data/gtm [root@gtm gtm]# chown -R pgxc:pgxc /usr/local/pgxc

9.环境变量：

su - pgxc export PATH=/usr/local/pgxc/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgxc/lib:LD_LIBRARY_PATH

10 . 初始化gtm

10.1 在56.56.56.95机器上初始化

su - gtm initgtm -Z gtm -D /data/gtm

10.2 编辑/data/gtm/gtm.conf文件

nodename = 'one'     #节点名称，可以指定为任意名称，不能与其他节点名重复                           isten_addresses = '*'# GTM监听的ip地址，"*" 表示在所有的ip地址上监听               port = 6666          # GTM监控的端口                     startup = ACT        #主库设置为 ACT，如果是 Standby,则设置为 STANDBY

11.初始化GTM的备库

96机器上初始化

su - pgxc initgtm -Z gtm -D  /data/gtm_standby

11.1 编辑 /data/gtm_standby/gtm.conf

nodename = 'two'           # 节点名称       listen_addresses = '*'    # active_host:指定连接GTM的IP地址             port = 6666               # active_port:指定连接GTM备库的端口                        startup = STANDBY         # startup: 因为是GTM的备库，所以要设置为STANDBY  active_port = 6666        # GTM监控的端口

12. 初始化 gtm proxy

在56.56.56.97  98 99 三台机器上分别执行以下命令

chown -R pgxc:pgxc /data/gtm_proxy su - pgxc initgtm -Z gtm_proxy -D /data/gtm_proxy

13.在97机器配置文件:/data/gtm_proxy/gtm_proxy.conf

nodename = 'gtmproxy01'   port = 6666     gtm_host = 'gtm'  gtm_port = 6666

在98机器配置文件：/data/gtm_proxy/gtm_proxy.conf

nodename = 'gtmproxy02'   port = 6666     gtm_host = 'gtm'  gtm_port = 6666

在99机器配置文件 /data/gtm_proxy/gtm_proxy.conf

nodename = 'gtmproxy03'   port = 6666     gtm_host = 'gtm'  gtm_port = 6666

19.初始化Coordinator 和数据节点

在 97 、 98、 99 三台机器上分别执行以下命令

initdb --nodename co1 -D  /data/coordinator initdb --nodename dn1 -D /data/pgdata initdb --nodename co2 -D  /data/coordinator initdb --nodename dn2 -D /data/pgdata initdb --nodename co3 -D  /data/coordinator initdb --nodename dn3 -D /data/pgdata

20.修改3台机器的 coordinator配置文件/data/coordinator/postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on; port = 5432                     # (change requires restart) log_connections = on gtm_host = 'gtm'                # Host name or address of GTM gtm_port = 6666                 # Port of GTM pgxc_node_name = 'co1~coN'      # 根据实际的节点配置为co1、co2、... coN

21.修改3台机器的datanode的配置文件/data/pgdata/postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on; port = 5433                     # (change requires restart) log_connections = on gtm_host = 'gtm'                # Host name or address of GTM gtm_port = 6666                 # Port of GTM pgxc_node_name = 'dn1~dnN'      # 根据实际的节点配置为dn1、dn2、... dnN

注意：datanode 和 Coordinator  的三个节点都要修改

pg_hba.conf 

host    all            all            0.0.0.0/0            trust

22.启动集群

启动集群的顺序：

GTM GTM standy gtm-proxy datanodes coordinators

22.1.启动95机器gtm

gtm_ctl -Z gtm start -D /data/gtm

22.2.启动96机器standby

gtm_ctl -Z gtm_standby start -D /data/gtm_standby

注意：要先启动 gtm 再启动gtm_standby,否则gtm standby会因为无法连接到gtm而失败

22.3 用下面的命令查看gtm和standby启动状态

gtm_ctl -Z gtm status -D /data/gtm gtm_ctl -Z gtm_standby status -D /data/gtm_standby

22.4.在 97 98 99 三台机器上启动proxy

gtm_ctl -Z gtm_proxy start -D /data/gtm_proxy

22.5 .在 97 98 99 三台机器上启动 datanodes

pg_ctl start -D /data/pgdata -Z datanode

22.6. ps -ef|grep pgxc 查看启动信息

22.7.在 97 98 99 三台机器上启动coordinator

pg_ctl start -D /data/coordinator -Z coordinator

23. 注册集群节点信息，登录到 97 98 99  coordinator上执行

[[pgxl@cd1 coordinator]$ psql -p 5432 postgres 2018-03-14 16:04:49.826 CST [23830] LOG:  connection received: host=[local] 2018-03-14 16:04:49.829 CST [23830] LOG:  connection authorized: user=pgxl database=postgres psql (PGXL 10alpha2, based on PG 10beta3 (Postgres-XL 10alpha2)) Type "help" for help.

create node co1 with(type='coordinator',host='cd1',port=5432); create node co2 with(type='coordinator',host='cd2',port=5432); create node co3 with(type='coordinator',host='cd3',port=5432); create node dn1 with(type='datanode',host='cd1',port=5433); create node dn2 with(type='datanode',host='cd2',port=5433); create node dn3 with(type='datanode',host='cd3',port=5433);

postgres=# select * from pgxc_node; node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |  node_id   -----------+-----------+-----------+-----------+----------------+------------------+------------- co1       | C         |      5432 | localhost | f              | f                |  1344656819 co2       | C         |      5432 | cd2       | f              | f                |  474101254 co3       | C         |      5432 | cd3       | f              | f                | -1046823559 dn1       | D         |      5433 | cd1       | f              | f                |  -560021589 dn2       | D         |      5433 | cd2       | f              | f                |  352366662 dn3       | D         |      5433 | cd3       | f              | f                |  -700122826

不管是修改还是删除重建完成后你都需要重新加载文件 select pgxc_pool_reload()

postgres=#  select pgxc_pool_reload(); #以上操作三个节点最好都要执行一下 pgxc_pool_reload  ------------------ t (1 row)

24. 测试：

24.1. coordinator1上验证

[pgxc@cd1 coordinator]$ psql -p 5432 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# create table t (id int); LOG:  connection received: host=56.56.56.97 port=41712 LOG:  connection authorized: user=pgxc database=postgres CREATE TABLE postgres=#  postgres=# insert into t select generate_series(1,100); INSERT 0 100 postgres=# select count(*) from t; count  -------   100 (1 row)

24.2. coordinator2 上验证

[pgxc@cd2 coordinator]$ psql -p 5432 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# select count(*) from t; LOG:  connection received: host=56.56.56.98 port=34162 LOG:  connection authorized: user=pgxc database=postgres count  -------   100 (1 row)

24.3. coordinator23上验证

[pgxc@cd3 coordinator]$ psql -p 5432 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# select count(*) from t; LOG:  connection received: host=56.56.56.99 port=45888 LOG:  connection authorized: user=pgxc database=postgres count  -------   100 (1 row)

24.4.数据节点1验证

[pgxc@cd1 coordinator]$ psql -p 5433 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# select count(*) from t; count  -------     37 (1 row) postgres=# select * from t; id  ----   7 14 15 20 21 22 24 25 27 30 ........

24.5数据节点2

[pgxc@cd2 coordinator]$ psql -p 5433 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# select count(*) from t; count  -------     31 (1 row) postgres=# select * from t; id   -----   2   3   5   6   11   12   17   18   19   26 ......

24.6. 数据节点3

[pgxc@cd3 coordinator]$ psql -p 5433 postgres LOG:  connection received: host=[local] LOG:  connection authorized: user=pgxc database=postgres psql (PGXC 1.0.4, based on PG 9.1.13) Type "help" for help. postgres=# select count(*) from t; count  -------     32 (1 row) postgres=# select * from t; id  ----  1  4  8  9 10 13 16 23 28 29 31 ......