<PG-XC搭建测试记录>
最近在学习PG-XC， 目前PG-XC已经更名为PG-X2，而且由中国开发社区接力开发，相信以后尤其是国内的发展前景更好。
参考文档： 主要按照德哥和Francs的文章进行了学习测试，在此表示感谢，都是国内PG的先驱啊。
http://blog.163.com/digoal@126/blog/static/16387704020133292915600/
http://blog.163.com/digoal@126/blog/static/1638770402013332335933/
http://francs3.blog.163.com/blog/static/405767272012532308480/
---官方文档：
15.1. Short Version
The following short installation allows to install a simple cluster on a local machine with 1 Coordinator, 2 Datanodes and 1 GTM. When installing a more complex cluster, you might change the number of Coordinators and Datanodes, and might have to start nodes on different servers.
./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data_coord1
mkdir /usr/local/pgsql/data_datanode1
mkdir /usr/local/pgsql/data_datanode2
mkdir /usr/local/pgsql/data_gtm
chown postgres /usr/local/pgsql/data_coord1
chown postgres /usr/local/pgsql/data_datanode1
chown postgres /usr/local/pgsql/data_datanode2
chown postgres /usr/local/pgsql/data_gtm
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data_coord1 --nodename coord1
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data_datanode1 --nodename datanode1
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data_datanode2 --nodename datanode2
/usr/local/pgsql/bin/initgtm -D /usr/local/pgsql/data_gtm -Z gtm
/usr/local/pgsql/bin/gtm -D /usr/local/pgsql/data_gtm >logfile 2>&1 &
/usr/local/pgsql/bin/postgres -X -p 15432 -D /usr/local/pgsql/data_datanode1 >logfile 2>&1 &
/usr/local/pgsql/bin/postgres -X -p 15433 -D /usr/local/pgsql/data_datanode2 >logfile 2>&1 &
/usr/local/pgsql/bin/postgres -C -D /usr/local/pgsql/data_coord1 >logfile 2>&1 &
/usr/local/pgsql/bin/psql -c "CREATE NODE datanode1 WITH (TYPE = 'datanode', PORT = 15432)" postgres
/usr/local/pgsql/bin/psql -c "CREATE NODE datanode2 WITH (TYPE = 'datanode', PORT = 15433)" postgres
/usr/local/pgsql/bin/psql -c "SELECT pgxc_pool_reload()" postgres
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
---搭建过程：
准备工作：
在5个节点使用root创建pgxc用户、安装pg-xc软件：
root用户：
/etc/hosts:
192.168.100.106 DEV-2 gtm
192.168.100.107 DEV-3 gtm_standby
192.168.100.108 DEV-4 cd1
192.168.100.109 DEV-5 cd2
192.168.100.110 DEV-6 cd3
mkdir -p /opt/postgres
[root@DEV-2 opt]# id
uid=0(root) gid=0(root) groups=0(root)
[root@DEV-2 opt]# groupadd pgxc
[root@DEV-2 opt]# useradd pgxc -g pgxc -d /opt/postgres
[root@DEV-2 opt]# passwd pgxc
Changing password for user pgxc.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@DEV-2 opt]#
cd /opt;chown pgxc:pgxc postgres
[root@DEV-6 opt]# su - pgxc
[pgxc@DEV-6 ~]$ cd /opt/postgres
[pgxc@DEV-6 postgres]$ mkdir coordinator pgdata gtm_proxy
[pgxc@DEV-6 postgres]$ ls -tlr
total 12
drwxrwxr-x 2 pgxc pgxc 4096 Aug 7 16:56 pgdata
drwxrwxr-x 2 pgxc pgxc 4096 Aug 7 16:56 gtm_proxy
drwxrwxr-x 2 pgxc pgxc 4096 Aug 7 16:56 coordinator
[pgxc@DEV-6 postgres]$
root:
tar zxvf pgxc-v1.0.4.tar.gz
yum install pam pam-devel
./configure --prefix=/opt/postgres --with-perl --with-python --with-openssl --with-pam --with-libxml --with-libxslt --with-libedit-preferred --enable-thread-safety
gmake
gmake install-world
su - pgxc
vi .bash_profile
1. gtm
export PGPORT=6666
export PGDATA=/opt/postgres/gtm
export.utf8
export PGHOME=/opt/postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
2. gtm_standby
export PGPORT=6666
export PGDATA=/opt/postgres/gtm_standby
export.utf8
export PGHOME=/opt/postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
3. gtm_proxy, coordinate, datanode
export.utf8
export PGHOME=/opt/postgres
export LD_LIBRARY_PATH=$PGHOME/lib:$PGHOME/lib/postgresql:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
[开始初始化Postgres-XC集群]
1. 初始化datanode
192.168.100.108 DEV-4 cd1
initdb -D /opt/postgres/pgdata --nodename=datanode_1 -E UTF8 --locale=C -U postgres -W
192.168.100.109 DEV-5 cd2
initdb -D /opt/postgres/pgdata --nodename=datanode_2 -E UTF8 --locale=C -U postgres -W
192.168.100.110 DEV-6 cd3
initdb -D /opt/postgres/pgdata --nodename=datanode_3 -E UTF8 --locale=C -U postgres -W
Enter new superuser password:
Enter it again: opt1234
2. 初始化coordinate
192.168.100.108 DEV-4 cd1
initdb -D /opt/postgres/coordinator --nodename=coordinator_1 -E UTF8 --locale=C -U postgres -W
192.168.100.109 DEV-5 cd2
initdb -D /opt/postgres/coordinator --nodename=coordinator_2 -E UTF8 --locale=C -U postgres -W
192.168.100.110 DEV-6 cd3
initdb -D /opt/postgres/coordinator --nodename=coordinator_3 -E UTF8 --locale=C -U postgres -W
3. 初始化gtm
192.168.100.106 DEV-2 gtm
su - pgxc
initgtm -Z gtm -D /opt/postgres/gtm
-bash-4.1$ initgtm -Z gtm -D /opt/postgres/gtm
The files belonging to this GTM system will be owned by user "pgxc".
This user must also own the server process.
fixing permissions on existing directory /opt/postgres/gtm ... ok
creating configuration files ... ok
Success. You can now start the GTM server using:
gtm -D /opt/postgres/gtm
or
gtm_ctl -Z gtm -D /opt/postgres/gtm -l logfile start
-bash-4.1$
vi gtm.conf
nodename = 'one' # 所有gtm节点nodename唯一.
listen_addresses = '0.0.0.0'
port = 6666
startup = ACT
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = WARNING
synchronous_backup = on
4. 初始化gtm_standby
su - pgxc
initgtm -Z gtm -D /opt/postgres/gtm_standby
nodename = 'two' # 所有gtm节点nodename唯一.
listen_addresses = '0.0.0.0'
port = 6666
startup = STANDBY
active_host = '192.168.100.106'
active_port = 6666
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm.log'
log_min_messages = WARNING
synchronous_backup = on
5. 启动gtm
su - pgxc
-bash-4.1$ gtm_ctl start -Z gtm -D /opt/postgres/gtm
server starting
-bash-4.1$
-bash-4.1$
-bash-4.1$ ps -ef|grep pgxc
root 26976 26922 0 09:48 pts/1 00:00:00 su - pgxc
pgxc 26977 26976 0 09:48 pts/1 00:00:00 -bash
pgxc 28474 1 0 17:40 pts/1 00:00:00 /opt/postgres/bin/gtm -D /opt/postgres/gtm
pgxc 28475 26977 0 17:40 pts/1 00:00:00 ps -ef
pgxc 28476 26977 0 17:40 pts/1 00:00:00 grep pgxc
-bash-4.1$ gtm_ctl status -Z gtm -D /opt/postgres/gtm
gtm_ctl: server is running (PID: 28474)
"-D" "/opt/postgres/gtm"
1 master
-bash-4.1$
6. 启动gtm_standby
su - pgxc
-bash-4.1$ gtm_ctl start -Z gtm -D /opt/postgres/gtm_standby
server starting
-bash-4.1$
-bash-4.1$ ps -ef|grep pgxc
root 20704 20635 0 09:48 pts/0 00:00:00 su - pgxc
pgxc 20705 20704 0 09:48 pts/0 00:00:00 -bash
pgxc 22856 1 0 17:41 pts/0 00:00:00 /opt/postgres/bin/gtm -D /opt/postgres/gtm_standby
pgxc 22858 20705 0 17:41 pts/0 00:00:00 ps -ef
pgxc 22859 20705 0 17:41 pts/0 00:00:00 grep pgxc
-bash-4.1$ gtm_ctl status -Z gtm -D /opt/postgres/gtm_standby
gtm_ctl: server is running (PID: 22856)
0 slave
-bash-4.1$
7. 初始化gtm_proxy (192.168.100.108 - 192.168.100.110)
su - pgxc
initgtm -Z gtm_proxy -D /opt/postgres/gtm_proxy
cd /opt/postgres/gtm_proxy
vi gtm_proxy.conf
nodename = '1' # 所有代理节点nodename唯一.(本例为: 1,2,3)
listen_addresses = '0.0.0.0'
port = 6666
worker_threads = 1
gtm_host = '192.168.100.106'
gtm_port = 6666
gtm_connect_retry_idle = 30
gtm_connect_retry_count = 10
gtm_connect_retry_interval = 10
err_wait_idle = 60
err_wait_count = 10
err_wait_interval = 10
keepalives_idle = 60
keepalives_interval = 10
keepalives_count = 10
log_file = 'gtm_proxy.log'
log_min_messages = WARNING
8. 启动gtm_proxy
su - pgxc
gtm_ctl start -Z gtm_proxy -D /opt/postgres/gtm_proxy
gtm_ctl status -Z gtm_proxy -D /opt/postgres/gtm_proxy
9. 配置datanode (192.168.100.108 - 192.168.100.110)
su - pgxc
cd /opt/postgres/pgdata
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 6434
max_connections = 1600 # 与max_prepared_transactions一致, #(datanode的max_connection>=coordinator.max_coordinators*coordinator.max_connection.)
superuser_reserved_connections = 13
unix_socket_directory = '.'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 1024MB
max_prepared_transactions = 1600 # 与max_connections一致
maintenance_work_mem = 512MB
max_stack_depth = 8MB
wal_level = hot_standby
synchronous_commit = off
wal_buffers = 16384kB
wal_writer_delay = 10ms
checkpoint_segments = 64
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 32
wal_sender_delay = 10ms
wal_keep_segments = 256
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 300s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
random_page_cost = 1.0
effective_cache_size = 8192MB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_statement = 'ddl'
track_activity_query_size = 2048
log_autovacuum_min_duration = 0
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
gtm_host = '192.168.100.108' # 配置为对应的gtm_proxy的地址和端口. 获取gxid,sequence,timestamp等全局信息.
gtm_port = 6666
pgxc_node_name = 'datanode_1' # 集群中的每个datanode名字必须唯一. 对应initdb 时指定的值.
enforce_two_phase_commit = on
enable_fast_query_shipping = on
enable_remotejoin = on
enable_remotegroup = on
配置pg_hba.conf：
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host all all 192.168.100.106/32 trust
host all all 192.168.100.107/32 trust
host all all 192.168.100.108/32 trust
host all all 192.168.100.109/32 trust
host all all 192.168.100.110/32 trust
host all all 0.0.0.0/0 md5
10. 启动datanode (192.168.100.108 - 192.168.100.110)
su - pgxc
pg_ctl -Z datanode start -D /opt/postgres/pgdata
pg_ctl -Z datanode stop -D /opt/postgres/pgdata
11. 配置coordinate (192.168.100.108 - 192.168.100.110)
su - pgxc
cd /opt/postgres/coordinator
vi postgresql.conf
listen_addresses = '0.0.0.0'
port = 6435
max_connections = 100
superuser_reserved_connections = 13
unix_socket_directory = '/tmp'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
shared_buffers = 512MB
max_prepared_transactions = 16 # 用作二阶事务, 必须>=max_coordinators
maintenance_work_mem = 128MB
max_stack_depth = 8MB
wal_level = minimal
synchronous_commit = off
wal_buffers = 16384kB
wal_writer_delay = 10ms
checkpoint_segments = 128
random_page_cost = 1.0
effective_cache_size = 8192MB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_autovacuum_min_duration = 0
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
pooler_port = 6667 # Pool Manager TCP port
min_pool_size = 1
max_pool_size = 6416 # >=(max_connection*max_datanodes+max_coordinators); #(datanode的max_connection>=max_coordinators*this.max_connection.)
max_coordinators = 16 # Maximum number of Coordinators
max_datanodes = 64 # Maximum number of Datanodes
gtm_host = '192.168.100.108' # Host name or address of GTM, 指定本机的gtm_proxy
gtm_port = 6666 # Port of GTM, 指定本机的gtm_proxy
pgxc_node_name = 'coordinator_1' # Coordinator or Datanode name, 集群中的coordinate名字必须唯一, 与initdb初始化时指定的匹配.
enforce_two_phase_commit = on # Enforce the usage of two-phase commit on transactions
enable_fast_query_shipping = on
enable_remotejoin = on
enable_remotegroup = on
配置pg_hba.conf：
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
host all all 192.168.100.106/32 trust
host all all 192.168.100.107/32 trust
host all all 192.168.100.108/32 trust
host all all 192.168.100.109/32 trust
host all all 192.168.100.110/32 trust
host all all 0.0.0.0/0 md5
12. 启动coordinate (192.168.100.108 - 192.168.100.110)
su - pgxc
pg_ctl -Z coordinator start -D /opt/postgres/coordinator
-bash-4.1$ netstat -anp | grep gtm
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:6666 0.0.0.0:* LISTEN 28474/gtm
tcp 0 0 192.168.100.106:35905 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:35906 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:35907 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.110:35286 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:35909 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.109:43396 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.108:35748 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.110:35285 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.108:35747 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:35904 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:6666 192.168.100.109:43395 ESTABLISHED 28474/gtm
tcp 0 0 192.168.100.106:35908 192.168.100.107:6666 ESTABLISHED 28474/gtm
tcp 0 0 :::6666 :::* LISTEN 28474/gtm
-bash-4.1$
13. 在所有coordinate节点的库里面创建datanode, 需要在每个coordinator节点操作,
因为coordinator上创建node以及group的操作不会在所有的coordinator中同步, 需要手工操作.
------以下引用德哥的总结：
# 同时不建议使用IP地址, 建议使用主机名.
# datanode可以设置primary和prefered.
# primary表示replicated table的dml操作发生在该节点, 因此所有coordinator节点必须统一配置同一个primary.
# 只能有一个datanode的primary=true. 其他都是false.
# prefered表示replicated table的读操作优先选择该节点. 因此每个coordinator配置为离它最近的datanode节点即可.
# 当本地添加了远程所有coordinator节点后,
# 执行DDL, 会在所有的coordinator节点执行create table t1 (id int primary key, info text) distribute by hash(id) to group gp0;生成元数据.
# 注意这个表未创建成功, 是因为其他coordinator节点未定义gp0这个组.
# 在其中的一个coordinator定义组时, group的信息不会被同步到其他coordinator, 包括datanode的定义也是如此, 可以认为group,datanode是coordinator节点本地的信息.
# 为了确保pgxc集群元数据定义一致, 建议每个coordinator节点的pgxc_node以及pgxc_group一致.
# 并且调整了pgxc_node信息后记得调用pgxc_pool_reload重载node连接.
###在所有coordinator创建datanode和其余的coordinator：
192.168.100.108:
alter node coordinator_1 with (host='127.0.0.1',port=6435);
create node coordinator_2 with (type=coordinator, host='192.168.100.109', port=6435);
create node coordinator_3 with (type=coordinator, host='192.168.100.110', port=6435);
create node datanode_1 with (type=datanode, host='192.168.100.108', port=6434, primary=true, preferred=true);
create node datanode_2 with (type=datanode, host='192.168.100.109', port=6434, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.100.110', port=6434, primary=false, preferred=false);
select pgxc_pool_reload();
-bash-4.1$ psql -p 6435
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
---------------+-----------+-----------+-----------+----------------+------------------+-----------
coordinator_1 | C | 5432 | localhost | f | f | 459515430
(1 row)
postgres=# alter node coordinator_1 with (host='127.0.0.1',port=6435);
ALTER NODE
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
---------------+-----------+-----------+-----------+----------------+------------------+-----------
coordinator_1 | C | 6435 | 127.0.0.1 | f | f | 459515430
(1 row)
postgres=# create node datanode_1 with (type=datanode, host='192.168.100.108', port=6434, primary=true, preferred=true);
CREATE NODE
postgres=# create node datanode_2 with (type=datanode, host='192.168.100.109', port=6434, primary=false, preferred=false);
CREATE NODE
postgres=# create node datanode_3 with (type=datanode, host='192.168.100.110', port=6434, primary=false, preferred=false);
CREATE NODE
postgres=#
postgres=# create node coordinator_2 with (type=coordinator, host='192.168.100.109', port=6435);
CREATE NODE
postgres=# create node coordinator_3 with (type=coordinator, host='192.168.100.110', port=6435);
CREATE NODE
postgres=#
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
---------------+-----------+-----------+---------------+----------------+------------------+-------------
coordinator_1 | C | 6435 | 127.0.0.1 | f | f | 459515430
datanode_1 | D | 6434 | 192.168.100.108 | t | t | -675012441
datanode_2 | D | 6434 | 192.168.100.109 | f | f | -1047623914
datanode_3 | D | 6434 | 192.168.100.110 | f | f | 1787525382
coordinator_2 | C | 6435 | 192.168.100.109 | f | f | -555475300
coordinator_3 | C | 6435 | 192.168.100.110 | f | f | -1616098144
(6 rows)
postgres=#
postgres=# select pgxc_pool_reload();
pgxc_pool_reload
------------------
t
(1 row)
192.168.100.109:
alter node coordinator_2 with (host='127.0.0.1',port=6435);
create node coordinator_1 with (type=coordinator, host='192.168.100.108', port=6435);
create node coordinator_3 with (type=coordinator, host='192.168.100.110', port=6435);
create node datanode_1 with (type=datanode, host='192.168.100.108', port=6434, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.100.109', port=6434, primary=false, preferred=true);
create node datanode_3 with (type=datanode, host='192.168.100.110', port=6434, primary=false, preferred=false);
select pgxc_pool_reload();
192.168.100.110:
alter node coordinator_3 with (host='127.0.0.1',port=6435);
create node coordinator_1 with (type=coordinator, host='192.168.100.108', port=6435);
create node coordinator_2 with (type=coordinator, host='192.168.100.109', port=6435);
create node datanode_1 with (type=datanode, host='192.168.100.108', port=6434, primary=true, preferred=false);
create node datanode_2 with (type=datanode, host='192.168.100.109', port=6434, primary=false, preferred=false);
create node datanode_3 with (type=datanode, host='192.168.100.110', port=6434, primary=false, preferred=true);
select pgxc_pool_reload();
14.在所有节点的coordinator库里面创建node group(192.168.100.108 - 192.168.100.110)：
postgres=# create node group gp0 with datanode_1, datanode_2, datanode_3;
CREATE NODE GROUP
postgres=# select * from pgxc_group;
group_name | group_members
------------+-------------------
gp0 | 16384 16385 16386
(1 row)
15.在任意一个coordinator节点创建distribute分布方式的sharding表
postgres=# create table t1 (id int primary key, info text) distribute by hash(id) to group gp0;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
postgres=#
# 该coordinator节点上定义的pgxc_node中的coordinator节点将自动创建该表的元数据.
# 该coordinator节点上定义的group gp0中的datanode节点将创建该表.
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/12804/16384
(1 row)
-bash-4.1$ ls *16384*
ls: cannot access *16384*: No such file or directory
-bash-4.1$ pwd
/opt/postgres/coordinator/base/12804
-bash-4.1$ cd ../../../pgdata/base/
-bash-4.1$ ls
1 12796 12804
-bash-4.1$ cd 12804
-bash-4.1$ ls *16384*
16384
-bash-4.1$ ls -l *16384*
-rw------- 1 pgxc pgxc 0 Aug 12 14:01 16384
-bash-4.1$ pwd
/opt/postgres/pgdata/base/12804
-bash-4.1$
#在任意一个coordinator上面执行的dml，会按照分片规则将修改分布到所有数据节点上。
#在任意一个datanode上面执行的dml，只会对本datanode库做修改。
#数据表都存在datanode上面， coordinator看到的表相当于各个datanode节点的表union后的一个统一视图
---不指定distribute默认创建出来的表，是hash方式的数据分片：
-bash-4.1$ psql -p 6435
postgres=# create table test_1 (id integer,name varchar(32));
CREATE TABLE
postgres=# insert into test_1 select generate_series(1,100),'test_xc';
INSERT 0 100
postgres=# \q
-bash-4.1$ psql -p 6434
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
public | test_1 | table | postgres
(3 rows)
postgres=# select count(*) from test_1;
count
-------
37
(1 row)
postgres=#
-bash-4.1$ psql -p 6434
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# select count(*) from test_1;
count
-------
31
(1 row)
postgres=#
-bash-4.1$ psql -p 6434
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# select count(*) from test_1;
count
-------
32
(1 row)
postgres=#
#备注：表共有100条记录，数据节点一分布了 37 条，数据节点二分布了 31 条，数据节点二分布了 32 条，可见数据已经分片到数据节点上。
# 当然在创建表时，也可以设置 replication 模式，这样数据就会完全复制到每个节点。
16 . 创建replication复制方式的表：
create table test_2(id int primary key , note text) distribute by replication to group gp0;
postgres=# \d+ test_2
Table "public.test_2"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
id | integer | not null | plain |
note | text | | extended |
Indexes:
"test_2_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
postgres=# insert into test_2 values (1,'aaa');
INSERT 0 1
postgres=# insert into test_2 values (2,'bbb');
INSERT 0 1
postgres=# insert into test_2 values (3,'ccc');
INSERT 0 1
postgres=# insert into test_2 values (4,'ddd');
INSERT 0 1
postgres=# insert into test_2 values (5,'eee');
INSERT 0 1
postgres=# select * from test_2;
id | note
----+------
1 | aaa
2 | bbb
3 | ccc
4 | ddd
5 | eee
(5 rows)
postgres=# \q
postgres=# EXECUTE DIRECT ON datanode_1 'SELECT * FROM TEST_2';
id | note
----+------
1 | aaa
2 | bbb
3 | ccc
4 | ddd
5 | eee
(5 rows)
postgres=#
17. 创建取模方法分布的表：
create table test_3(id int primary key, note text) distribute by modulo(id) to group gp0;
postgres=# insert into test_3 values (1,'aaa');
INSERT 0 1
postgres=# insert into test_3 values (2,'bbb');
INSERT 0 1
postgres=# insert into test_3 values (3,'ccc');
INSERT 0 1
postgres=# insert into test_3 values (4,'ddd');
INSERT 0 1
postgres=# insert into test_3 values (5,'eee');
INSERT 0 1
postgres=# insert into test_3 values (6,'fff');
INSERT 0 1
postgres=#
postgres=#
postgres=# select * from test_3;
id | note
----+------
1 | aaa
4 | ddd
2 | bbb
5 | eee
3 | ccc
6 | fff
(6 rows)
postgres=# execute direct on datanode_1 'select * from test_3';
id | note
----+------
3 | ccc
6 | fff
(2 rows)
postgres=# execute direct on datanode_2 'select * from test_3';
id | note
----+------
1 | aaa
4 | ddd
(2 rows)
postgres=# execute direct on datanode_3 'select * from test_3';
id | note
----+------
2 | bbb
5 | eee
(2 rows)
postgres=#
18. 创建roundrobin方法分布数据的表：
create table test_4(id int,note text) distribute by roundrobin(id) to group gp0;
insert into test_4 values (1,'aaa');
insert into test_4 values (2,'bbb');
insert into test_4 values (3,'ccc');
insert into test_4 values (4,'ddd');
insert into test_4 values (5,'eee');
insert into test_4 values (6,'fff');
insert into test_4 values (7,'fff');
可以看到数据是按照insert的顺序依次插入底层的各个节点中的。
19. 重启所有组件 :
[stop顺序]
1. coordinator (如果配置了流复制, 那么先停1.primary, 再停2.standby)
(192.168.100.108 - 192.168.100.110)
pg_ctl -Z coordinator stop -m fast -D /opt/postgres/coordinator
# 确认所有coordinator节点已关闭
2. datanode (如果配置了流复制, 那么先停1.primary, 再停2.standby)
(192.168.100.108 - 192.168.100.110)
pg_ctl -Z datanode stop -m fast -D /opt/postgres/pgdata
# 确认所有datanode节点已关闭
3. gtm_proxy
(192.168.100.108 - 192.168.100.110)
gtm_ctl -Z gtm_proxy stop -m fast -D /opt/postgres/gtm_proxy
# 确认所有gtm_proxy节点已关闭
4. gtm
(192.168.100.106)
gtm_ctl -Z gtm stop -m fast -D /opt/postgres/gtm
# 确认gtm节点已关闭
5. gtm_standby
(192.168.100.107)
gtm_ctl -Z gtm stop -m fast -D /opt/postgres/gtm_standby
# 确认gtm_standby节点已关闭
[startup顺序]
1. gtm
(192.168.100.106)
gtm_ctl -Z gtm start -D /opt/postgres/gtm
# 确认gtm节点已开启
2. gtm_standby
(192.168.100.107)
gtm_ctl -Z gtm start -D /opt/postgres/gtm_standby
# 确认gtm_standby节点已开启
3. gtm_proxy
(192.168.100.108 - 192.168.100.110)
gtm_ctl -Z gtm_proxy start -D /opt/postgres/gtm_proxy
# 确认所有gtm_proxy节点已开启
4. datanode (如果配置了流复制, 那么先启动1.primary, 再启动2.standby)
(192.168.100.108 - 192.168.100.110)
pg_ctl -Z datanode start -D /opt/postgres/pgdata
# 确认所有datanode节点已开启
5. coordinator (如果配置了流复制, 那么先启动1.primary, 再启动2.standby)
(192.168.100.108 - 192.168.100.110)
pg_ctl -Z coordinator start -D /opt/postgres/coordinator
# 确认所有coordinator节点已开启
检查各组件状态：
gtm_ctl -Z gtm status -D /opt/postgres/gtm
gtm_ctl -Z gtm status -D /opt/postgres/gtm_standby
gtm_ctl -Z gtm_proxy status -D /opt/postgres/gtm_proxy
pg_ctl -Z datanode status -D /opt/postgres/pgdata
pg_ctl -Z coordinator status -D /opt/postgres/coordinator