PostgreSQL Cluster　

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了PostgreSQL Cluster　，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

/data


=============================================================
3. Configuration
=============================================================
(EX.System Composition)

|
((Load Balance Server))
( hostname: lb.hoo.com)
( receive port:5432 )
( recovery port:6101)
( lifecheck port:6201)
|
----------+-------------+------------+----------
| |
((Cluster DB 1 )) ((Cluster DB 2 ))
( hostname:c1.hoo.com) ( hostname:c2.hoo.com)
( receive port: 5432 ) ( receive port:5432)
( recovery port:7101 ) ( recovery port: 7102 )
( lifecheck port:7201 ) ( lifecheck port: 7202 )
| |
----------+-------------+------------+----------
|
((Replication Server))
( hostname:pgr.hoo.com)
( receive port:8001 )
( recovery port:8101)
( lifecheck port:8201)


3-1. Load Balance Server

The setup file of load balance server is copied from the sample file and edited.
(the sample file is installed '/usr/local/pgsql/etc' in default)
----------------------------------------------------------------
$cd /usr/local/pgsql/etc
$cp pglb.conf.sample pglb.conf
----------------------------------------------------------------

In the case of the above system composition example,
the setup example of pglb.conf file is as the following

#============================================================
# Load Balance Server configuration file
#-------------------------------------------------------------
# file: pglb.conf
#-------------------------------------------------------------
# This file controls:
# o which hosts are db cluster server
# o which portuse connect to db cluster server
# o how many connections are allowed on each DB server
#============================================================
#-------------------------------------------------------------
# set cluster DB server information
# o Host_Name : hostname
# o Port : connection for postmaster
# o Max_Connection : maximun number of connection to postmaster
#-------------------------------------------------------------
<Cluster_Server_Info>;
<Host_Name>; c1.hoo.com</Host_Name>;
<Port>; 5432 </Port>;
<Max_Connect>; 32 </Max_Connect>;
</Cluster_Server_Info>;
<Cluster_Server_Info>;
<Host_Name>; c2.hoo.com</Host_Name>;
<Port>; 5432 </Port>;
<Max_Connect>; 32 </Max_Connect>;
</Cluster_Server_Info>;
#-------------------------------------------------------------
# set Load Balance server information
# o Receive_Port : connection from client
# o Recovery_Port : connection for recovery process
# o Max_Cluster_Num : maximun number of cluster DB servers
# o Use_Connection_Pooling : use connection pool [yes/no]
# o Max_Pool_Each_Server : number of pool connections/DB server
#-------------------------------------------------------------
<Receive_Port>; 5432 </Receive_Port>;
<Recovery_Port>; 6101 </Recovery_Port>;
<LifeCheck_Port>;6201 </LifeCheck_Port>;
<Max_Cluster_Num>; 128 </Max_Cluster_Num>;
<Use_Connection_Pooling>; yes </Use_Connection_Pooling>;
<Max_Pool_Each_Server>; 1 </Max_Pool_Each_Server>;

3-2. Cluster DB Server

The Cluster DB server need edit two configuration files
('pg_hba.conf' and 'cluster.conf').
These files are create under the $PG_DATA directory after 'initdb'.

A. pg_hba.conf
Permission to connect DB via IP connectoins is need for this system.

B. cluster.conf
In the case of the above system composition example,
the setup example of cluster.conf file is as the following

#============================================================
# Cluster DB Server configuration file
#-------------------------------------------------------------
# file: cluster.conf
#-------------------------------------------------------------
# This file controls:
# o which hosts & port are replication server
# o which port use for replication request to replication server
# o which command use for recovery function
#
#============================================================
#-------------------------------------------------------------
# set cluster DB server information
# o Host_Name : hostname
# o Port : connection for postmaster
# o Recovery_Port : connection for recovery process
#-------------------------------------------------------------
<Replicate_Server_Info>;
<Host_Name>; pgr.hoo.com </Host_Name>;
<Port>; 8001 </Port>;
<Recovery_Port>; 8101 </Recovery_Port>;
<LifeCheck_Port>; 8201 </LifeCheck_Port>;
</Replicate_Server_Info>;
#-------------------------------------------------------------
# set Cluster DB Server information
# o Recovery_Port : connection for recovery
# o Rsync_Path : path of rsync command
# o Rsync_Option : file transfer option for rsync
# o When_Stand_Alone : When all replication servers fell,
# you can set up two kinds of permittion,
# "real_only" or "read_write".
#-------------------------------------------------------------
<Recovery_Port>; 7101 </Recovery_Port>;
<LifeCheck_Port>; 7201 </LifeCheck_Port>;
<Rsync_Path>; /usr/bin/rsync </Rsync_Path>;
<Rsync_Option>; ssh -1 </Rsync_Option>;
<When_Stand_Alone>; read_only </When_Stand_Alone>;
<Status_Log_File>;/tmp/cluster.sts </Status_Log_File>;
<Error_Log_File>; /tmp/cluster.log</Error_Log_File>;
#-------------------------------------------------------------
# set partitional replicate control information
# set DB name and Table name to stop reprication
# o DB_Name : DB name
# o Table_Name : table name
#-------------------------------------------------------------
#<Not_Replicate_Info>;
# <DB_Name>; test_db </DB_Name>;
# <Table_Name>;log_table </Table_Name>;
#</Not_Replicate_Info>;

