pgxc-test

http://michael.otacoo.com/tag/pgxc/page/4/

 

About

Postgres-XC is an open source project to provide a write-scalable, synchronous multi-master, transparent PostgreSQL cluster solution.

Description

Postgres-XC is an open source project to provide a write-scalable, synchronous multi-master, transparent PostgreSQL cluster solution. It is a collection if tightly coupled database components which can be installed in more than one hardware or virtual machines.

Write-scalable means Postgres-XC can be configured with as many database servers as you want and handle many more writes (updating SQL statements) compared to what a single database server can not do.

Multi-master means you can have more than one database server that clients connect to which provide a single, consistent cluster-wide view of the database.

Synchronous means any database update from any database server is immediately visible to any other transactions running on different masters.

Transparent means you (and your applications) do not have to worry about how your data is stored in more than one database servers internally.

You can configure Postgres-XC to run on multiple servers. Your data is stored in a distributed way, that is, partitioned or replicated, as chosen by you for each table. When you issue queries, Postgres-XC determines where the target data is stored and issues corresponding queries to servers containing the target data.

 

 

alter node coordinate_1with(host='127.0.0.1',port=1921);

 

13. 创建node,需要在每个coordinator节点操作,因为coordinator上创建node以及group的操作不会在所有的coordinator中同步,需要手工操作.

# 同时不建议使用IP地址,建议使用主机名. 下一篇中会对本例的IP地址修改为主机名举例.

# datanode可以设置primary和prefered.

# primary表示replicated table的dml操作发生在该节点,因此所有coordinator节点必须统一配置同一个primary.

# 只能有一个datanode的primary=true.其他都是false.

# prefered表示replicated table的读操作优先选择该节点.因此每个coordinator配置为离它最近的节点即可.

192.168.122.173 : 

 

 

alter node coordinate_1with(host='127.0.0.1',port=1921);

create node datanode_1with(type=datanode, host='192.168.122.173', port=1923, primary=true, preferred=true);

create node datanode_2with(type=datanode, host='192.168.122.174', port=1923, primary=false, preferred=false);

create node datanode_3with(type=datanode, host='192.168.122.175', port=1923, primary=false, preferred=false);

create node datanode_4with(type=datanode, host='192.168.122.176', port=1923, primary=false, preferred=false);

create node datanode_5with(type=datanode, host='192.168.122.177', port=1923, primary=false, preferred=false);

create node datanode_6with(type=datanode, host='192.168.122.178', port=1923, primary=false, preferred=false);

gtm 为分配GXID和管理PGXC MVCC的模块,在一个CLUSTER中只能有一台主的gtm, gtm也将成为整个CLUSTER的瓶颈已经单点故障点.

gtm_standby 为gtm的备机.

 

 

OLTP：基于磁盘的写优化数据库系统，具有良好的并发性能，完整的事物特性，高效的资源共享，采用索引技术，快速定位需要访问的数据，适合在线事务处理应用。

 

OLAP应用时针对大量的数据，进行复杂的查询分析，试图在海量的数据里找出规律性的线索，为决策提供依据。OLAP应用不是面向日常的业务处理，而是把大量的数据进行必要整理后，进行统计分析；因此这些因素决定了OLAP应用具有以下的不同特性：并发度要求不高；复杂的只读查询；针对静态大规模数据进行分析统计；涉及数据量大，大量的全表扫描；通常只涉及记录的部分字段等。

 

TPCH就是典型的OLAP应用。

 

create table c distribute by round robin to node dn1,dn2 as select * from b;

 

execute direct on node dn4 'select count(*) from c';

 

However, SELECT INTO does not have any extension for distribution type and node subsets. The reason for that is because SELECT INTO is by default a SELECT query, CREATE TABLE AS is a DDL. So in this case table created is distributed by hash on all the nodes.
postgres=# select * into d from b;
INSERT 0 100
postgres=# select pclocatortype,nodeoids from pgxc_class where pcrelid = 'd'::regclass;
-[ RECORD 1 ]-+------------------------
pclocatortype | H
nodeoids | 16384 16385 16386 16387

select pgxc_pool_reload();

