No subject
Mon Nov 14 14:56:52 JST 2011
- 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 versi=
on. They are running on Debian Squeeze using backports repository.
>
> When I try to recovery one of the backends using pcp_recovery_node its fa=
ils 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 de=
ad node?
>
> This is my pgpool.conf for PostgreSQL 8.4 nodes.
>
> listen_addresses =3D '*'
> port =3D 5432
> pcp_port =3D 9898
> socket_dir =3D '/var/run/postgresql'
> pcp_socket_dir =3D '/var/run/postgresql'
> backend_socket_dir =3D '/var/run/postgresql'
> pcp_timeout =3D 10
> num_init_children =3D 32
> max_pool =3D 4
> child_life_time =3D 300
> connection_life_time =3D 0
> child_max_connections =3D 0
> client_idle_limit =3D 0
> authentication_timeout =3D 60
> logdir =3D '/var/run/postgresql'
> pid_file_name =3D '/var/run/postgresql/pgpool.pid'
> replication_mode =3D true
> load_balance_mode =3D true
> replication_stop_on_mismatch =3D true
> replicate_select =3D false
> reset_query_list =3D 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT=
'
> print_timestamp =3D true
> master_slave_mode =3D false
> connection_cache =3D true
> health_check_timeout =3D 3
> health_check_period =3D 5
> health_check_user =3D 'pgpool2'
> failover_command =3D 'pgpool-failover %d %h %p %D %m %M'
> failback_command =3D 'pgpool-failback %d %h %p %D %m %M'
> insert_lock =3D true
> ignore_leading_white_space =3D true
> log_statement =3D false
> log_connections =3D false
> log_hostname =3D false
> parallel_mode =3D false
> enable_query_cache =3D false
> pgpool2_hostname =3D 'ppsqlft1'
> system_db_hostname =3D 'localhost'
> system_db_port =3D 5432
> system_db_dbname =3D 'pgpool'
> system_db_schema =3D 'pgpool_catalog'
> system_db_user =3D 'pgpool'
> system_db_password =3D ''
> backend_hostname0 =3D 'PPSQLBK1'
> backend_port0 =3D 5432
> backend_weight0 =3D 1
> backend_data_directory0 =3D '/var/lib/postgresql/8.4/main'
> backend_hostname1 =3D 'PPSQLBK2'
> backend_port1 =3D 5432
> backend_weight1 =3D 1
> backend_data_directory1 =3D '/var/lib/postgresql/8.4/main'
> enable_pool_hba =3D true
> recovery_user =3D 'pgpool2'
> recovery_password =3D ''
> recovery_1st_stage_command =3D 'base-backup'
> recovery_2nd_stage_command =3D 'pgpool-recovery-pitr'
> recovery_timeout =3D 90
> client_idle_limit_in_recovery =3D 10
>
>
> Thanks for your help
>
> --
>
> Joaqu=C3=ADn Manuel Llano Montero
>
> ________________________________________
>
> C O MPAREX Espa=C3=B1a S.A.
> C/ Severo Ochoa, 20
> 06800 M=C3=A9rida (Badajoz) Espa=C3=B1a
> 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
>
--=_100d2ae0-254d-4dd4-bba5-1902e53902ff
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: Verdana; font-size: 12pt; color: #000000'><br>Hi =
again.<br><br>First of all thanks for your advice Tatsuo.<br><br>I've compi=
led and installed Pgpool 3.1.1. <br><br>Now when recovery a node clients ar=
en't disconnected. But they are disconnected when a node fails (not matter =
if the node is primary or slave).<br><br>Is there any way to no disconnect =
clients when a node fails?<br><br>PostgreSQL version is 9.1.1.<br><br>Thank=
s again.<br><br><hr id=3D"zwchr"><div style=3D"color:#000;font-weight:norma=
l;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-s=
erif;font-size:12pt;"><b>De: </b>"Tatsuo Ishii" <ishii at postgresql.org>=
;<br><b>Para: </b>"joaquin llano" <joaquin.llano at cpxextremadura.com><=
br><b>CC: </b>pgpool-general at pgpool.net<br><b>Enviados: </b>Martes, 29 de N=
oviembre 2011 13:00:16<br><b>Asunto: </b>Re: [pgpool-general: 10] Pgpool di=
sconnect clients when recovery nodes.<br><br>With pgpool's native replicati=
on mode, there's no way to avoid<br>disconnection to clients at recovery.<b=
r><br>In pgpool-II 3.1 or later, with streaming replication mode and<br>rec=
overy does not disconnect existing client connections.<br><br>From pgpool-I=
I 3.1's "Enhancements" in NEWS:<br><br> =
- Enhance online recovery in streaming replication mode.=
<br>  =
; Now restarting pgpool-II children is avoided when =
recovery finished. So<br> &n=
bsp; existing sessions can be cont=
inued while doing online recovery(Tatsuo)<br>--<br>Tatsuo Ishii<br>SRA OSS,=
Inc. Japan<br>English: http://www.sraoss.co.jp/index_en.php<br>Japanese: h=
ttp://www.sraoss.co.jp<br><br>> I have a cluster with 2 postgresql 8.4 b=
ackends and 1 pgpool2 3.0.3 version. They are running on Debian Squeeze usi=
ng backports repository. <br>> <br>> When I try to recovery one of th=
e backends using pcp_recovery_node its fails to recover until all clients c=
onnected to pgpool are disconnected. <br>> <br>> Also I tried streami=
ng replication in other machines with PostgreSQL 9.1 but when recover a bac=
kend Pgpool drops all clients connected to it. <br>> <br>> Is there a=
nt way to Pgpool maintain clients connected when I recover a dead node? <br=
>> <br>> This is my pgpool.conf for PostgreSQL 8.4 nodes. <br>> <b=
r>> listen_addresses =3D '*' <br>> port =3D 5432 <br>> pcp_port =
=3D 9898 <br>> socket_dir =3D '/var/run/postgresql' <br>> pcp_socket_=
dir =3D '/var/run/postgresql' <br>> backend_socket_dir =3D '/var/run/pos=
tgresql' <br>> pcp_timeout =3D 10 <br>> num_init_children =3D 32 <br>=
> max_pool =3D 4 <br>> child_life_time =3D 300 <br>> connection_li=
fe_time =3D 0 <br>> child_max_connections =3D 0 <br>> client_idle_lim=
it =3D 0 <br>> authentication_timeout =3D 60 <br>> logdir =3D '/var/r=
un/postgresql' <br>> pid_file_name =3D '/var/run/postgresql/pgpool.pid' =
<br>> replication_mode =3D true <br>> load_balance_mode =3D true <br>=
> replication_stop_on_mismatch =3D true <br>> replicate_select =3D fa=
lse <br>> reset_query_list =3D 'ABORT; RESET ALL; SET SESSION AUTHORIZAT=
ION DEFAULT' <br>> print_timestamp =3D true <br>> master_slave_mode =
=3D false <br>> connection_cache =3D true <br>> health_check_timeout =
=3D 3 <br>> health_check_period =3D 5 <br>> health_check_user =3D 'pg=
pool2' <br>> failover_command =3D 'pgpool-failover %d %h %p %D %m %M' <b=
r>> failback_command =3D 'pgpool-failback %d %h %p %D %m %M' <br>> in=
sert_lock =3D true <br>> ignore_leading_white_space =3D true <br>> lo=
g_statement =3D false <br>> log_connections =3D false <br>> log_hostn=
ame =3D false <br>> parallel_mode =3D false <br>> enable_query_cache =
=3D false <br>> pgpool2_hostname =3D 'ppsqlft1' <br>> system_db_hostn=
ame =3D 'localhost' <br>> system_db_port =3D 5432 <br>> system_db_dbn=
ame =3D 'pgpool' <br>> system_db_schema =3D 'pgpool_catalog' <br>> sy=
stem_db_user =3D 'pgpool' <br>> system_db_password =3D '' <br>> backe=
nd_hostname0 =3D 'PPSQLBK1' <br>> backend_port0 =3D 5432 <br>> backen=
d_weight0 =3D 1 <br>> backend_data_directory0 =3D '/var/lib/postgresql/8=
.4/main' <br>> backend_hostname1 =3D 'PPSQLBK2' <br>> backend_port1 =
=3D 5432 <br>> backend_weight1 =3D 1 <br>> backend_data_directory1 =
=3D '/var/lib/postgresql/8.4/main' <br>> enable_pool_hba =3D true <br>&g=
t; recovery_user =3D 'pgpool2' <br>> recovery_password =3D '' <br>> r=
ecovery_1st_stage_command =3D 'base-backup' <br>> recovery_2nd_stage_com=
mand =3D 'pgpool-recovery-pitr' <br>> recovery_timeout =3D 90 <br>> c=
lient_idle_limit_in_recovery =3D 10 <br>> <br>> <br>> Thanks for y=
our help <br>> <br>> -- <br>> <br>> Joaqu=C3=ADn Manuel Llano M=
ontero <br>> <br>> ________________________________________ <br>> =
<br>> C O MPAREX Espa=C3=B1a S.A. <br>> C/ Severo Ochoa, 20 <br>> =
06800 M=C3=A9rida (Badajoz) Espa=C3=B1a <br>> phone: +34 924 373 573 <br=
>> fax: +34 924 374 518 <br>> mobile: +34 615 848 019 <br>> email =
: joaquin.llano at comparex.es <br>> web: www.comparex.es <br>> <br></di=
v><br></div></body></html>
--=_100d2ae0-254d-4dd4-bba5-1902e53902ff--
More information about the pgpool-general
mailing list