[pgpool-general: 4632] Re: PGPOOL-II Replication Mode sequential execution of replicated statements

[Lazar Krumov]

Hi All, 

Few hours next my initial post, a colleague of mine found the needed by me information in:
http://www.sraoss.jp/pipermail/pgpool-general/2013-March/001507.html

Sorry for the duplicated topic! Simply I could not find any information for long time and finally decided to post in the mailing list.
May be it would be nice to clarify in eather FAQ or Manual the sequential update of first two nodes when in Replication Mode?

Thank You for the great PGPOOL software!
Cheers,
LAZA


> Hi PGPOOL forum members,
> 
> I try to use PGPOOL-II 3.5.1 in "Replication Mode" with two backend servers of PostgreSQL 9.1.19. When I execute a modification SQL statement it is replicated to both servers. PGPOOL sends the replicated SQL first to one of the servers. When this SQL finishes and returns the result to PGPOOL, then PGPOOL sends the replicated SQL to the second server. I presume that the replicated SQL statements should be send simultaneously to the backends in Replication Mode. Could not find any description (DOC, FAQ, Goo) on how should be sent the SQLs to the backends - either simultaneously or sequentially ?
> My PGPOOL config is:
> listen_addresses = '*'
> port = 5491
> socket_dir = '/var/run/postgresql'
> pcp_port = 9891
> pcp_socket_dir = '/var/run/postgresql'
> backend_hostname0 = 'ca-dbs05'
> backend_port0 = 10091
> backend_weight0 = 20
> backend_data_directory0 = '/opt/pgsql91/data/iiscpr'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_hostname1 = 'ca-dbs04'
> backend_port1 = 10091
> backend_weight1 = 10
> backend_data_directory1 = '/opt/pgsql91/data/iiscpr'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> enable_pool_hba = on
> pool_passwd = 'pool_passwd'
> authentication_timeout = 60
> ssl = off
> num_init_children = 256
> max_pool = 8
> child_life_time = 300
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> log_destination = 'stderr'
> print_timestamp = on
> log_connections = off
> log_hostname = off
> log_statement = off
> log_per_node_statement = on
> log_standby_delay = 'none'
> syslog_facility = 'LOCAL1'
> syslog_ident = 'pgpool'
> debug_level = 0
> pid_file_name = '/var/run/postgresql/pgpool.pid'
> logdir = '/var/log/postgresql'
> connection_cache = on
> reset_query_list = 'ABORT; DISCARD ALL'
> replication_mode = on
> replicate_select = off
> insert_lock = on
> lobj_lock_table = 'my_lock_table'
> replication_stop_on_mismatch = off
> failover_if_affected_tuples_mismatch = off
> load_balance_mode = on
> ignore_leading_white_space = on
> white_function_list = 'compare_versions_get_changed_imm_right,compare_versions_get_changed_imm,compare_versions_get_deleted_imm,compare_versions_get_new_imm,getfromekatte,getplaceforekatte,getprojectestate,getprojectversionname,getspobjectdesc,gettempcadregfull,gettempsurvpointreg,getunfinishedproject,ntables_ngetapptype,ntables_ngetboundarytype,ntables_ngetdenotationtype,ntables_ngetdeterminingtype,ntables_ngetfunctype,ntables_ngetisunmovable,ntables_ngetisupdate,ntables_ngetlaw,ntables_ngetlinesymboltype,ntables_ngetpointcenexist,ntables_ngetpointtype,ntables_ngetprecisiontype,ntables_ngetprojectstate,ntables_ngetprojlineboundarytype,ntables_ngetpropertytype,ntables_ngetpurposetype,ntables_ngetscheme_linetype,ntables_ngetsignaltype,ntables_ngetsource,ntables_ngetstabletype,ntables_ngetsubboundarytype,ntables_ngetsurveypointtype,ntables_ngettexttype,ntables_ngetusagetype,project_cadnumber_getresnumberfortask,,ntables_ngetfiletype,ntables_ngetprojecttype,sgetaddressinfo,adm_util_get_adm_offices,adm_util_get_adm_nomdesc,adm_util_get_user_sessions,adm_util_get_adm_userinfo,ntables_geo_get_npointclass,getdata_geo_get_cpoint,getdeletedcadnumbers,getdocdesc,sgetpersonsbyshortname,getdocumentbyprojectid,getekatte ,getekatte_desc,getekatte_in ,getofficebyekatte,getofficesbyzone,cad_validates_getvalidateforekatte,getprojectsandtasksiiscpr,getproject_xmlfile,getquarterforekatte,getreldocforservice,getrgonumberdesc,getstreetforekatte,getsubject,getsubjectdesc,xml_messages_xmlgetdocrights_kais,ntables_getntable,cad4_cad4_defs ,cad4_cad4_table,ntables_ngetapptype_short,ntables_ngetcadobjectactivitysearch,ntables_ngetdoctype,ntables_ngetekatte,ntables_ngetekattetype,ntables_ngethightcoordinatesystem,ntables_ngethightmethod,ntables_ngetidentstatus,ntables_ngetmunicipality,ntables_ngetofficepublisher,ntables_ngetplcoordinatesystem,ntables_ngetplmethod,cadfunctiondesc_getchangevalidatesbyservid,ntables_ngetpublisher,ntables_ngetquartertype,ntables_ngetreceivestatus,ntables_ngetregion,ntables_ngetrgoobjecttype,ntables_ngetrightchangeflag,cadobjects_record_getcadnumberengendered,cadobjects_record_getestatechangebyprojectid,cadobjects_record_getestateinfobycadnumanddate,ntables_ngetstreettitle,ntables_ngetstreettype,ntables_ngetsubtype,ntables_ngetsurveyortype,cad_serialize_getcadprojserialize,cad_serialize_geterrorinfo,project_cadnumber_getmaxnumber_scheme_app,cmcr_edit_sgetcadplace,cmcr_edit_sgetcadplacebyadmid,cmcr_edit_sgetcadquarter,cmcr_edit_sgetcadquarterbyadmid,cmcr_edit_sgetcadstreet,cmcr_edit_sgetcadstreetbyadmid,cmcr_edit_sgetnpublisher,cmcr_edit_sgetnpublisherbyadmid,sgetaddressinfo,getaddressdesc,sgetdocumentinfo,sgetpersonsbyname,cad_validates_getvalidatebasic,cad_validates_getvalidatebyservice,getinfoxml,getlayerstat,test_modul_info_get_bnd_info_by_ekatte,test_modul_info_getcadproj_comparenode_wkt,test_modul_info_getcadproj_compareedge_wkt,test_modul_info_getcadproj_comparepoly_wkt,test_modul_info_getcadproj_comparepoint_wkt,test_modul_info_getcadproj_compareline_wkt,test_modul_info_getcadproj_comparebnd_wkt'
> black_function_list = ''
> master_slave_mode = off
> master_slave_sub_mode = 'slony'
> sr_check_period = 0
> sr_check_user = 'nobody'
> sr_check_password = ''
> delay_threshold = 0
> follow_master_command = ''
> parallel_mode = off
> pgpool2_hostname = ''
> system_db_hostname = 'localhost'
> system_db_port = 5432
> system_db_dbname = 'pgpool'
> system_db_schema = 'pgpool_catalog'
> system_db_user = 'pgpool'
> system_db_password = ''
> health_check_period = 0
> health_check_timeout = 20
> health_check_user = 'nobody'
> health_check_password = ''
> health_check_max_retries = 0
> health_check_retry_delay = 1
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = on
> search_primary_node_timeout = 10
> recovery_user = 'nobody'
> recovery_password = ''
> recovery_1st_stage_command = ''
> recovery_2nd_stage_command = ''
> recovery_timeout = 90
> client_idle_limit_in_recovery = 0
> use_watchdog = on
> trusted_servers = 'ca-dbs04'
> ping_path = '/bin'
> wd_hostname = 'ca-dbs05'
> wd_port = 9091
> wd_authkey = 'pg at agkk1'
> delegate_IP = '192.168.0.219'
> ifconfig_path = '/opt/pgsql91/bin/'
> if_up_cmd = 'ifconfig bond0:vip inet $_IP_$ netmask 255.255.255.0'
> if_down_cmd = 'ifconfig bond0:vip down'
> arping_path = '/opt/pgsql91/bin/'
> arping_cmd = 'arping -U $_IP_$ -w 1 -c 50'
> clear_memqcache_on_escalation = on
> wd_escalation_command = ''
> wd_lifecheck_method = 'heartbeat'
> wd_interval = 10
> wd_heartbeat_port = 9691
> wd_heartbeat_keepalive = 2
> wd_heartbeat_deadtime = 30
> heartbeat_destination0 = 'ca-dbs04'
> heartbeat_destination_port0 = 9691 
> heartbeat_device0 = ''
> wd_life_point = 3
> wd_lifecheck_query = 'SELECT 1'
> wd_lifecheck_dbname = 'template1'
> wd_lifecheck_user = 'nobody'
> wd_lifecheck_password = ''
> other_pgpool_hostname0 = 'ca-dbs04'
> other_pgpool_port0 = 5491
> other_wd_port0 = 9091
> relcache_expire = 0
> relcache_size = 256
> check_temp_table = on
> memory_cache_enabled = off
> memqcache_method = 'shmem'
> memqcache_memcached_host = 'localhost'
> memqcache_memcached_port = 11211
> memqcache_total_size = 67108864
> memqcache_max_num_cache = 1000000
> memqcache_expire = 0
> memqcache_auto_cache_invalidation = on
> memqcache_maxcache = 409600
> memqcache_cache_block_size = 1048576
> memqcache_oiddir = '/var/log/pgpool/oiddir'
> white_memqcache_table_list = ''
> black_memqcache_table_list = ''
> 
> Kind Regards 
> Lazar