-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thursday, October 14, 2010

PGCluster - Postgres Clustering & Load Balancing


PGCluster - Postgres Clustering & Load Balancing


PG CLUSTER Setup
Download it from here
http://pgfoundry.org/frs/?group_id=1000072

# useradd postgres
# useradd -M -g postgres postgres
# mkdir /home/postgres

# yum -y install glibc-devel glibc.x86_64 glibc glibc_devel.x86_64 gcc gcc-c++ flex flex-devel bison bison-devel readline readline-devel zlib-devel

give ownership to postgres for pgcluster installation directory
# chown -R postgres:postgres pgcluster-1.7.0rc7
# su - postgres
# cd pgcluster-1.7.0rc7
# ./configure --enable-thread-safety --with-perl
# make
# su -
# make install
# chown -R postgres /usr/local/pgsql
# su - postgres
# /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Setup replication and Cluster

1: pgreplicate.conf and put it under /usr/local/pgsql/etc


#=============================================================
#  PGReplicate configuration file
#                                     for  PGCluster-1.1.0a
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# This file controls:
#       o which hosts & port are cluster server
#       o which port use for replication request from cluster server
#=============================================================
#
#-------------------------------------------------------------
# A setup of Cluster DB(s)
#
#        o Host_Name : The host name of Cluster DB.
#                      -- please write a host name by FQDN.
#                      -- do not write IP address.
#        o Port : The connection port with postmaster.
#        o Recovery_Port : The connection port at the time of
#                          a recovery sequence .
#        o LifeCheck_Port : connection for life check process
#-------------------------------------------------------------

<Cluster_Server_Info>
    <Host_Name>   clusterdb1 
                    5432       
    <Recovery_Port>       7101       
    <LifeCheck_Port>      7201       

<Cluster_Server_Info>
    <Host_Name>   clusterdb2
                    5432       
    <Recovery_Port>       7101       
    <LifeCheck_Port>      7201       

#<Cluster_Server_Info>
#    <Host_Name>   cluster3.postgres.jp  
#                    5432       
#    <Recovery_Port>       7101       
#    <LifeCheck_Port>      7201       
#
#

#<LoadBalance_Server_Info>
#    <Host_Name>   loadbalancer.postgres.jp 
#    <Recovery_Port>       6101       
#    <LifeCheck_Port>      6201       
#

<Replicate_Server_Info>
<Host_Name> pgrepl1
                       8001          
    <Recovery_Port>          8101          
    <LifeCheck_Port>         8201          


<Replicate_Server_Info>
<Host_Name> pgrepl2
                       8001          
    <Recovery_Port>          8101          
    <LifeCheck_Port>         8201          


<Status_Log_File>  /tmp/pgreplicate.sts 
<Error_Log_File>   /tmp/pgreplicate.log 
<Replication_Port>       8001           
<Recovery_Port>          8101           
<LifeCheck_Port>         8201           
<RLOG_Port>              8301           
<Response_Mode>        normal           
<Use_Replication_Log>      yes           
<Reserved_Connections>      1           

2:Cluster.conf and put it under /usr/local/pgsql/data

#============================================================
#          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 Replication Server information
#        o Host_Name : hostname
#        o Port : connection for postmaster
#        o Recovery_Port : connection for recovery process
#------------------------------------------------------------

#<replicate_server_info>
#<host_name>pgrepl1
# 8001
#<recovery_port>8101
#
#<recovery_port>7001
#<rsync_path>/usr/bin/rsync
#<rsync_option>ssh -1
#<rsync_compress>yes
# <pg_dump_path>/usr/local/pgsql/bin/pg_dump
#<when_stand_alone>read_only

<Replicate_Server_Info>
    <Host_Name> pgrepl1
    8001
    <Recovery_Port> 8101
    <LifeCheck_Port> 8201

#<Replicate_Server_Info>
#    <Host_Name> replicate2.postgres.jp
#    8002
#    <Recovery_Port> 8102
#    <LifeCheck_Port> 8202
#
#<Replicate_Server_Info>
#    <Host_Name> replicate3.postgres.jp
#    8003
#    <Recovery_Port> 8103
#    <LifeCheck_Port> 8203
#
#-------------------------------------------------------------
# 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
<LifeCheck_Port> 7201
<Rsync_Path> /usr/bin/rsync
<Rsync_Option> ssh -1
<When_Stand_Alone> read_only 
<Status_Log_File>  /tmp/cluster.sts
<Error_Log_File> /tmp/cluster.log 
#-------------------------------------------------------------
# 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     
#    <Table_Name>  log_table   
#

3: pglb.conf and put it under /usr/local/pgsql/etc

#============================================================
#          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>   clusterdb1 
            5432               
    <Max_Connect> 32                  

<Cluster_Server_Info>
    <Host_Name>   clusterdb2  
            5432               
    <Max_Connect> 32                 

#<Cluster_Server_Info>
#    <Host_Name>   post3.postgres.jp  
#            5432               
#    <Max_Connect> 32                 
#
#-------------------------------------------------------------
# set Load Balance server information
#        o Backend_Socket_Dir : Unix domain socket path for the backend
#        o Receive_Port : connection from client
#        o Recovery_Port : connection for recovery process
#        o LifeCheck_Port : connection for life check 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
#-------------------------------------------------------------


<host_name>    pglb   
<Backend_Socket_Dir>    /tmp    
<Receive_Port>          5433    
<Recovery_Port>         6001    
<LifeCheck_Port>        6201    
<Max_Cluster_Num>        128    
<Use_Connection_Pooling>  yes    

Rsync Setup
# rsync -auzr -e "ssh -o PreferredAuthentications=publickey" clusterdb1:/usr/local/pgsql/data /home/postgres/

Key Generation

#mkdir ~/.ssh
# chmod 700 ~/.ssh
# ssh-keygen -q -f ~/.ssh/id_rsa -t rsa
Enter passphrase (empty for no passphrase): …
Enter same passphrase again: …

Note: Do not use your account password (!), nor an empty passphrase.
Note: but in this case we didn’t enter any password
# chmod go-w ~/
# chmod 700 ~/.ssh
# chmod go-rwx ~/.ssh/*

Key Distribution
# scp ~/.ssh/id_rsa.pub server.example.edu:
# mkdir ~/.ssh
# chmod 700 ~/.ssh
# cat ~/id_rsa.pub >> ~/.ssh/authorized_keys
# chmod 600 ~/.ssh/authorized_keys
# rm ~/id_rsa.pub

# ssh -o PreferredAuthentications=publickey server.example.edu
Enter passphrase for key '/…/.ssh/id_rsa': …

server$

# ssh-keygen -q -f ~/.ssh/id_rsa -t rsa
# chmod go-w ~/
# chmod 700 ~/.ssh
# chmod go-rwx ~/.ssh/*

# scp ~/.ssh/id_rsa.pub
# chmod 700 ~/.ssh
# cat ~/id_rsa.pub >> ~/.ssh/authorized_keys
# chmod 600 ~/.ssh/authorized_keys

No comments:

Post a Comment