PostgreSQL Cluster

netkiller

www.linuxvirtualserver.org
www.linux-ha.org
http://gborg.postgresql.org/project/slony1/projdisplay.php

ecloud

Cluster的前提是支持裸设备，这方面PostgreSQL好象还没有什么计划

bitbird

claster和裸设备有什么必然联系？真是莫名其妙。

edwardzeng

我们公司的两台数据库进行实时同步备份. 其中一台是read-write.备份的那台是read-only. 同步的过程都没有什么问题.但就是最近非常慢. 为了加快read-wirte 数据库,将postgresql.conf 里的fsync 设为 "false".
速度有了很大的提升.当是有个表的index有问题.结果导致slony 的数据同步无法进行. 请问有何解救?

suowei1979

good

kingkii

还有erserver， http://www.erserver.com，不过都没有用过，楼主有没有这方面的经验介绍一下，我很关心数据同步复制和高可用性，不知道postgres这方面有什么解决方案？

wxxszzz

edwardzeng

请问两个postgresql数据库是怎么进行同步备份的啊.

是用第三方软件,还是postgresql自己就带了呢?

请赐教.

myaniu

Multi-Master replication: PGCluster
The PGCluster is constituted by Load Balance Server, Cluster DB Server, and Replication Server. With each server's combination, PGCluster can build two kinds of systems, a load-balancing system and a highly available system.

－－－－－－－－－－－－－－
http://pgcluster.projects.postgresql.org/
http://pgcluster.projects.postgresql.org/1_3/index.html

wxxszzz

谢谢楼上的提示，我知道了，测试中。

wxxszzz

PGCluster Installation Instructions

=============================================================
1. Installation
=============================================================

1-1. Install Cluster DB Server, Replication Server & Load Balancer
----------------------------------------------------------------
$ cd $source_dir
$ ./configure --enable-thread-safety
$ gmake
$ su
# gmake install
# chown -R postgres /usr/local/pgsql
----------------------------------------------------------------

=============================================================
2. Initialize DB
=============================================================
$ su
# adduser postgres
# mkdir /usr/local/pgsql/data
# chown postgres /usr/local/pgsql/data
# su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/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 port  use 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>;
    <ort>;        5432                </Port>;
    <Max_Connect>; 32                   </Max_Connect>;
</Cluster_Server_Info>;
<Cluster_Server_Info>;
    <Host_Name>;   c2.hoo.com  </Host_Name>;
    <ort>;        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>;
        <ort>; 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>;
    <ort>;           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>;
    <ort>;           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)