Postgres-XC集群部署实例（单机调试）
呼延曜灿
数据库版本：postgresxc 1.1(postgresql 9.2.4)
作者：高铭杰
邮箱：jackgo73@outlook.com
日期：2017年4月8日
Postgres-XC集群部署实例（单机调试）
开始接触神奇的xc :)，边学变记录单机部署pgxc集群的方法，仅供娱乐与调试。后面的实例会继续补充。（使用单用户+切换pg环境变量的脚本在调试代码的时候很方便）
| 版本 | 链接 |
|---|---|
| Postgres-XC1.0 1.1 1.2 | https://sourceforge.net/projects/postgres-xc/files/?source=navbar |
| postgres-x2 | https://github.com/postgres-x2/postgres-x2 |
1 部署规划
gtm <----- gtm_standby
/ \
gtm_proxy00 gtm_proxy01
/ \ / \
cn00 dn00 cn01 dn01
| node name | port | data dir |
|---|---|---|
| gtm | 9400 | $HOME/app/pgsql00 |
| gtm_standby | 9401 | $HOME/app/pgsql01 |
| gtm_proxy00 | 9402 | $HOME/app/pgsql02 |
| cn00 | 9403 (pool 10403) | $HOME/app/pgsql03 |
| dn00 | 9404 | $HOME/app/pgsql04 |
| gtm_proxy01 | 9405 | $HOME/app/pgsql05 |
| cn01 | 9406 (pool 10406) | $HOME/app/pgsql06 |
| dn01 | 9407 | $HOME/app/pgsql07 |
2 编译安装
./configure --prefix=$HOME/app/pgsql00 --with-openssl --enable-debug --enable-cassert --enable-thread-safety CFLAGS='-O0' --with-pgport=9400 --enable-depend;make -sj12;make install;3 初始化
命令执行前请根据上表切换相应的$PGDATA环境变量，可以编写脚本配合alias直接进行切换。
例如在.basesh中加入alias pp='source $HOME/bin/pgenv.sh'
这样的脚本简单方便，在德哥的很多博客（https://github.com/digoal/blog）中有提到，或者email我索取。
3.1 初始化gtm节点
($PGDATA=$HOME/app/pgsql00)
initgtm -Z gtm -D $PGDATA
sed -ir "s/#*nodename.*/nodename = 'gtm'/" $PGDATA/gtm.conf
sed -ir "s/#*listen_addresses.*/listen_addresses= '*'/" $PGDATA/gtm.conf
sed -ir "s/#*port.*/port = 9400/" $PGDATA/gtm.conf
sed -ir "s/#*startup.*/startup = ACT/" $PGDATA/gtm.conf3.2 初始化gtm_standby节点
($PGDATA=$HOME/app/pgsql01)
initgtm -Z gtm -D $PGDATA
sed -ir "s/#*nodename.*/nodename = 'gtm_standby'/" $PGDATA/gtm.conf
sed -ir "s/#*listen_addresses.*/listen_addresses= '*'/" $PGDATA/gtm.conf
sed -ir "s/#*port.*/port = 9401/" $PGDATA/gtm.conf
sed -ir "s/#*startup.*/startup = STANDBY/" $PGDATA/gtm.conf
sed -ir "s/#*active_host.*/active_host = 'localhost'/" $PGDATA/gtm.conf
sed -ir "s/#*active_port.*/active_port = 9400/" $PGDATA/gtm.conf3.3 初始化gtm_proxy00, gtm_proxy01节点
gtm_proxy00
($PGDATA=$HOME/app/pgsql02)
initgtm -Z gtm_proxy -D $PGDATA
sed -ir "s/#*nodename.*/nodename = 'gtm_proxy00'/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*port.*/port = 9402/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*gtm_host.*/gtm_host= 'localhost'/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9400/" $PGDATA/gtm_proxy.confgtm_proxy01
($PGDATA=$HOME/app/pgsql05)
initgtm -Z gtm_proxy -D $PGDATA
sed -ir "s/#*nodename.*/nodename = 'gtm_proxy01'/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*port.*/port = 9405/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/gtm_proxy.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9400/" $PGDATA/gtm_proxy.conf3.4 初始化cn节点
cn00
($PGDATA=$HOME/app/pgsql03)
initdb --nodename cn00 -D $PGDATA
sed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.conf
sed -ir "s/#*port.*/port = 9403/" $PGDATA/postgresql.conf
sed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.conf
sed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.conf
sed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.conf
sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf
sed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9402/" $PGDATA/postgresql.conf
sed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'cn00'/" $PGDATA/postgresql.conf
sed -ir "s/#*pooler_port.*/pooler_port= 10403/" $PGDATA/postgresql.confcn01
($PGDATA=$HOME/app/pgsql06)
initdb --nodename cn01 -D $PGDATA
sed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.conf
sed -ir "s/#*port.*/port = 9406/" $PGDATA/postgresql.conf
sed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.conf
sed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.conf
sed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.conf
sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf
sed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9405/" $PGDATA/postgresql.conf
sed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'cn01'/" $PGDATA/postgresql.conf
sed -ir "s/#*pooler_port.*/pooler_port= 10406/" $PGDATA/postgresql.conf3.5 初始化dn节点
dn00
($PGDATA=$HOME/app/pgsql04)
initdb --nodename dn00 -D $PGDATA
sed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.conf
sed -ir "s/#*port.*/port = 9404/" $PGDATA/postgresql.conf
sed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.conf
sed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.conf
sed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.conf
sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf
sed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9402/" $PGDATA/postgresql.conf
sed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'dn00'/" $PGDATA/postgresql.confdn01
($PGDATA=$HOME/app/pgsql07)
initdb --nodename dn01 -D $PGDATA
sed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.conf
sed -ir "s/#*port.*/port = 9407/" $PGDATA/postgresql.conf
sed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.conf
sed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.conf
sed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.conf
sed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.conf
sed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.conf
sed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.conf
sed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.conf
sed -ir "s/#*gtm_port.*/gtm_port = 9405/" $PGDATA/postgresql.conf
sed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'dn01'/" $PGDATA/postgresql.conf4 启停集群
4.1 启动gtm
($PGDATA=$HOME/app/pgsql00)
gtm_ctl -Z gtm start -D $PGDATA
gtm_ctl -Z gtm status -D $PGDATA
($PGDATA=$HOME/app/pgsql01)
gtm_ctl -Z gtm_standby start -D $PGDATA
gtm_ctl -Z gtm_standby status -D $PGDATA4.2 启动gtm_proxy
($PGDATA=$HOME/app/pgsql02)
gtm_ctl -Z gtm_proxy start -D $PGDATA
gtm_ctl -Z gtm_proxy status -D $PGDATA
($PGDATA=$HOME/app/pgsql05)
gtm_ctl -Z gtm_proxy start -D $PGDATA
gtm_ctl -Z gtm_proxy status -D $PGDATA4.3 启动datanode coordinators
($PGDATA=$HOME/app/pgsql04)
pg_ctl start -D $PGDATA -Z datanode
($PGDATA=$HOME/app/pgsql07)
pg_ctl start -D $PGDATA -Z datanode
($PGDATA=$HOME/app/pgsql03)
pg_ctl start -D $PGDATA -Z coordinator
($PGDATA=$HOME/app/pgsql06)
pg_ctl start -D $PGDATA -Z coordinator4.4 正常启动后的进程
/home/pathto/app/pgsql00/bin/gtm -D /home/pathto/app/data/pg_root00
/home/pathto/app/pgsql01/bin/gtm -D /home/pathto/app/data/pg_root01
/home/pathto/app/pgsql02/bin/gtm_proxy -D /home/pathto/app/data/pg_root02
/home/pathto/app/pgsql05/bin/gtm_proxy -D /home/pathto/app/data/pg_root05
/home/pathto/app/pgsql04/bin/postgres --datanode -D /home/pathto/app/data/pg_root04
postgres: logger process
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: stats collector process
/home/pathto/app/pgsql07/bin/postgres --datanode -D /home/pathto/app/data/pg_root07
postgres: logger process
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: stats collector process
/home/pathto/app/pgsql03/bin/postgres --coordinator -D /home/pathto/app/data/pg_root03
postgres: logger process
postgres: pooler process
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: stats collector process
/home/pathto/app/pgsql06/bin/postgres --coordinator -D /home/pathto/app/data/pg_root06
postgres: logger process
postgres: pooler process
postgres: checkpointer process
postgres: writer process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: stats collector process 4.5 停止顺序
coordinator
|
datanode
|
gtm_proxy
|
gtm
|
gtm_standby6 配置集群
6.1 配置cn00 cn01
psql连接cn00执行：
CREATE NODE dn00 WITH (TYPE='datanode', PORT=9404);
CREATE NODE dn01 WITH (TYPE='datanode', PORT=9407);
CREATE NODE cn01 WITH (TYPE='coordinator', PORT=9406);
select pgxc_pool_reload();
select * from pgxc_node;psql连接cn01执行：
CREATE NODE dn00 WITH (TYPE='datanode', PORT=9404);
CREATE NODE dn01 WITH (TYPE='datanode', PORT=9407);
CREATE NODE cn00 WITH (TYPE='coordinator', PORT=9403);
select pgxc_pool_reload();
select * from pgxc_node;7 使用实例（主要来自唐成的《PostgreSQL修炼之道》）
7.1 例1 create table
postgres=# create table user_info_hash(id int primary key, firstname text, lastname text, info text) distribute by hash(id) to node (dn00, dn01);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_info_hash_pkey" for table "user_info_hash"
CREATE TABLEpostgres=# \d+ user_info_hash
Table "public.user_info_hash"
Column | Type | Modifiers | Storage | Stats target | Description
-----------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
firstname | text | | extended | |
lastname | text | | extended | |
info | text | | extended | |
Indexes:
"user_info_hash_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES7.2 例2 DISTRIBUTE & NODE GROUP
xc建表的DISTRIBUTE语法
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { [HASH | MODULO ] ( column_name ) } } ]
[ TO { GROUP groupname | NODE ( nodename [, … ] ) } ]
| DISTRIBUTE BY ? | 说明 | 限制 |
|---|---|---|
| REPLICATION | 不同节点上有相同数据 | |
| ROUNDROBIN | 根据插入顺序把数据插入到不同节点中 | |
| HASH | 按hash结果分布数据 | 分布键必须是唯一约束 |
| MODULO | 按去模的方式分布数据 | 分布键必须是唯一约束 |
cn00 / cn01 两个节点执行：
CREATE NODE GROUP ngrp2 WITH (dn00, dn01);任意cn执行：
CREATE TABLE testgrp2 (id int primary key, note text) DISTRIBUTE BY HASH(id) TO GROUP ngrp2;postgres=# \d+ testgrp2
Table "public.testgrp2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
note | text | | extended | |
Indexes:
"testgrp2_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES创建一般表，分布键默认使用主键：
CREATE TABLE test2 (id int primary key, note text);postgres=# \d+ test2
Table "public.test2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
note | text | | extended | |
Indexes:
"test2_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
Distribute By: HASH(id)
Location Nodes: ALL DATANODES插入数据：
postgres=# insert into test2 values(generate_series(1,100), 'text');
ERROR: set-valued function called in context that cannot accept a set
insert into test2 values(1, 'text');
insert into test2 values(2, 'text');
insert into test2 values(3, 'text');
insert into test2 values(4, 'text');
insert into test2 values(5, 'text');
insert into test2 values(6, 'text');
insert into test2 values(7, 'text');查看node00
postgres=# select * from test2;
id | note
----+------
1 | text
2 | text
5 | text
6 | text
(4 rows)查看node01
postgres=# select * from test2;
id | note
----+------
3 | text
4 | text
7 | text
(3 rows)在CN上查询底层数据节点的语法
postgres=# execute direct on(dn00) 'select * from test2';
id | note
----+------
1 | text
2 | text
5 | text
6 | text
(4 rows)
postgres=# execute direct on(dn01) 'select * from test2';
id | note
----+------
3 | text
4 | text
7 | text
(3 rows)7.3 例3 REPLICATION MODULO ROUNDROBIN
create table test3 (id int primary key, note text) distribute by replication to node (dn00,dn01);
insert into test3 values(1, 'text');
insert into test3 values(2, 'text');
insert into test3 values(3, 'text');
insert into test3 values(4, 'text');
insert into test3 values(5, 'text');
insert into test3 values(6, 'text');
insert into test3 values(7, 'text');postgres=# execute direct on(dn01) 'select * from test3';
id | note
----+------
1 | text
2 | text
3 | text
4 | text
5 | text
6 | text
7 | text
(7 rows)
postgres=# execute direct on(dn00) 'select * from test3';
id | note
----+------
1 | text
2 | text
3 | text
4 | text
5 | text
6 | text
7 | text
(7 rows)modulo
create table test4 (id int primary key, note text) distribute by modulo(id) to node (dn00,dn01);
insert into test4 values(1, 'text');
insert into test4 values(2, 'text');
insert into test4 values(3, 'text');
insert into test4 values(4, 'text');
insert into test4 values(5, 'text');
insert into test4 values(6, 'text');
insert into test4 values(7, 'text');按节点个数取模，使用id mod 2的结果来分布数据
postgres=# execute direct on(dn00) 'select * from test4';
id | note
----+------
2 | text
4 | text
6 | text
(3 rows)
postgres=# execute direct on(dn01) 'select * from test4';
id | note
----+------
1 | text
3 | text
5 | text
7 | text
(4 rows)
roundrobin （无主键）
postgres=# create table test5 (id int, note text) distribute by ROUNDROBIN;
insert into test5 values(1, 'text');
insert into test5 values(2, 'text');
insert into test5 values(3, 'text');
insert into test5 values(4, 'text');
insert into test5 values(5, 'text');
insert into test5 values(6, 'text');
insert into test5 values(7, 'text');数据依次插入各节点
postgres=# execute direct on(dn00) 'select * from test5';
id | note
----+------
2 | text
4 | text
6 | text
(3 rows)
postgres=# execute direct on(dn01) 'select * from test5';
id | note
----+------
1 | text
3 | text
5 | text
7 | text
(4 rows)
7.4 例4 数据重分布
待补充8 其他
8.1 XC1.1 Restrictions and Issues you need to know!
| Restrictions |
|---|
| SERIALIZABLE and REPEATABLE READ transaction isolation modes are not supported. |
| Only constraints which can be enforced only locally to a datanode are supported. For example, UNIQUE constraints for non-distribute column is not supported in distributed tables. |
| Using complicated statement in PREPARE steatement may not be supported (depends upon specific statement). |
| Row triggers do not work with COPY. |
| Privilege to views may not work correctly. |
| COPY TO from replicated table does not work. |
| Quoted cast may not work. For example, select “my table”.::”my table” from “my table”; does not work. Use select row(“my table”.) from “my table”; instead. |
| SQL functions containing utility statement may not work. |
| DML cannot be used in plpgsql functions. |
| CREATE TABLE AS EXECUTE is not supported. |
| WHERE CURRENT OF is not supported. |
| In cursors, MOVE BACKWARD works only if SCROLL is used in the cursor. |
| WITH HOLD cursors are not supported. |
| Foreign Data Wrapper is not supported. Following related SQL statements are not supported. |
| CREATE FOREIGN DATA WRAPPER, ALTER FOREIGN DATA WRAPPER, DROP FOREIGN DATA WRAPPER, CREATE FOREIGN TABLE, ALTER FOREIGN TABLE, DROP FOREING TABLE, CREATE SERVER, ALTER SERVER, DROP SERVER, CREATE USER MAPPING, ALTER USER MAPPING, DROP USER MAPPING. |
| Savepoint is not supported. Following related SQL statements are not supported. |
| SAVEPOINT, RELEASE SAVEPOINT and ROLLBACK TO SAVEPOINT. |
| LISTEN, UNLISTEN and NOTIFY work only locally at a coordinator. |
| SECURITY LABEL is not supported. |
| Exception block in plpgsql is not available because SAVEPOINT is not supported. |
| Distribution key of a table cannot be updated. |
| Statistics are not collected globally. These statistic data are maintained locally to each node. |
| BARRIERs do not have timeout, meaning if a 2PC transaction is stuck forever, barrier will be stuck too. |
| CREATE TABLE SELECT statement will cause error if SELECT statement involves aggregate functions such that its transition function returns different type of row from the final row. |
| Issues |
|---|
| Regression test may have random failure. You can run the regression again to see if the test is successful. |
| The following regression fails: int4, point, create_index, inherit, constraints, join, aggregates, privileges, window and plpgsql. |
类似资料：