1.Download, untar, make, install
$groupadd pgxc
$useradd pgxc -g pgxc
$passwd pgxc
$mkdir /pgdata_xc
$chown -R pgxc:pgxc /pgdata_xc
$tar -zxvf pgxc-v1.0.3.tar.gz
$cd postgres-xc/
$yum install gcc flex perl readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxml2 libxslt libxslt-devel openldap openldap-devel python-devel bison
$./configure --prefix=/opt/pgsql_xc --with-segsize=8 --with-wal-segsize=64 --with-wal-blocksize=64 --with-perl --with-python --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety
$gmake
$gmake install
Global setting (edit ~/.bash_profile)
-------------------------------------
(Optional)
export PGPORT=XXXX
export PGDATA=/pgdata_xc/XXXXXX
export LANG=en_US.utf8
(Conv)
export PGHOME=/opt/pgsql_xc
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
--------------------------------------
2.Config Data Node
### data node 1 ###
$mkdir -p /pgdata_xc/db_data
$initdb -D /pgdata_xc/db_data --nodename db_data_1 -E UTF8 -U postgres -W
modify postgresql.conf
-----------------------
port = 15431
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
gtm_host = '192.168.1.53'
gtm_port = 6666
#pgxc_node_name = 'db_data_1'
pooler_port = 6667
max_pool_size = 100
-----------------------
modify pg_hba.conf
-----------------------
host all all 192.168.38.0/24 trust
or
host all all gtm_server_ip/32 trust
-----------------------
### data node 2 ###
$mkdir -p /pgdata_xc/db_data <- data="" node="" p="" second="">$initdb -D /pgdata_xc/db_data --nodename db_data_2 -E UTF8 -U postgres -W
modify postgresql.conf
-----------------------
port = 15432
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
gtm_host = '192.168.1.53'
gtm_port = 6666
#pgxc_node_name = 'db_data_2'
pooler_port = 6667
max_pool_size = 100
-----------------------
modify pg_hba.conf
-----------------------
host all all 192.168.38.0/24 trust
or
host all all gtm_server_ip/32 trust
-----------------------
3.Config GTM
$mkdir -p /pgdata_xc/gtm
$initgtm -Z gtm -D /pgdata_xc/gtm
4.Config Coordinator Node
### coordinator node 1 ###
$mkdir -p /pgdata_xc/coord1
$initdb -D /pgdata_xc/coord1 --nodename coord1 -E UTF8 -U postgres -W
modify postgresql.conf
-----------------------
listen_addresses = '*'
port = 1921
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
gtm_host = '192.168.38.53'
gtm_port = 6666
#pgxc_node_name = 'coord1'
pooler_port = 6667
max_pool_size = 100
-----------------------
modify pg_hba.conf
-----------------------
host all all 192.168.38.0/24 trust
or
host all all data_node_ip/32 trust
-----------------------
### coordinator node 2 ###
$mkdir -p /pgdata_xc/coord2
$initdb -D /pgdata_xc/coord2 --nodename coord2 -E UTF8 -U postgres -W
modify postgresql.conf
-----------------------
listen_addresses = '*'
port = 1921
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
gtm_host = '192.168.38.53'
gtm_port = 6666
#pgxc_node_name = 'coord2'
pooler_port = 6667
max_pool_size = 100
-----------------------
modify pg_hba.conf
-----------------------
host all all 192.168.38.0/24 trust
or
host all all data_node_ip/32 trust
-----------------------
5.Startup (su - pgxc)
a)GTM
$gtm -D /pgdata_xc/gtm &
b)Data node
$postgres -X -D /pgdata_xc/db_data -p 15431 -i & <- data="" first="" node="" p="">
$postgres -X -D /pgdata_xc/db_data -p 15431 -i & <- data="" node="" p="" second="">c)Coordinator node
$postgres -C -D /pgdata_xc/coord2 -p 1921 -i &
$postgres -C -D /pgdata_xc/coord1 -p 1921 -i &
d) stop all service
#pg_ctl stop -D /pgdata_xc/coord2 -p 1921 (node2)
#pg_ctl stop -D /pgdata_xc/coord1 -p 1921 (node1)
#pg_ctl stop -D /pgdata_xc/db_data -p 15431 (node1)
#pg_ctl stop -D /pgdata_xc/db_data -p 15431 (node2)
#pg_ctl stop -D /pgdata_xc/db_data_slave -p 15432 (node1)
#pg_ctl stop -D /pgdata_xc/db_data_slave -p 15432 (node2)
#gtm_ctl stop -Z gtm -D /pgdata_xc/gtm (gtm server)
6.check status
a)GTM
[pgxc@pgxc-1 ~]$ gtm_ctl status -Z gtm -D /pgdata_xc/gtm
gtm_ctl: server is running (PID: 1551)
"-D" "/pgdata_xc/gtm"
1 master
b)Data node and Coordinator node
[pgxc@pgxc-1 ~]$ ps -ef | grep pgxc
pgxc 1551 1526 0 11:36 pts/0 00:00:00 gtm -D /pgdata_xc/gtm <-gtm p="">
pgxc 1561 1526 0 11:38 pts/0 00:00:00 postgres -X -D /pgdata_xc/db_1/pg_root -p 15431 -i <-datanode nbsp="" p="">
pgxc 1569 1526 0 11:39 pts/0 00:00:00 postgres -C -D /pgdata_xc/coord_1 -p 1921 -i <-coordinatornode p="">
pgxc 1572 1569 0 11:39 ? 00:00:00 postgres: pooler process <-pooler atanode="" oordinator="" p="" process="">
7.在CoordinatorNode上註冊DataNode
### coordinator node 1 ###
#psql -p 1921 -U postgres
postgres=# CREATE NODE db_data_1 WITH (TYPE='datanode',HOST = '192.168.38.53', PORT=15431);
postgres=# CREATE NODE db_data_2 WITH (TYPE='datanode',HOST = '192.168.38.54', PORT=15431);
postgres=# CREATE NODE coord2 WITH (TYPE='coordinator',HOST = '192.168.38.54', PORT=1921);
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+---------------+----------------+------------------+-------------
coord1 | C | 5432 | localhost | f | f | 1885696643
db_data_1 | D | 15431 | 192.168.38.53 | t | t | 1356996994
db_data_2 | D | 15431 | 192.168.38.54 | t | t | -822936791
coord2 | C | 1921 | 192.168.38.54 | f | f | -1197102633
#SELECT pgxc_pool_reload(); (if alter node, execute it)
### coordinator node 2 ###
#psql -p 1921 -U postgres
postgres=# CREATE NODE db_data_1 WITH (TYPE='datanode',HOST = '192.168.38.53', PORT=15431);
postgres=# CREATE NODE db_data_2 WITH (TYPE='datanode',HOST = '192.168.38.54', PORT=15431);
postgres=# CREATE NODE coord1 WITH (TYPE='coordinator',HOST = '192.168.38.53', PORT=1921);
postgres=# select * from pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+---------------+----------------+------------------+-------------
coord2 | C | 5432 | localhost | f | f | -1197102633
db_data_1 | D | 15431 | 192.168.38.53 | t | t | 1356996994
db_data_2 | D | 15431 | 192.168.38.54 | t | t | -822936791
coord1 | C | 1921 | 192.168.38.53 | f | f | 1885696643
#SELECT pgxc_pool_reload(); (if alter node, execute it)
8.Test (在Coordinator1做的create database, table and insert data，會在Coordinator2看到一樣的結果,反之亦然)
### Coordinator node 1 ###
$psql -p 1921 -U postgres
postgres=# \l
postgres=# create database test_xc;
postgres=# \l
postgres=# \c test_xc
test_xc=# create table test_1 (id integer,name varchar(32));
test_xc=# insert into test_1 select generate_series(1,100),'test_xc';
test_xc=# select count(*) from test_1;
count
-------
402
(1 row)
### Coordinator node 2 ###
$psql -p 1921 -U postgres
postgres=# \l
postgres=# \c test_xc
test_xc=# \d
test_xc=# select count(*) from test_1;
count
-------
402
(1 row)
### Data node 1 ### (可以看到每個Data node的筆數不同，表示他會平均分散資料存放，可減少IO增進Query performance)
$psql -p 15431 -U postgres -d test_xc
test_xc=# \d
test_xc=# select count(*) from test_1;
count
-------
204
(1 row)
### Data node 2 ###
$psql -p 15431 -U postgres -d test_xc
test_xc=# \d
test_xc=# select count(*) from test_1;
count
-------
198
(1 row)
