Postgres-XC:
http://blog.youkuaiyun.com/huguangshanse00/article/details/9636621
http://blog.163.com/digoal@126/blog/static/1638770402013332335933/
http://blog.163.com/digoal@126/blog/static/163877040201331001855771/
http://blog.163.com/digoal@126/blog/static/16387704020133108138729/
http://francs3.blog.163.com/blog/static/4057672720125315352442/
http://francs3.blog.163.com/blog/static/405767272012532308480/
pg-xc描述:
是对于多数据节点同时DML/DDL共享的一种良好的解决方案。
下载:
架构图:
组件:
gtm:
利用pg的MVCC全局地控制tuple,一个pgxc中只能有一个gtm。
gtm_standby:
gtm的备机。
gtm_proxy:
降低gtm压力,用多个代理来进行对coordinator进行操作。
coordinator:
协调节点,负责操作所有datanode,本身不存放数据。在coordinator上可以以distribute切片(分布)或者replication复制的方式进行创建表。
datanode:
存放数据的节点,如果数据在coordinator上是以切片方式建的表则数据只存放此表的一部分数据,如果是replication方式的表则存放全部数据。
演示:
1:解压
tar -zxvf pgxc-v1.0.4.tar.gz
2:配置
cd 到解压后的目录下:
[pgxc@localhost postgres-xc-1.0.4]$ pwd
/usr/local/rj/postgres-xc-1.0.4
[pgxc@localhost postgres-xc-1.0.4]$ ll
total 1.4M
-rw-rw-r--. 1 pg93 pg93 385 Apr 4 18:26 aclocal.m4
drwxrwxr-x. 2 pg93 pg93 4.0K Apr 4 18:26 config
-rw-r--r--. 1 root root 339K Jun 5 15:09 config.log
-rwxr-xr-x. 1 root root 38K Jun 5 15:09 config.status
-rwxrwxr-x. 1 pg93 pg93 852K Apr 4 18:26 configure
-rw-rw-r--. 1 pg93 pg93 63K Apr 4 18:26 configure.in
drwxrwxr-x. 53 pg93 pg93 4.0K Apr 4 18:26 contrib
-rw-rw-r--. 1 pg93 pg93 2.6K Apr 4 18:26 COPYRIGHT
drwxrwxr-x. 3 pg93 pg93 4.0K Apr 4 18:26 doc
drwxrwxr-x. 3 pg93 pg93 4.0K Apr 4 18:26 doc-xc
-rw-r--r--. 1 root root 3.6K Jun 5 15:09 GNUmakefile
-rw-rw-r--. 1 pg93 pg93 3.6K Apr 4 18:26 GNUmakefile.in
-rw-rw-r--. 1 pg93 pg93 283 Apr 4 18:26 HISTORY
-rw-rw-r--. 1 pg93 pg93 1.5K Apr 4 18:26 Makefile
-rw-rw-r--. 1 pg93 pg93 1.5K Apr 4 18:26 README
-rw-rw-r--. 1 pg93 pg93 817 Apr 4 18:26 README.git
drwxrwxr-x. 16 pg93 pg93 4.0K Jun 5 15:09 src
[pgxc@localhost postgres-xc-1.0.4]$
./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 |
yum install perl-ExtUtils-Embed
yum这个命令我运行了2遍才行。第1遍的时候包了个错误:
Error Downloading Packages:
perl-Test-Harness-3.17-136.el6.x86_64: failure: Packages/perl-Test-Harness-3.17-136.el6.x86_64.rpm from base: [Errno 256]
No more mirrors to try.
gdbm-devel-1.8.0-36.el6.x86_64: failure: Packages/gdbm-devel-1.8.0-36.el6.x86_64.rpm from base: [Errno 256] No more
mirrors to try.
重新./configure --......
如果依旧报错configure: error: library 'crypto' is required for OpenSSL
则安装:
yum -y install openssl-devel
重新./configure --......
3:编译:
gmake
提示:
All of PostgreSQL successfully made. Ready to install.
4:安装:
gmake install
提示:
PostgreSQL installation complete.
审阅安装目录:
[pgxc@localhost bin]$ cd /opt/pgsql_xc/bin
[pgxc@localhost bin]$ ll
total 8996
-rwxr-xr-x. 1 root root 54796 Jun 5 15:27 clusterdb
-rwxr-xr-x. 1 root root 57925 Jun 5 15:27 createdb
-rwxr-xr-x. 1 root root 58967 Jun 5 15:27 createlang
-rwxr-xr-x. 1 root root 58247 Jun 5 15:27 createuser
-rwxr-xr-x. 1 root root 53591 Jun 5 15:27 dropdb
-rwxr-xr-x. 1 root root 58797 Jun 5 15:27 droplang
-rwxr-xr-x. 1 root root 53593 Jun 5 15:27 dropuser
-rwxr-xr-x. 1 root root 752166 Jun 5 15:27 ecpg
-rwxr-xr-x. 1 root root 267405 Jun 5 15:27 gtm
-rwxr-xr-x. 1 root root 55591 Jun 5 15:27 gtm_ctl
-rwxr-xr-x. 1 root root 215042 Jun 5 15:27 gtm_proxy
-rwxr-xr-x. 1 root root 80215 Jun 5 15:27 initdb
-rwxr-xr-x. 1 root root 35545 Jun 5 15:27 initgtm
-rwxr-xr-x. 1 root root 12071 Jun 5 15:27 makesgml
-rwxr-xr-x. 1 root root 42318 Jun 5 15:27 pg_basebackup
-rwxr-xr-x. 1 root root 27004 Jun 5 15:27 pg_config
-rwxr-xr-x. 1 root root 25598 Jun 5 15:27 pg_controldata
-rwxr-xr-x. 1 root root 40245 Jun 5 15:27 pg_ctl
-rwxr-xr-x. 1 root root 334571 Jun 5 15:27 pg_dump
-rwxr-xr-x. 1 root root 77270 Jun 5 15:27 pg_dumpall
-rwxr-xr-x. 1 root root 34185 Jun 5 15:27 pg_resetxlog
-rwxr-xr-x. 1 root root 144486 Jun 5 15:27 pg_restore
-rwxr-xr-x. 1 root root 6103149 Jun 5 15:27 postgres
lrwxrwxrwx. 1 root root 8 Jun 5 15:27 postmaster -> postgres
-rwxr-xr-x. 1 root root 418571 Jun 5 15:27 psql
-rwxr-xr-x. 1 root root 57964 Jun 5 15:27 reindexdb
-rwxr-xr-x. 1 root root 32763 Jun 5 15:27 vacuumdb
[pgxc@localhost bin]$
由此可见,postgres-xc和postgresql是一个级别的。
5:部署规划:
跟原作者一样没有gtm_standby和gtm_proxy节点。
ip:127.0.0.1
gtm节点:port:15431
coordinator节点1:coord1 1921
coordinator节点1:coord1 1925
ip:127.0.0.1
datanode节点1: db_1 15431
datanode节点2: db_2 15432
gtm节点:port:15431
coordinator节点1:coord1 1921
coordinator节点1:coord1 1925
ip:127.0.0.1
datanode节点1: db_1 15431
datanode节点2: db_2 15432
6:linux新建用户
su - root
useradd pgxc
7:编辑 .bash_profile修改环境变量
su - pgxc
cd
vi .bash_profile:
export PGPORT=15431
export PGDATA=/pgdata_xc/db_1/pg_root
export LANG=en_US.utf8
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'
8:配置datanode节点
生成数据节点目录:
[pgxc@localhost ~]$ su - root
[root@localhost ~]# mkdir -p /pgdata_xc/db_1/pg_root
[root@localhost ~]# mkdir -p /pgdata_xc/db_2/pg_root
赋予操作权限给pgxc用户:
[root@localhost ~]# chown -R pgxc /pgdata_xc
[root@localhost ~]# su - pgxc
[pgxc@localhost ~]$ initdb -D /pgdata_xc/db_1/pg_root --nodename db_1 -E UTF8 --locale=C -U postgres -W
[pgxc@localhost ~]$ initdb -D /pgdata_xc/db_2/pg_root --nodename db_2 -E UTF8 --locale=C -U postgres -W
datanode1的修改:
postgresql.conf:
修改如下几处
# - Connection Settings -
listen_addresses = '*'
port = 15431
log_destination = 'csvlog'
logging_collector = on
# GTM CONNECTION
gtm_host = 'localhost'
gtm_port = 6666
pgxc_node_name = 'db_1'
pg_hba.conf:
添加下面两行
datanode2的修改
postgresql.conf:
pg_hba.conf:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
datanode2的修改
postgresql.conf:
# - Connection Settings -
listen_addresses = '*'
port = 15432
log_destination = 'csvlog'
logging_collector = on
# GTM CONNECTION
gtm_host = 'localhost'
gtm_port = 6666
pgxc_node_name = 'db_2'
pg_hba.conf:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
9:配置gtm
生成数据目录:
[root@localhost ~]# mkdir -p /database/1922/pgdata1/pgdata_xc/gtm
[root@localhost ~]# chown -R pgxc /database
初始化gtm:
[root@localhost ~]# mkdir -p /database/1922/pgdata1/pgdata_xc/gtm
[root@localhost ~]# chown -R pgxc /database
[root@localhost ~]# su - pgxc
[pgxc@localhost ~]$ initgtm -Z gtm -D /database/1922/pgdata1/pgdata_xc/gtm
10:配置coordinator节点
生成协调节点目录:[root@localhost ~]# mkdir -p /database/1922/pgdata1/pgdata_xc/coord1
[root@localhost ~]# mkdir -p /database/1922/pgdata1/pgdata_xc/coord2
[root@localhost ~]# chown -R pgxc /database
初始化协调节点:
[pgxc@localhost ~]$ initdb -D /database/1922/pgdata1/pgdata_xc/coord1 --nodename coord1 -E UTF8 --locale=C -U postgres -W
[pgxc@localhost ~]$ initdb -D /database/1922/pgdata1/pgdata_xc/coord2 --nodename coord2 -E UTF8 --locale=C -U postgres -W
coordinator1的修改:
postgresql.conf:
# - Connection Settings -
listen_addresses = '*'
port = 1921
log_destination = 'csvlog'
logging_collector = on
# GTM CONNECTION
gtm_host = 'localhost'
gtm_port = 6666
pgxc_node_name = 'coord1'
# DATA NODES AND CONNECTION POOLING
pooler_port = 6667
pg_hba.conf:
添加以下两行
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
coordinator2的修改:
postgresql.conf:
# - Connection Settings -
listen_addresses = '*'
port = 1925
log_destination = 'csvlog'
logging_collector = on
# GTM CONNECTION
gtm_host = 'localhost'
gtm_port = 6666
pgxc_node_name = 'coord2'
# DATA NODES AND CONNECTION POOLING
pooler_port = 6668
pg_hba.conf:
添加以下两行
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
11:启动gtm
[pgxc@localhost ~]$ gtm_ctl start -Z gtm -D /database/1922/pgdata1/pgdata_xc/gtm
查看gtm是否已启动:
[pgxc@localhost ~]$ gtm_ctl status -Z gtm -D /database/1922/pgdata1/pgdata_xc/gtm
gtm_ctl: server is running (PID: 16959)
"-D" "/database/1922/pgdata1/pgdata_xc/gtm"
1 master
12:启动datanode
使用postgres命令(在安装完postgres-xc后会在安装目录的bin下生成)。
postgres --help
......
Node options:
-C start as a Coordinator
-X start as a Datanode
-C start as a Coordinator
-X start as a Datanode
[pgxc@localhost ~]$ postgres -X -D /pgdata_xc/db_1/pg_root -p 15431 -i &
[2] 17010
[pgxc@localhost ~]$ postgres -X -D /pgdata_xc/db_2/pg_root -p 15432 -i &
[3] 17017
查看启动是否成功:
[pgxc@localhost ~]$ ps -ef | grep pgxc
root 16426 2270 0 15:29 pts/0 00:00:00 su - pgxc
pgxc 16427 16426 0 15:29 pts/0 00:00:00 -bash
root 16508 16499 0 15:32 pts/1 00:00:00 su - pgxc
pgxc 16509 16508 0 15:32 pts/1 00:00:00 -bash
root 16623 16509 0 16:05 pts/1 00:00:00 su - pgxc
pgxc 16629 16623 0 16:05 pts/1 00:00:00 -bash
pgxc 16959 16629 0 17:14 pts/1 00:00:00 gtm -D /database/1922/pgdata1/pgdata_xc/gtm
pgxc 17010 16629 0 17:36 pts/1 00:00:00 postgres -X -D /pgdata_xc/db_1/pg_root -p 15431 -i
pgxc 17011 17010 0 17:36 ? 00:00:00 postgres: logger process
pgxc 17013 17010 0 17:36 ? 00:00:00 postgres: writer process
pgxc 17014 17010 0 17:36 ? 00:00:00 postgres: wal writer process
pgxc 17015 17010 0 17:36 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17016 17010 0 17:36 ? 00:00:00 postgres: stats collector process
pgxc 17017 16629 1 17:36 pts/1 00:00:00 postgres -X -D /pgdata_xc/db_2/pg_root -p 15432 -i
pgxc 17018 17017 0 17:36 ? 00:00:00 postgres: logger process
pgxc 17020 17017 0 17:36 ? 00:00:00 postgres: writer process
pgxc 17021 17017 0 17:36 ? 00:00:00 postgres: wal writer process
pgxc 17022 17017 0 17:36 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17023 17017 0 17:36 ? 00:00:00 postgres: stats collector process
pgxc 17024 16629 16 17:36 pts/1 00:00:00 ps -ef
pgxc 17025 16629 0 17:36 pts/1 00:00:00 grep pgxc
[pgxc@localhost ~]$
13:启动coordinator节点
<pre name="code" class="sql">[pgxc@localhost ~]$ postgres -C -D /database/1922/pgdata1/pgdata_xc/coord1 -p 1921 -i &
[4] 17026
[pgxc@localhost ~]$ postgres -C -D /database/1922/pgdata1/pgdata_xc/coord2 -p 1925 -i &
[5] 17035
[pgxc@localhost ~]$ LOG: database system was shut down at 2014-06-05 16:59:24 CST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
查看启动是否成功:
[pgxc@localhost ~]$ ps -ef | grep pgxc
root 16426 2270 0 15:29 pts/0 00:00:00 su - pgxc
pgxc 16427 16426 0 15:29 pts/0 00:00:00 -bash
root 16508 16499 0 15:32 pts/1 00:00:00 su - pgxc
pgxc 16509 16508 0 15:32 pts/1 00:00:00 -bash
root 16623 16509 0 16:05 pts/1 00:00:00 su - pgxc
pgxc 16629 16623 0 16:05 pts/1 00:00:00 -bash
pgxc 16959 16629 0 17:14 pts/1 00:00:00 gtm -D /database/1922/pgdata1/pgdata_xc/gtm
pgxc 17010 16629 0 17:36 pts/1 00:00:00 postgres -X -D /pgdata_xc/db_1/pg_root -p 15431 -i
pgxc 17011 17010 0 17:36 ? 00:00:00 postgres: logger process
pgxc 17013 17010 0 17:36 ? 00:00:00 postgres: writer process
pgxc 17014 17010 0 17:36 ? 00:00:00 postgres: wal writer process
pgxc 17015 17010 0 17:36 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17016 17010 0 17:36 ? 00:00:00 postgres: stats collector process
pgxc 17017 16629 0 17:36 pts/1 00:00:00 postgres -X -D /pgdata_xc/db_2/pg_root -p 15432 -i
pgxc 17018 17017 0 17:36 ? 00:00:00 postgres: logger process
pgxc 17020 17017 0 17:36 ? 00:00:00 postgres: writer process
pgxc 17021 17017 0 17:36 ? 00:00:00 postgres: wal writer process
pgxc 17022 17017 0 17:36 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17023 17017 0 17:36 ? 00:00:00 postgres: stats collector process
pgxc 17026 16629 0 17:38 pts/1 00:00:00 postgres -C -D /database/1922/pgdata1/pgdata_xc/coord1 -p 1921 -i
pgxc 17027 17026 0 17:38 ? 00:00:00 postgres: logger process
pgxc 17029 17026 0 17:38 ? 00:00:00 postgres: pooler process
pgxc 17030 17026 0 17:38 ? 00:00:00 postgres: writer process
pgxc 17031 17026 0 17:38 ? 00:00:00 postgres: wal writer process
pgxc 17032 17026 0 17:38 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17033 17026 0 17:38 ? 00:00:00 postgres: stats collector process
pgxc 17035 16629 0 17:38 pts/1 00:00:00 postgres -C -D /database/1922/pgdata1/pgdata_xc/coord2 -p 1925 -i
pgxc 17037 17035 0 17:39 ? 00:00:00 postgres: pooler process
pgxc 17038 17035 0 17:39 ? 00:00:00 postgres: writer process
pgxc 17039 17035 0 17:39 ? 00:00:00 postgres: wal writer process
pgxc 17040 17035 0 17:39 ? 00:00:00 postgres: autovacuum launcher process
pgxc 17041 17035 0 17:39 ? 00:00:00 postgres: stats collector process
pgxc 17048 16629 3 17:40 pts/1 00:00:00 ps -ef
pgxc 17049 16629 0 17:40 pts/1 00:00:00 grep pgxc
[pgxc@localhost ~]$
注意注意其中的 pooler process 进程,用于 数据节点间通信。
查看gtm和pool信息:
[pgxc@localhost ~]$ 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 16959/gtm
tcp 0 0 :::6666 :::* LISTEN 16959/gtm
[pgxc@localhost ~]$ ps -ef | grep pool
pgxc 17029 17026 0 17:38 ? 00:00:00 postgres: pooler process
pgxc 17037 17035 0 17:39 ? 00:00:00 postgres: pooler process
pgxc 17075 16629 0 17:45 pts/1 00:00:00 grep pool
[pgxc@localhost ~]$
14:注册节点
在coord1上注册:psql -h localhost -p 1921 postgres postgres
select * from pgxc_node;
alter node coord1 with (port=1921);
CREATE NODE db_1 WITH (type='datanode',host = 'localhost', PORT=15431);
CREATE NODE db_2 WITH (type='datanode',host = 'localhost', PORT=15432);
CREATE NODE coord2 WITH (type='coordinator',HOST = 'localhost', PORT=1925);
select pgxc_pool_reload();
在coord2上注册:
psql -h localhost -p 1925 postgres postgres
select * from pgxc_node;
alter node coord2 with (port=1925);
CREATE NODE db_1 WITH (type='datanode',host = 'localhost', PORT=15431);
CREATE NODE db_2 WITH (type='datanode',host = 'localhost', PORT=15432);
CREATE NODE coord1 WITH (type='coordinator',HOST = 'localhost', PORT=1921);
select pgxc_pool_reload();
15:测试
在coord1上创建一个数据库,并建立一个新表
psql -h localhost -p 1921 postgres postgres
create database test_xc;
postgres=# \c test_xc
create table test_1 (id integer,name varchar(32));
insert into test_1 select generate_series(1,100),'test_xc';
INSERT 0 100
在coord2上查询是否能够查询到在coord1上新建的库和表
psql -h localhost -p 1925 postgres postgres
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
test_xc | postgres | UTF8 | C | C |
(4 rows)
postgres=# \c test_xc
You are now connected to database "test_xc" as user "postgres".
test_xc=# select count(*) from test_1;
count
-------
100
(1 row)
在db_1上查看:
[pgxc@localhost ~]$ psql -h localhost -p 15431 postgres postgres
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
test_xc | postgres | UTF8 | C | C |
(4 rows)
postgres=# \c test_xc
You are now connected to database "test_xc" as user "postgres".
test_xc=# select count(*) from test_1;
count
-------
42
(1 row)
此处表内row的数量为
42
在db_2上查看:
[pgxc@localhost ~]$ psql -h localhost -p 15432 postgres postgres
psql (PGXC 1.0.4, based on PG 9.1.13)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
test_xc | postgres | UTF8 | C | C |
(4 rows)
postgres=# \c test_xc
You are now connected to database "test_xc" as user "postgres".
test_xc=# select count(*) from test_1;
count
-------
58
(1 row)
此处表内row的数量为
58
经过上面的查看:
1:在db_1上分到了42条数据在db_2上分到了58条数据。(这是因为在建立表的时候默认)
2:在coord1和coord2上分到的数据一样都是100条数据。
create table ***(id int primary key,***) distribute by hash(id) to node datanode_1,datanode_2,datanode_3;
create table ***(id int primary key,***) distribute by modulo(id) to node datanode_1,datanode_2,datanode_3;
create table ***(id int,***) distribute by round robin (id) to node datanode_1,datanode_2,datanode_3;
create table ***(id int primary key,***) distribute by replication (id) to node datanode_1,datanode_2,datanode_3;