Postgres-XC集群部署实例（单机调试）

来源：互联网发布：ping远程ip的8080端口编辑：程序博客网时间：2024/06/11 18:01

数据库版本：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 $PGDATAsed -ir "s/#*nodename.*/nodename = 'gtm'/" $PGDATA/gtm.confsed -ir "s/#*listen_addresses.*/listen_addresses= '*'/" $PGDATA/gtm.confsed -ir "s/#*port.*/port = 9400/" $PGDATA/gtm.confsed -ir "s/#*startup.*/startup = ACT/" $PGDATA/gtm.conf

3.2 初始化gtm_standby节点

($PGDATA=$HOME/app/pgsql01)initgtm -Z gtm -D $PGDATAsed -ir "s/#*nodename.*/nodename = 'gtm_standby'/" $PGDATA/gtm.confsed -ir "s/#*listen_addresses.*/listen_addresses= '*'/" $PGDATA/gtm.confsed -ir "s/#*port.*/port = 9401/" $PGDATA/gtm.confsed -ir "s/#*startup.*/startup = STANDBY/" $PGDATA/gtm.confsed -ir "s/#*active_host.*/active_host = 'localhost'/" $PGDATA/gtm.confsed -ir "s/#*active_port.*/active_port = 9400/" $PGDATA/gtm.conf

3.3 初始化gtm_proxy00, gtm_proxy01节点

gtm_proxy00

($PGDATA=$HOME/app/pgsql02)initgtm -Z gtm_proxy -D $PGDATAsed -ir "s/#*nodename.*/nodename = 'gtm_proxy00'/" $PGDATA/gtm_proxy.confsed -ir "s/#*port.*/port = 9402/" $PGDATA/gtm_proxy.confsed -ir "s/#*gtm_host.*/gtm_host= 'localhost'/" $PGDATA/gtm_proxy.confsed -ir "s/#*gtm_port.*/gtm_port = 9400/" $PGDATA/gtm_proxy.conf

gtm_proxy01

($PGDATA=$HOME/app/pgsql05)initgtm -Z gtm_proxy -D $PGDATAsed -ir "s/#*nodename.*/nodename = 'gtm_proxy01'/" $PGDATA/gtm_proxy.confsed -ir "s/#*port.*/port = 9405/" $PGDATA/gtm_proxy.confsed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/gtm_proxy.confsed -ir "s/#*gtm_port.*/gtm_port = 9400/" $PGDATA/gtm_proxy.conf

3.4 初始化cn节点

cn00

($PGDATA=$HOME/app/pgsql03)initdb --nodename cn00 -D $PGDATAsed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.confsed -ir "s/#*port.*/port = 9403/" $PGDATA/postgresql.confsed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.confsed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.confsed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.confsed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.confsed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.confsed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.confsed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.confsed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.confsed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.confsed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.confsed -ir "s/#*gtm_port.*/gtm_port = 9402/" $PGDATA/postgresql.confsed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'cn00'/" $PGDATA/postgresql.confsed -ir "s/#*pooler_port.*/pooler_port= 10403/" $PGDATA/postgresql.conf

cn01

($PGDATA=$HOME/app/pgsql06)initdb --nodename cn01 -D $PGDATAsed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.confsed -ir "s/#*port.*/port = 9406/" $PGDATA/postgresql.confsed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.confsed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.confsed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.confsed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.confsed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.confsed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.confsed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.confsed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.confsed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.confsed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.confsed -ir "s/#*gtm_port.*/gtm_port = 9405/" $PGDATA/postgresql.confsed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'cn01'/" $PGDATA/postgresql.confsed -ir "s/#*pooler_port.*/pooler_port= 10406/" $PGDATA/postgresql.conf

3.5 初始化dn节点

dn00

($PGDATA=$HOME/app/pgsql04)initdb --nodename dn00 -D $PGDATAsed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.confsed -ir "s/#*port.*/port = 9404/" $PGDATA/postgresql.confsed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.confsed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.confsed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.confsed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.confsed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.confsed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.confsed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.confsed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.confsed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.confsed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.confsed -ir "s/#*gtm_port.*/gtm_port = 9402/" $PGDATA/postgresql.confsed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'dn00'/" $PGDATA/postgresql.conf

dn01

($PGDATA=$HOME/app/pgsql07)initdb --nodename dn01 -D $PGDATAsed -ir "s/#*listen_addresses.*/listen_addresses = '*'/" $PGDATA/postgresql.confsed -ir "s/#*port.*/port = 9407/" $PGDATA/postgresql.confsed -ir "s/#*logging_collector.*/logging_collector= on/" $PGDATA/postgresql.confsed -ir "s/#*log_directory.*/log_directory = 'pg_log'/" $PGDATA/postgresql.confsed -ir "s/#*log_filename.*/log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_size.*/log_rotation_size = 10MB/" $PGDATA/postgresql.confsed -ir "s/#*log_line_prefix.*/log_line_prefix='%p %r %u %d %t %e'/" $PGDATA/postgresql.confsed -ir "s/#*log_min_duration_statement.*/log_min_duration_statement= 1000/" $PGDATA/postgresql.confsed -ir "s/#*log_timezone.*/log_timezone = 'UTC'/" $PGDATA/postgresql.confsed -ir "s/#*log_truncate_on_rotation.*/log_truncate_on_rotation = on/" $PGDATA/postgresql.confsed -ir "s/#*log_rotation_age.*/log_rotation_age = 0/" $PGDATA/postgresql.confsed -ir "s/#*log_statement.*/log_statement= 'all'/" $PGDATA/postgresql.confsed -ir "s/#*max_prepared_transactions.*/max_prepared_transactions= 800/" $PGDATA/postgresql.confsed -ir "s/#*gtm_host.*/gtm_host = 'localhost'/" $PGDATA/postgresql.confsed -ir "s/#*gtm_port.*/gtm_port = 9405/" $PGDATA/postgresql.confsed -ir "s/#*pgxc_node_name.*/pgxc_node_name = 'dn01'/" $PGDATA/postgresql.conf

4 启停集群

4.1 启动gtm

($PGDATA=$HOME/app/pgsql00)gtm_ctl -Z gtm start -D $PGDATAgtm_ctl -Z gtm status -D $PGDATA($PGDATA=$HOME/app/pgsql01)gtm_ctl -Z gtm_standby start -D $PGDATAgtm_ctl -Z gtm_standby status -D $PGDATA

4.2 启动gtm_proxy

($PGDATA=$HOME/app/pgsql02)gtm_ctl -Z gtm_proxy start -D $PGDATAgtm_ctl -Z gtm_proxy status -D $PGDATA($PGDATA=$HOME/app/pgsql05)gtm_ctl -Z gtm_proxy start -D $PGDATAgtm_ctl -Z gtm_proxy status -D $PGDATA

4.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 coordinator

4.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_root04postgres: 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_root07postgres: 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_root03postgres: 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_root06postgres: 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_standby

6 配置集群

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 TABLE

postgres=# \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: noDistribute By: HASH(id)Location Nodes: ALL DATANODES

7.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: noDistribute 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: noDistribute 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 setinsert 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.

0 0