This optimization avoids to have to restart a Coordinator when changing
cluster configuration and solves security problems related to cluster_nodes.sql

 

 

建议每个coordinator配置一个gtm_proxy,并将gtm_proxy和coordinator放在一台主机上

 

 

 create barrier .这个对于一个分布式数据库来说可以说是非常重要的,全局一致的备份和还原对于企业级应用来说是特别需要考虑的.相信PGXC未来会得到企业的认可.

 

 

. Postgres-XC中聚合函数类似mapreduce,需要多节点的汇聚,甚至可以做多次的mapreduce.未来Postgres-XC是否能在数据仓库领域大有可为至少聚合函数这块要有比较便利的开发框架才行.

 

试下1个coordinator不添加其他coordinator节点查询情况。

 

 

 

Postgres-XC （eXtensible Cluster）是一个提供写可扩展、同步、对称的和透明的 PostgreSQL群集解决方案的开放源码项目。它是紧耦合的数据库组件，可以安装在多个硬件或虚拟机的集合。

相较于一台数据库服务器，写可扩展性意味着 Postgres-XC可以配置与尽可能多的数据库服务器，以及你想要处理很多更多写入（更新 SQL语句）

对称意味着可以有更多客户端连接到其中的一个数据库服务器提供单一、一致的群集全视图的数据库。

从任何数据库服务器的任何数据库更新的同步手段是立即可见的在不同的主机上运行的任何其他交易。

透明的手段，您（和您的应用程序）不必担心您的数据如何存储在多个数据库服务器内部。

您可以配置 Postgres-XC在多个服务器上运行。您的数据是存储在分布式方式、就是分区或复制，如为每个表由您选择。当你发出查询时，Postgres-XC确定目标数据存储在哪里，到包含目标数据的服务器的问题相应的查询。

 

 

总体介绍

Postgres-XC 是一种提供写可靠性，多主节点数据同步，数据传输的开源集群方案。它包含很多组件，主要包括GTM节点，Coordinator节点，Datanode节点，GTM Proxy, GTM standby这些Postgres-XC组件可以分别安装在多台物理机器或虚拟机上。

 

多主节点 (Multi-master )：是指多个Coordinator节点都可以提供统一个完整一致的数据库视图；主节点数据同步（Synchronous):是指在一台数据库上更新操作会立刻呈现在另一台数据库中；数据传输（Transparent）是指；数据位于不同的数据库节点中，当查询数据时，不必关心数据位于具体的节点。

一个集群中包含一个GTM节点，一个或多个Coordinator节点，一个或多个Datanode节点。

    Postgres-XC 数据以分布式存储，有两种方式， 即 Replication 或distribution。

repliation分布模式：各个datanode节点拥有该table的全部数据。

distribution分布模式：

（1）    hash(xx)

（2）    roundrobin

（3）    moduo(xx)

（4）    xxx

 

各节点作用介绍

 GTM

GTM是Postgres-XC的核心组件，用于全局事务控制以及tuple的可见性控制。gtm为分配GXID和管理PGXC MVCC的模块,在一个CLUSTER中只能有一台主的gtm。PostgreSQL的事务控制是基于 MVCC 机制的，在 PostgreSQL-XC体系中将这种技术单独划分出来，
称之为 GTM。

gtm_standby

gtm_standby 为gtm的备机.

gtm_proxy

gtm_proxy为降低gtm压力而诞生的,用于对coordinator节点提交的任务进行分组等操作.机器中可以存在多个gtm_proxy.。

Coordinator

协调节点 (Coordinator)是数据节点 (Datanode)  与应用之间的接口，Coordinator节点并不物理上存储表数据，表数据以分片或者复制的方式分布式存储，表数据存储在数据节点上。当应用发起SQL时，会先到达 Coordinator节点，然后 Coordinator 节点将 SQL分发到各个数据节点，汇总数据，这一系统过程是通过  GXID和 Global Snapshot  来控制的。

Datanode

数据节点（datanode）物理上存储表数据，表数据存储方式分为分片（distributed）和完全复制（replicated）两种。数据节点只存储本地的数据

 

 

 

 

 

 

 

支持postgres-xc 的企业

NTT Open Source Software Center (Japanese)

EnterpriseDB

