Example Setup
This document is by no means the bible on how to set up PGCluster, it's simply a list of all of the things I've found and done in order to get a working PGCluster system. If there are some things that I haven't got working yet, I will highlight them in the document. My idea is that this will grow and be altered when I get more accurate information.
First off, you need to know that I have spent a couple of months 'playing' with PGCluster and looking through endless mailing list posts and documents to put this all together. I'm not a PostgreSQL expert. If at any time you feel there is a mistake, please feel free to email me at john.gardner@….
The Testing Scenario
I am using three servers to test PGCluster as shown below. All servers are CentOS 4.4 running PGCluster 1.7 rc 5:
SERVER 1 SERVER 2 SERVER 3
+----------------+ +----------------+ +----------------+
| cluster_1 | | cluster_2 | VPN | cluster_3 |
| rep_1 |------| rep_2 |------------------------| |
| lb_1 | | lb_2 | | |
+----------------+ +----------------+ +----------------+
There are three physical servers involved. SERVER 1 and SERVER 2 are located on the same subnet, but SERVER 3 is located on another subnet, separated by VPN over an ADSL connection. It is always recommended to put all of the PGCluster servers on the same subnet, or at least the same LAN, running one Cluster DB node in a different geographical location is not what PGCluster was built for, however I needed to at least try this scenario for the future production set up.
cluster_1, cluster_2 and cluster_3 are Cluster DB nodes.
rep_1 and rep_2 are Replicators.
lb_1 and lb_2 are Load Balancers.
The Host File
After experiencing many problems with the replicators, and also reading many mailing list posters who were also having problems, I have put this entry right at the start of the document. Check your hosts file!
It appears that not setting up your hosts file as PGCluster expects it may cause many problems further down the line.
It is very important that each server in the PGCluster infrastructure can reliably communicate with any other server, to do this I have configured my /etc/host file as this on every server:
127.0.0.1 localhost.localdomain localhost ### PGCluster ### 192.168.1.19 cluster_1 192.168.1.20 cluster_2 192.168.10.59 cluster_3 192.168.1.19 rep_1 192.168.1.20 rep_2 192.168.1.19 lb_1 192.168.1.20 lb_2
When these hosts file have been set up on every server in your PGCluster system, check the connectivity by pinging all of the servers. It will take time, but you will be able diagnose any faults before anything else happens.
Installing PGCluster
I don't feel the need to document the whole installation process as this is available at: http://pgcluster.projects.postgresql.org/1_3/install.html
Eventually, the installation instructions will be transferred to this site.
Configuring PGCluster
Again, this is already available here: http://pgcluster.projects.postgresql.org/1_3/configuration.html
Configuration Files
These are very important to get correct. There doesn't seem to be much documentation on these files, but they are extremely important for correct operation of your PGCluster system. Each version of PGCluster (1.5, 1.7 etc.) have different formatting, so it is always advised to use the example configuration files that come with your version of PGCluster. The following will work for version 1.7.
CAVEAT: The only problem I am currently having is with two Replicators. When the primary Replicator, goes down, the secondary doesn't appear to take over. Other than that, the config files below seem to work for the above setup.
SERVER 1
cluster.conf
#------------------------------------------------------------
# file: cluster.conf (cluster_1)
#------------------------------------------------------------
# set Replication Server information
#------------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> rep_1 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> rep_2 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
#-------------------------------------------------------------
# set Cluster DB Server information
#-------------------------------------------------------------
<Host_Name> cluster_1 </Host_Name>
<Recovery_Port> 7001 </Recovery_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -1 </Rsync_Option>
<Rsync_Compress> yes </Rsync_Compress>
<Pg_Dump_Path> /usr/local/pgsql/bin/pg_dump </Pg_Dump_Path>
<When_Stand_Alone> read_only </When_Stand_Alone>
<Replication_Timeout> 1min </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 11s </LifeCheck_Interval>
pgreplicate.conf
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# A setup of Cluster DB(s)
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cluster_1 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_2 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_3 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
#--------------------------------------------------------------------
# A setup of loader balancer
#--------------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> lb_1 </Host_Name>
<Recovery_Port> 6101 </Recovery_Port>
</LoadBalance_Server_Info>
<LoadBalance_Server_Info>
<Host_Name> lb_2 </Host_Name>
<Recovery_Port> 6101 </Recovery_Port>
</LoadBalance_Server_Info>
#------------------------------------------------------------
# A setup of a replication server
#-------------------------------------------------------------
<Host_Name> rep_1 </Host_Name>
<Replication_Port> 8001 </Replication_Port>
<Recovery_Port> 8101 </Recovery_Port>
<RLOG_Port> 8301 </RLOG_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> no </Use_Replication_Log>
<Replication_Timeout> 1min </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 15s </LifeCheck_Interval>
#-------------------------------------------------------------
# A setup of a log files
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pgreplicate.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
pglb.conf
#-------------------------------------------------------------
# file: pglb.conf
#-------------------------------------------------------------
# set cluster DB server information
#--------------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cluster_1 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_2 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_3 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
#-------------------------------------------------------------
# set Load Balance server information
#-------------------------------------------------------------
<Host_Name> lb_1 </Host_Name>
<Backend_Socket_Dir> /tmp </Backend_Socket_Dir>
<Receive_Port> 5433 </Receive_Port>
<Recovery_Port> 6101 </Recovery_Port>
<Max_Cluster_Num> 128 </Max_Cluster_Num>
<Use_Connection_Pooling> no </Use_Connection_Pooling>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 15s </LifeCheck_Interval>
#-------------------------------------------------------------
# A setup of a log files
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pglb.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
SERVER 2
cluster.conf
#------------------------------------------------------------
# file: cluster.conf (cluster_2)
#------------------------------------------------------------
# set Replication Server information
#------------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> rep_1 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> rep_2 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
#-------------------------------------------------------------
# set Cluster DB Server information
#-------------------------------------------------------------
<Host_Name> cluster_2 </Host_Name>
<Recovery_Port> 7001 </Recovery_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -1 </Rsync_Option>
<Rsync_Compress> yes </Rsync_Compress>
<Pg_Dump_Path> /usr/local/pgsql/bin/pg_dump </Pg_Dump_Path>
<When_Stand_Alone> read_only </When_Stand_Alone>
<Replication_Timeout> 1min </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 11s </LifeCheck_Interval>
pgreplicate.conf
#-------------------------------------------------------------
# file: pgreplicate.conf
#-------------------------------------------------------------
# A setup of Cluster DB(s)
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cluster_1 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_2 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_3 </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
#-------------------------------------------------------------
# A setup of Load Balance Server
#--------------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> lb_1 </Host_Name>
<Recovery_Port> 6101 </Recovery_Port>
</LoadBalance_Server_Info>
<LoadBalance_Server_Info>
<Host_Name> lb_2 </Host_Name>
<Recovery_Port> 6101 </Recovery_Port>
</LoadBalance_Server_Info>
#-------------------------------------------------------------
# A setup of a replication server
#-------------------------------------------------------------
<Host_Name> rep_2 </Host_Name>
<Replication_Port> 8001 </Replication_Port>
<Recovery_Port> 8101 </Recovery_Port>
<RLOG_Port> 8301 </RLOG_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> no </Use_Replication_Log>
<Replication_Timeout> 1min </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 15s </LifeCheck_Interval>
#-------------------------------------------------------------
# A setup of a log files
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pgreplicate.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
file: pglb.conf
#-------------------------------------------------------------
# file: pglb.conf
#-------------------------------------------------------------
# set cluster DB server information
#--------------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> cluster_1 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_2 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> cluster_3 </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 30 </Max_Connect>
</Cluster_Server_Info>
#-------------------------------------------------------------
# set Load Balance server information
#-------------------------------------------------------------
<Host_Name> lb_2 </Host_Name>
<Backend_Socket_Dir> /tmp </Backend_Socket_Dir>
<Receive_Port> 5433 </Receive_Port>
<Recovery_Port> 6101 </Recovery_Port>
<Max_Cluster_Num> 128 </Max_Cluster_Num>
<Use_Connection_Pooling> no </Use_Connection_Pooling>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 15s </LifeCheck_Interval>
#-------------------------------------------------------------
# A setup of a log files
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pglb.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
SERVER 3
file: cluster.conf
#------------------------------------------------------------
# file: cluster.conf (cluster_3)
#------------------------------------------------------------
# set Replication Server information
#------------------------------------------------------------
<Replicate_Server_Info>
<Host_Name> rep_1 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> rep_2 </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
#-------------------------------------------------------------
# set Cluster DB Server information
#-------------------------------------------------------------
<Host_Name> cluster_3 </Host_Name>
<Recovery_Port> 7001 </Recovery_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -1 </Rsync_Option>
<Rsync_Compress> yes </Rsync_Compress>
<Pg_Dump_Path> /usr/local/pgsql/bin/pg_dump </Pg_Dump_Path>
<When_Stand_Alone> read_only </When_Stand_Alone>
<Replication_Timeout> 1min </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 11s </LifeCheck_Interval>
John Gardner
john.gardner@…
Return to Wiki Start
