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