<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:12pt"><div><span>Hello, <br></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;"><span>I have a pgpool 3.3.2 cluster using native replication with 2 Postgresql 9.2 nodes with online recovery using PITR.</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;"><br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;"><span>The problem is that from time to time one of the
nodes get disconnected (I do not know why, because the load is very low and the machines are in the same subnet), and when I try to recovery it with pgpool-admin recovery button, after the first state the recovery process apparently freezes and nothing happens. During this pgpool cannot be accessed, in fact i guess that the connection are made but it somehow waits for something... .</span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;">Active Pgpool machine logs :<br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;
background-color: transparent; font-style: normal;"><br></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;">// the first postgresql node is declared dead (have no ideea why... how may i debug this kind of issues ?)<br><span></span></div><div style="color: rgb(0, 0, 0); font-size: 16px; font-family: HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif; background-color: transparent; font-style: normal;"><span>Jan 10 09:36:51 pgpool133 pgpool[26722]: wd_send_response: WD_STAND_FOR_LOCK_HOLDER received it<br>Jan 10 09:36:51 pgpool133 pgpool[26722]: degenerate_backend_set: 0 fail over request from pid 26722<br>Jan 10 09:36:51 pgpool133 pgpool[26703]: wd_start_interlock: start interlocking<br>Jan 10 09:36:53 pgpool133 pgpool[26703]: starting degeneration. shutdown host 192.168.91.33(5432)<br>Jan 10
09:36:53 pgpool133 pgpool[26703]: Restart all children<br>Jan 10 09:37:00 pgpool133 pgpool[26703]: wd_end_interlock: end interlocking<br>Jan 10 09:37:01 pgpool133 pgpool[26703]: failover: set new primary node: -1<br>Jan 10 09:37:01 pgpool133 pgpool[26703]: failover: set new master node: 1<br>Jan 10 09:37:01 pgpool133 pgpool[26703]: failover done. shutdown host 192.168.91.33(5432)<br>Jan 10 09:37:01 pgpool133 pgpool[27029]: worker process received restart request<br>Jan 10 09:37:02 pgpool133 pgpool[27028]: pcp child process received restart request<br>Jan 10 09:37:02 pgpool133 pgpool[26703]: PCP child 27028 exits with status 256 in failover()<br>Jan 10 09:37:02 pgpool133 pgpool[26703]: fork a new PCP child pid 32576 in failover()<br>Jan 10 09:37:02 pgpool133 pgpool[26703]: worker child 27029 exits with status 256<br>Jan 10 09:37:02 pgpool133 pgpool[26703]: fork a new worker child pid 32577<br></span></div><div> <br><br>Before start the recovery
process I deleted everything in the archive directory and in data directory to the node that was about to be recovered<br><br>// start the recovery process<br>Jan 10 09:43:07 pgpool133 pgpool[32576]: starting recovering node 0<br>Jan 10 09:43:08 pgpool133 pgpool[32576]: CHECKPOINT in the 1st stage done<br>Jan 10 09:43:08 pgpool133 pgpool[32576]: starting recovery command: "SELECT pgpool_recovery('basebackup.sh', '192.168.91.33', '/var/lib/postgresql/9.2/data')"<br>Jan 10 09:43:22 pgpool133 pgpool[32576]: 1st stage is done<br>Jan 10 09:43:22 pgpool133 pgpool[32576]: starting 2nd stage<br>... after that nothing happens<br><br><br>Online postgresql node logs : <br>+ DATA=/var/lib/postgresql/9.2/data<br>+ RECOVERY_TARGET=192.168.91.33<br>+ RECOVERY_DATA=/var/lib/postgresql/9.2/data<br>+ ARCHIVE_DIR=/var/lib/postgresql/9.2/archive<br>+ psql -c 'SELECT pg_start_backup('\''pgpoo-recovery'\'')'
postgres<br> pg_start_backup<br>-----------------<br> 1/36000020<br>(1 row)<br><br>+ rsync -C -a -c -e 'ssh -p 2022' --delete --exclude postmaster.log --exclude postmaster.pid --exclude postmaster.opts --exclude pg_log --exclude recovery.conf --<br>+ cat<br>+ scp -P 2022 recovery.conf 192.168.91.33:/var/lib/postgresql/9.2/data/<br>+ rm -f recovery.conf<br>+ psql -c 'SELECT pg_stop_backup()' postgres<br>NOTICE: pg_stop_backup complete, all required WAL segments have been archived<br> pg_stop_backup<br>----------------<br> 1/360000E0<br>(1 row)<br><br>P.S - I had experienced this kind of problems in the past but if i tried multiple times worked. But now, it seems that it doesn`t want to work anymore :)<br><br>............<br>Junst as i was writting this email the second Postgres node (and the last up) was declared down and pgpool was not acccepting conections due to the fact that no backend was online. After a complet
turnoff of both postgresql servers and pgpoll servers I could recover the node 1 also....<br><br><br>I have attached my relevant conf files.<br><br><br></div><div>Regards,<br>Adrian Videanu</div></div></body></html>