[Pgcluster-general] ERROR: This query is not permitted when all replication servers fell down
Karsten Nielsen
karsten at wwi.dk
Tue May 29 08:04:48 UTC 2007
Hi,
I have this setup.
[Load Balancer]
________/ \________
/ \
[DB-Cluster1] [DB-Cluster2]
\_________ _________/
\ \ / /
\ \/ /
\ /\ /
[Replicator 1] [Replicator 2]
It is running fine until I test a fall out of Replicator 1. If I turn off replicator 1 the system fails with:
ERROR: This query is not permitted when all replication servers fell down
Eventhough that replicator 2 is running.
If I restart postgres on cluster db 1 and 2 then the setup runs again - this is not the way I expect it to run.
I have attached my config files.
-------------- next part --------------
#============================================================
# 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> maildb-rp01.wwi.local </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> maildb-rp02.wwi.local </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
#<Replicate_Server_Info>
# <Host_Name> replicate3.postgres.jp </Host_Name>
# <Port> 8003 </Port>
# <Recovery_Port> 8103 </Recovery_Port>
#</Replicate_Server_Info>
#-------------------------------------------------------------
# set Cluster DB Server information
# o Host_Name : host name which connect with replication server
# o Recovery_Port : connection for recovery
# o Rsync_Path : path of rsync command
# o Rsync_Option : file transfer option for rsync
# o Rsync_Compress : use compression option for rsync
# [yes/no]. default : yes
# o Pg_Dump_Path : path of pg_dump
# o When_Stand_Alone : When all replication servers fell,
# you can set up two kinds of permittion,
# "real_only" or "read_write".
# o Replication_Timeout : timeout of each replication request
# o Lifecheck_Timeout : timeout of the lifecheck response
# o Lifecheck_Interval : interval time of the lifecheck
# (range 1s - 1h)
# 10s -- 10 seconds
# 10min -- 10 minutes
# 1h -- 1 hours
#-------------------------------------------------------------
<Host_Name> maildb01.wwi.local </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> 30s </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 3s </LifeCheck_Interval>
#-------------------------------------------------------------
# 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>
-------------- next part --------------
#============================================================
# 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> maildb-rp01.wwi.local </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
<Replicate_Server_Info>
<Host_Name> maildb-rp02.wwi.local </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
</Replicate_Server_Info>
#<Replicate_Server_Info>
# <Host_Name> replicate3.postgres.jp </Host_Name>
# <Port> 8003 </Port>
# <Recovery_Port> 8103 </Recovery_Port>
#</Replicate_Server_Info>
#-------------------------------------------------------------
# set Cluster DB Server information
# o Host_Name : host name which connect with replication server
# o Recovery_Port : connection for recovery
# o Rsync_Path : path of rsync command
# o Rsync_Option : file transfer option for rsync
# o Rsync_Compress : use compression option for rsync
# [yes/no]. default : yes
# o Pg_Dump_Path : path of pg_dump
# o When_Stand_Alone : When all replication servers fell,
# you can set up two kinds of permittion,
# "real_only" or "read_write".
# o Replication_Timeout : timeout of each replication request
# o Lifecheck_Timeout : timeout of the lifecheck response
# o Lifecheck_Interval : interval time of the lifecheck
# (range 1s - 1h)
# 10s -- 10 seconds
# 10min -- 10 minutes
# 1h -- 1 hours
#-------------------------------------------------------------
<Host_Name> maildb02.wwi.local </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> 30s </Replication_Timeout>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 3s </LifeCheck_Interval>
#-------------------------------------------------------------
# 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>
-------------- next part --------------
127.0.0.1 localhost.localdomain localhost
### PGCluster ###
172.22.0.112 maildb-lb.wwi.local
172.22.0.113 maildb-lb01.wwi.local
172.22.0.114 maildb-lb02.wwi.local
172.22.0.116 maildb-rp01.wwi.local
172.22.0.117 maildb-rp02.wwi.local
172.22.0.120 maildb01.wwi.local
172.22.0.121 maildb02.wwi.local
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
ff02::3 ip6-allhosts
-------------- next part --------------
#============================================================
# 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 of Cluster DB
# -- please write a host name by FQDN or IP address.
# o Port : connection for postmaster
# o Max_Connection : maximun number of connection to postmaster
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> maildb01.wwi.local </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 2000 </Max_Connect>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> maildb02.wwi.local </Host_Name>
<Port> 5432 </Port>
<Max_Connect> 2000 </Max_Connect>
</Cluster_Server_Info>
#<Cluster_Server_Info>
# <Host_Name> post3.postgres.jp </Host_Name>
# <Port> 5432 </Port>
# <Max_Connect> 32 </Max_Connect>
#</Cluster_Server_Info>
#-------------------------------------------------------------
# set Load Balance server information
# o Host_Name : The host name of this load balance server.
# -- please write a host name by FQDN or IP address.
# 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 Max_Cluster_Num : maximun number of cluster DB servers
# o Use_Connection_Pooling : use connection pool [yes/no]
# o Lifecheck_Timeout : timeout of the lifecheck response
# o Lifecheck_Interval : interval time of the lifecheck
# (range 1s - 1h)
# 10s -- 10 seconds
# 10min -- 10 minutes
# 1h -- 1 hours
#-------------------------------------------------------------
<Host_Name> maildb-lb.wwi.local </Host_Name>
<Backend_Socket_Dir> /tmp </Backend_Socket_Dir>
<Receive_Port> 5432 </Receive_Port>
<Recovery_Port> 6001 </Recovery_Port>
<Max_Cluster_Num> 5 </Max_Cluster_Num>
<Use_Connection_Pooling> no </Use_Connection_Pooling>
<LifeCheck_Timeout> 3s </LifeCheck_Timeout>
<LifeCheck_Interval> 5s </LifeCheck_Interval>
#-------------------------------------------------------------
# A setup of a log files
#
# o File_Name : Log file name with full path
# o File_Size : Maximun size of each log files
# please specipy in a number and unit(K or M)
# 10 -- 10 Byte
# 10K -- 10 KByte
# 10M -- 10 MByte
# o Rotate : Rotation times
# if specipied 0, old versions are removed.
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pglb.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
-------------- next part --------------
#=============================================================
# 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
#=============================================================
#
#-------------------------------------------------------------
# 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 .
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> maildb01.wwi.local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> maildb02.wwi.local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
#<Cluster_Server_Info>
# <Host_Name> cluster3.postgres.jp </Host_Name>
# <Port> 5432 </Port>
# <Recovery_Port> 7001 </Recovery_Port>
#</Cluster_Server_Info>
#
#-------------------------------------------------------------
# A setup of Load Balance Server
#
# o Host_Name : The host name of a load balance server.
# -- please write a host name by FQDN or IP address.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> maildb-lb.wwi.local </Host_Name>
<Recovery_Port> 6001 </Recovery_Port>
</LoadBalance_Server_Info>
#
#------------------------------------------------------------
# A setup of the cascade connection between replication servers.
# When you do not use RLOG recovery, you can skip this setup
#
# o Host_Name : The host name of the upper replication server.
# -- please write a host name by FQDN or IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#------------------------------------------------------------
#<Replicate_Server_Info>
# <Host_Name> maildb-rp01.wwi.local </Host_Name>
# <Port> 8001 </Port>
# <Recovery_Port> 8101 </Recovery_Port>
#</Replicate_Server_Info>
#
#-------------------------------------------------------------
# A setup of a replication server
#
# o Host_Name : The host name of the this replication server.
# -- please write a host name by FQDN or IP address.
# o Replicate_Port : connection for replication
# o Recovery_Port : connection for recovery
# o RLOG_Port : connection for replication log
# o Response_mode : timing which returns a response
# normal -- return result of DB which received the query
# reliable -- return result after waiting for response of
# all Cluster DBs.
# o Use_Replication_Log : use replication log
# [yes/no]. default : no
# o Replication_Timeout : timeout of each replication response
# o Lifecheck_Timeout : timeout of the lifecheck response
# o Lifecheck_Interval : interval time of the lifecheck
# (range 1s - 1h)
# 10s -- 10 seconds
# 10min -- 10 minutes
# 1h -- 1 hours
#-------------------------------------------------------------
<Host_Name> maildb-rp01.wwi.local </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
#
# o File_Name : Log file name with full path
# o File_Size : Maximun size of each log files
# please specipy in a number and unit(K or M)
# 10 -- 10 Byte
# 10K -- 10 KByte
# 10M -- 10 MByte
# o Rotate : Rotation times
# if specipied 0, old versions are removed.
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pgreplicate.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
-------------- next part --------------
#=============================================================
# 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
#=============================================================
#
#-------------------------------------------------------------
# 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 .
#-------------------------------------------------------------
<Cluster_Server_Info>
<Host_Name> maildb01.wwi.local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> maildb02.wwi.local </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7001 </Recovery_Port>
</Cluster_Server_Info>
#<Cluster_Server_Info>
# <Host_Name> cluster3.postgres.jp </Host_Name>
# <Port> 5432 </Port>
# <Recovery_Port> 7001 </Recovery_Port>
#</Cluster_Server_Info>
#
#-------------------------------------------------------------
# A setup of Load Balance Server
#
# o Host_Name : The host name of a load balance server.
# -- please write a host name by FQDN or IP address.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#-------------------------------------------------------------
<LoadBalance_Server_Info>
<Host_Name> maildb-lb.wwi.local </Host_Name>
<Recovery_Port> 6001 </Recovery_Port>
</LoadBalance_Server_Info>
#
#------------------------------------------------------------
# A setup of the cascade connection between replication servers.
# When you do not use RLOG recovery, you can skip this setup
#
# o Host_Name : The host name of the upper replication server.
# -- please write a host name by FQDN or IP address.
# o Port : The connection port with postmaster.
# o Recovery_Port : The connection port at the time of
# a recovery sequence .
#------------------------------------------------------------
#<Replicate_Server_Info>
# <Host_Name> maildb-rp01.wwi.local </Host_Name>
# <Port> 8001 </Port>
# <Recovery_Port> 8101 </Recovery_Port>
#</Replicate_Server_Info>
#
#-------------------------------------------------------------
# A setup of a replication server
#
# o Host_Name : The host name of the this replication server.
# -- please write a host name by FQDN or IP address.
# o Replicate_Port : connection for replication
# o Recovery_Port : connection for recovery
# o RLOG_Port : connection for replication log
# o Response_mode : timing which returns a response
# normal -- return result of DB which received the query
# reliable -- return result after waiting for response of
# all Cluster DBs.
# o Use_Replication_Log : use replication log
# [yes/no]. default : no
# o Replication_Timeout : timeout of each replication response
# o Lifecheck_Timeout : timeout of the lifecheck response
# o Lifecheck_Interval : interval time of the lifecheck
# (range 1s - 1h)
# 10s -- 10 seconds
# 10min -- 10 minutes
# 1h -- 1 hours
#-------------------------------------------------------------
<Host_Name> maildb-rp02.wwi.local </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
#
# o File_Name : Log file name with full path
# o File_Size : Maximun size of each log files
# please specipy in a number and unit(K or M)
# 10 -- 10 Byte
# 10K -- 10 KByte
# 10M -- 10 MByte
# o Rotate : Rotation times
# if specipied 0, old versions are removed.
#-------------------------------------------------------------
<Log_File_Info>
<File_Name> /var/log/postgresql/pgreplicate.log </File_Name>
<File_Size> 1M </File_Size>
<Rotate> 3 </Rotate>
</Log_File_Info>
More information about the Pgcluster-general
mailing list