Postgres_XL 简要安装配置说明

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=#