Postgres_XL 简要安装配置说明

9iiooi

1.系统需求

CentOS 7.2 X64
postgres-xl-9.5r1.4.tar.gz
# yum install -y gcc-c++
# yum install -y readline-devel
# yum install -y zlib-devel
# yum install -y perl
# yum install -y flex
# yum install -y flex-devel

2.安装postgres-xl
/home/soft/
tar zxvf postgres-xl-9.5r1.4.tar.gz
./configure
make
make install

3.添加用户
adduser postgres
su - postgres
vi .bash_profile
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PATH=/usr/local/pgsql/bin:$PATH
export MANPATH=/usr/local/pgsql/share/man:$MANPATH

4.建立data目录
--建立coord, datanode, gtm的data目录
mkdir -p /opt/pgxl/data/data_coord1
mkdir -p /opt/pgxl/data/data_datanode_1
mkdir -p /opt/pgxl/data/data_datanode_2
mkdir -p /opt/pgxl/data/data_gtm
chown postgres /opt/pgxl/data/data_coord1
chown postgres /opt/pgxl/data/data_datanode_1
chown postgres /opt/pgxl/data/data_datanode_2
chown postgres /opt/pgxl/data/data_gtm

5.初始化coordinator, datanode
su - postgres
/usr/local/pgsql/bin/initdb -D /opt/pgxl/data/data_coord1 \
  --nodename coord1
/usr/local/pgsql/bin/initdb -D /opt/pgxl/data/data_datanode_1 \
  --nodename datanode_1
/usr/local/pgsql/bin/initdb -D /opt/pgxl/data/data_datanode_2 \
  --nodename datanode_2

/usr/local/pgsql/bin/initgtm -D /opt/pgxl/data/data_gtm -Z gtm

6.启动gtm, datanode, coordinator
-- gtm
/usr/local/pgsql/bin/gtm -D /opt/pgxl/data/data_gtm >/opt/pgxl/data/data_gtm/gtm_logfile.log 2>&1 &
-- datanode
/usr/local/pgsql/bin/postgres --datanode -p 15432 -c pooler_port=40101 \
  -D /opt/pgxl/data/data_datanode_1 >/opt/pgxl/data/data_datanode_1/datanode1_logfile.log 2>&1 &

/usr/local/pgsql/bin/postgres --datanode -p 15433 -c pooler_port=40102 \
  -D /opt/pgxl/data/data_datanode_2 >/opt/pgxl/data/data_datanode_1/datanode2_logfile.log 2>&1 &

-- coordinator
/usr/local/pgsql/bin/postgres --coordinator -c pooler_port=40100 \
  -D /opt/pgxl/data/data_coord1 >/opt/pgxl/data/data_coord1/data_coord1_logfile.log 2>&1 &

7.配置节点类型、端口
/usr/local/pgsql/bin/psql -c "ALTER NODE coord1 \
  WITH (TYPE = 'coordinator', PORT = 5432)" postgres
/usr/local/pgsql/bin/psql -c "CREATE NODE datanode_1 \
  WITH (TYPE = 'datanode', PORT = 15432)" postgres
/usr/local/pgsql/bin/psql -c "CREATE NODE datanode_2 \
  WITH (TYPE = 'datanode', PORT = 15433)" postgres

8.配置执行导向
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_1) \
  'ALTER NODE datanode_1 WITH (TYPE = ''datanode'', PORT = 15432)'" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_1) \
  'CREATE NODE datanode_2 WITH (TYPE = ''datanode'', PORT = 15433)'" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_1) \
  'CREATE NODE coord1 WITH (TYPE = ''coordinator'', PORT = 5432)'" postgres

/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_2) \
  'ALTER NODE datanode_2 WITH (TYPE = ''datanode'', PORT = 15433)'" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_2) \
  'CREATE NODE datanode_1 WITH (TYPE = ''datanode'', PORT = 15432)'" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_2) \
  'CREATE NODE coord1 WITH (TYPE = ''coordinator'', PORT = 5432)'" postgres

9.重载配置
/usr/local/pgsql/bin/psql -c "SELECT pgxc_pool_reload()" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_1) \
  'SELECT pgxc_pool_reload()'" postgres
/usr/local/pgsql/bin/psql -c "EXECUTE DIRECT ON (datanode_2) \
  'SELECT pgxc_pool_reload()'" postgres

10.插入测试
创建测试表

登录 coordinator
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
test=# create table t1(id int, name varchar(20))
test=# \dt
        List of relations
Schema | Name | Type  |  Owner   
--------+------+-------+----------
public | t1   | table | postgres
(1 row)
test=# insert into t1 SELECT generate_series(1,1000000) as key, 'Peter'||(random()*(10^3))::integer;
INSERT 0 1000000
test=# \timing
Timing is on.
test=# insert into t1 SELECT generate_series(1000001,5000000) as key, 'Peter'||(random()*(10^3))::integer;
INSERT 0 4000000
Time: 60653.631 ms
test=#

查看数据分布
登录datanode_1
[postgres@localhost ~]$ psql -p 15432 test
psql (PGXL 9.5r1.4, based on PG 9.5.5 (Postgres-XL 9.5r1.4))
Type "help" for help.
test=# select count(*) from t1;
  count  
---------
2502488
(1 row)
test=# \q

登录datanode_2
[postgres@localhost ~]$ psql -p 15433 test
psql (PGXL 9.5r1.4, based on PG 9.5.5 (Postgres-XL 9.5r1.4))
Type "help" for help.
test=# select count(*) from t1;
  count  
---------
2497512
(1 row)
test=# \q

登录 coordinator
[postgres@localhost ~]$ psql -p test
psql: invalid port number: "test"
[postgres@localhost ~]$ psql test
psql (PGXL 9.5r1.4, based on PG 9.5.5 (Postgres-XL 9.5r1.4))
Type "help" for help.

test=# select count(*) from t1;
  count  
---------
5000000
(1 row)
test=# \dt t1;
        List of relations
Schema | Name | Type  |  Owner   
--------+------+-------+----------
public | t1   | table | postgres
(1 row)
test=#