StormDB

 

 

版本发布增加特性情况

1.0

1.0 beta1

The following enhancements have been made since release of1.0beta1:
- Redaction of release notes, summarizing all the features added in
Postgres-XC since the creation of the project
- Support for make world
- Regressions stabilized (no failures for 139 tests)
- Fix of more than 50 bugs.
- Merge with stable branch of PostgreSQL 9.1 (600~ commits).

Compared to version Postgres-XC 0.9.7, the following features have been
added:
- Fast query shipping (FQS), quick identification of expressions in a query
that can be pushed down to remote nodes
- SERIAL types
- TABLESPACE
- Utility to clean up 2PC transactions in cluster (pgxc_clean)
- Utility for initialization of GTM (global transaction manager, utility
called initgtm)
- Relation-size functions
- Regression stabilization

 

1.0 beta2

30 bug fixes have been made since release ofbeta2, with some notable
enhancements:
- Support for EXTENSION is fixed
- Stabilization of the use of slave nodes in cluster
- Fix of a bug related to read-only transactions, improving performance by
15%.
- Support of compilation for MacOSX
About the scalability of this release, Postgres-XC 1.0.0 scales to a factor
of 3 when compared to a standalone server PostgreSQL 9.1.3 on 5 nodes using
a benchmark called DBT-1.

Compared to version Postgres-XC 0.9.7, the following features have been
added:
- Fast query shipping (FQS), quick identification of expressions in a query
that can be pushed down to remote nodes
- SERIAL types
- TABLESPACE
- Utility to clean up 2PC transactions in cluster (pgxc_clean)
- Utility for initialization of GTM (global transaction manager, utility
called initgtm)
- Relation-size functions and locking functions
- Regression stabilization

 

 

1.0.1

The project members are currently working hard on the next version of
Postgres-XC that will include those features:
- triggers (being implemented)
- Merge with PostgreSQL 9.2 code (already committed)
- RETURNING, WHERE CURRENT OF (being implemented)
- Insure consistency of utilities that cannot run inside transaction block
(ex: CREATE DATABASE safely insured in multiple nodes, being implemented)
- Change table distribution type with ALTER TABLE (already committed)
- Support for cursors (already committed)
- Stuff related to node addition and deletion
- and other things...

 

1.0.2

Upgrading base PostgreSQL version to 9.2 will be included in the next
major release.

----
Postgres-XC is a symmetric PostgreSQL cluster which provides both read
and write scalability using mixture of table sharding and replication.

Each Postgres-XC cluster node provides single database view, where
application can connect to any cluster node and run any transactions.
Result of transactions are visible from all the cluster nodes without
delay.

 

 

ftp://mirror.transact.net.au/sourceforge/p/po/postgres-xc/Version_0.9.5/PG-XC_InstallManual_v0_9_5.pdf

 

 

Postgres-XC currently runs on the Linux Operating Systems running on 64-bit Intel(R) processors.

The development team tested this with Cent-OS 5.3 and 5.4.

You might be able to build the data nodes and possibly the coordinators on Windows, but the GTM will take major work to port to Windows.

删除一个节点上表

int remove_datanodeMaster(char *name, int clean_opt)

datanode_cmd.c

ALTER TABLE rr_abc DELETE NODE (DATA_NODE_3);

 

 

SELECT pgxc_pool_reload();

 

Update the connection information cached in pool.

SELECT pgxc_pool_reload();

 

 

This directory contains pgxc_ddl, an application used to make a cold synchronization of DDL

in a Postgres-XC cluster by launching DDL and then copy Coordinator catalog file

data from a remote Coordinator (where DDL has been launched) to other Coordinators.

 

extern int pgxc_node_send_gxid(PGXCNodeHandle * handle, GlobalTransactionId gxid);

extern int pgxc_node_send_cmd_id(PGXCNodeHandle *handle, CommandId cid);

extern int pgxc_node_send_snapshot(PGXCNodeHandle * handle, Snapshot snapshot);

extern int pgxc_node_send_timestamp(PGXCNodeHandle * handle, TimestampTz timestamp);

 

 

 

pgxc安装GUI

https://github.com/xctools/postgres-xc-installer