3-3. Replication Server

The setup file of replication server is copied from the sample file and edited.
(the sample file is installed '/usr/local/pgsql/etc' in default)
----------------------------------------------------------------
$cd /usr/local/pgsql/etc
$cp pgreplicate.conf.sample pgreplicate.conf
----------------------------------------------------------------
In the case of the above system composition example,
the setup example of pgreplicate.conf file is as the following

#============================================================
# PGReplicate configuration file
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# This file controls:
# o which hosts & port are cluster server
# o which port use for replication request from cluster server
#============================================================
#-------------------------------------------------------------
# set cluster DB server information
# o Host_Name : hostname
# o Port : connection for postmaster
# o Recovery_Port : connection for recovery
#-------------------------------------------------------------
<Cluster_Server_Info>;
<Host_Name>; c1.hoo.com </Host_Name>;
<Port>; 5432 </Port>;
<Recovery_Port>;7101 </Recovery_Port>;
<LifeCheck_Port>; 7201 </LifeCheck_Port>;
</Cluster_Server_Info>;
<Cluster_Server_Info>;
<Host_Name>; c2.hoo.com </Host_Name>;
<Port>; 5432 </Port>;
<Recovery_Port>;7101 </Recovery_Port>;
<LifeCheck_Port>; 7201 </LifeCheck_Port>;
</Cluster_Server_Info>;
#-------------------------------------------------------------
# set Load Balance server information
# o Host_Name : hostname
# o Recovery_Port : connection for recovery
#-------------------------------------------------------------
<LoadBalance_Server_Info>;
<Host_Name>; lb.hoo.com </Host_Name>;
<Recovery_Port>; 6101 </Recovery_Port>;
<LifeCheck_Port>;6201 </LifeCheck_Port>;
</LoadBalance_Server_Info>;
#-------------------------------------------------------------
# set PGReplicate server information
# o Replicate_Port : connection for reprication
# o Recovery_Port : connection for recovery
#-------------------------------------------------------------
<Replication_Port>; 8001 </Replication_Port>;
<Recovery_Port>; 8101 </Recovery_Port>;
<LifeCheck_Port>; 8201 </LifeCheck_Port>;


=============================================================
4. Start Up / Stop
=============================================================

4-1. replication server

A. Start replication server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc
----------------------------------------------------------------

B. Stop replication server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc stop
----------------------------------------------------------------

usage: pgreplicate [-D path_of_config_file] [-W path_of_work_files] [-U login us
er][-l][-n][-v][-h][stop]
-l: print error logs in the log file.
-n: don't run in daemon mode.
-v: debug mode. need '-n' flag
-h: print this help
stop: stop pgreplicate
(config file default path: ./pgreplicate.conf)

4-2. cluster DB server
$PG_HOME = /usr/local/pgsql
$PG_DATA = /usr/local/pgsql/data

A. Start cluster DB server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -o "-i" start
----------------------------------------------------------------

B. Stop cluster DB server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
----------------------------------------------------------------

4-3. load balance server

A. Start load balance server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc
----------------------------------------------------------------

B. Stop load balance server
----------------------------------------------------------------
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc stop
----------------------------------------------------------------

usage: pglb [-D path_of_config_file] [-W path_of_work_files] [-n][-v][-h][stop]
-l: print error logs in the log file.
-n: don't run in daemon mode.
-v: debug mode. need '-n' flag
-h: print this help
stop: stop pglb
(config file default path: ./pglb.conf)

这是postgresql数据库集群的安装原文以下是我对他的一些理解与解释：部署postgresql数据库集群至少需要安装四个数据库1、balanceDB数据库(以下简称平行数据库)，字面为平衡或并行处理数据库。他必需定义好节点数据库信息.不需要创建实际的数据库。2、ClusterDB集群节点数据库二个(以下简称节点数据库)。单个话就不是集群了。这不废话吗？,他必需定义好本节点信息（是只读还是读写都可以）与调度与决策处理数据信息。(节点数据库必需创建数据库，即initdb啦)3、pgreplicateDB调度与决策处理数据库（以下简称调度数据库)。他必需要定义好并行处理数据库信息、节点数据库信息。(调度与决策数据库不需要创建数据库）。以下为我的推断：当客户端访问数据库时候的一个流程。我们访问数据库的时候，是访问平行处理数据库。而不是访问ClusterDB或pgreplicateDB。由平行数据库询问调度数据库，当前查询或修改使用那个节点。节点收到平行数据库访问请求事务后，使用二阶段递交，因为每一个查询与修改都是一个事务，事务完成后发送信号到调度数据库。本次查询与修改递交完成。调度数据库再通知别的节点数据库同步更改。同步节点完成后调度数据库再通知原节点数据库本次事务顺利完成。你可以把结果反回给客户端了。在这样的一个集群里面我们不需要关心使用那个节点，节点之前怎样同步。之所以这样推测，是想使大家有一个概念为什么要这样部署，有什么作用。不能想当然而所以然。上面讲得可能简单了一点。但我想读完的朋友，应该会对postgresql集群数据库有一个基础的理论知识了。

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问