postgres-xc

最新推荐文章于 2023-05-30 21:26:47 发布

转载最新推荐文章于 2023-05-30 21:26:47 发布 · 4.2k 阅读

postgresql 专栏收录该内容

33 篇文章

订阅专栏

Postgres-XC:

http://michael.otacoo.com/postgresql-2/postgres-xc-online-data-redistribution/ （在线帮助）

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共享的一种良好的解决方案。

下载：

http://sourceforge.net/projects/postgres-xc/

架构图：

组件：

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

如果报错configure: error: could not determine flags for linking embedded Perl则安装

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

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：

添加下面两行

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

[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;