[pgpool-general: 11] Re: Pgpool disconnect clients when recovery nodes.

[Tatsuo Ishii]

With pgpool's native replication mode, there's no way to avoid
disconnection to clients at recovery.

In pgpool-II 3.1 or later, with streaming replication mode and
recovery does not disconnect existing client connections.

From pgpool-II 3.1's "Enhancements" in NEWS:

	  - Enhance online recovery in streaming replication mode.
		Now restarting pgpool-II children is avoided when recovery finished. So
		existing sessions can be continued while doing online recovery(Tatsuo)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> I have a cluster with 2 postgresql 8.4 backends and 1 pgpool2 3.0.3 version. They are running on Debian Squeeze using backports repository. 
> 
> When I try to recovery one of the backends using pcp_recovery_node its fails to recover until all clients connected to pgpool are disconnected. 
> 
> Also I tried streaming replication in other machines with PostgreSQL 9.1 but when recover a backend Pgpool drops all clients connected to it. 
> 
> Is there ant way to Pgpool maintain clients connected when I recover a dead node? 
> 
> This is my pgpool.conf for PostgreSQL 8.4 nodes. 
> 
> listen_addresses = '*' 
> port = 5432 
> pcp_port = 9898 
> socket_dir = '/var/run/postgresql' 
> pcp_socket_dir = '/var/run/postgresql' 
> backend_socket_dir = '/var/run/postgresql' 
> pcp_timeout = 10 
> num_init_children = 32 
> max_pool = 4 
> child_life_time = 300 
> connection_life_time = 0 
> child_max_connections = 0 
> client_idle_limit = 0 
> authentication_timeout = 60 
> logdir = '/var/run/postgresql' 
> pid_file_name = '/var/run/postgresql/pgpool.pid' 
> replication_mode = true 
> load_balance_mode = true 
> replication_stop_on_mismatch = true 
> replicate_select = false 
> reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' 
> print_timestamp = true 
> master_slave_mode = false 
> connection_cache = true 
> health_check_timeout = 3 
> health_check_period = 5 
> health_check_user = 'pgpool2' 
> failover_command = 'pgpool-failover %d %h %p %D %m %M' 
> failback_command = 'pgpool-failback %d %h %p %D %m %M' 
> insert_lock = true 
> ignore_leading_white_space = true 
> log_statement = false 
> log_connections = false 
> log_hostname = false 
> parallel_mode = false 
> enable_query_cache = false 
> pgpool2_hostname = 'ppsqlft1' 
> system_db_hostname = 'localhost' 
> system_db_port = 5432 
> system_db_dbname = 'pgpool' 
> system_db_schema = 'pgpool_catalog' 
> system_db_user = 'pgpool' 
> system_db_password = '' 
> backend_hostname0 = 'PPSQLBK1' 
> backend_port0 = 5432 
> backend_weight0 = 1 
> backend_data_directory0 = '/var/lib/postgresql/8.4/main' 
> backend_hostname1 = 'PPSQLBK2' 
> backend_port1 = 5432 
> backend_weight1 = 1 
> backend_data_directory1 = '/var/lib/postgresql/8.4/main' 
> enable_pool_hba = true 
> recovery_user = 'pgpool2' 
> recovery_password = '' 
> recovery_1st_stage_command = 'base-backup' 
> recovery_2nd_stage_command = 'pgpool-recovery-pitr' 
> recovery_timeout = 90 
> client_idle_limit_in_recovery = 10 
> 
> 
> Thanks for your help 
> 
> -- 
> 
> Joaquín Manuel Llano Montero 
> 
> ________________________________________ 
> 
> C O MPAREX España S.A. 
> C/ Severo Ochoa, 20 
> 06800 Mérida (Badajoz) España 
> phone: +34 924 373 573 
> fax: +34 924 374 518 
> mobile: +34 615 848 019 
> email : joaquin.llano at comparex.es 
> web: www.comparex.es 
>