<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:12pt">Hi all,<br><br>After a further reading of pgpool tutorial i think that i found the problem, but i want to know if i understood this correctly : <br><br><div>
"Data synchronization is finalized during what is called "second stage".
Before entering the second stage, pgpool-II waits until all clients have disconnected.
It blocks any new incoming connection until the second stage is over.
</div>
<div>
After all connections have terminated, pgpool-II merges updated data between
the first stage and the second stage. This is the final data
synchronization step.
</div>
<div>
<font color="red">
Note that there is a restriction about online recovery. If pgpool-II itself
is installed on multiple hosts, online recovery does not work correctly,
because pgpool-II has to stop all clients during the 2nd stage of
online recovery. If there are several pgpool hosts, only one will have received
the online recovery command and will block connections. <span style="color: rgb(0, 0, 0);">"</span>
</font>
</div><br><div><span>So, what i understand from here is that when I perform online recovery I should stop the stand-by pgpool server. Also, on the primary server there should be no connections open left, and until the recovery is performed, no other connections will be opened. The entire cluster will be basically down when 2nd stage recovery process is running.</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>Are these assumptions correct ?</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>Also sometimes it happens that my standby pgpool node detects one postgresql backend as down and it degenerate it.<br>My question here is : What is
the business of pgpool standby node to detach postgres backends as long as it is the STAND-BY and not the ACTIVE node ?<br><br> <div>Regards,<br>Adrian Videanu</div><div><br></div> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 12pt;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 12pt;"> <div dir="ltr"> <hr size="1"> <font size="2" face="Arial"> <b><span style="font-weight:bold;">From:</span></b> Videanu Adrian <videanuadrian@yahoo.com><br> <b><span style="font-weight: bold;">To:</span></b> "pgpool-general@pgpool.net" <pgpool-general@pgpool.net> <br> <b><span style="font-weight: bold;">Sent:</span></b> Friday, January 10, 2014 12:21 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> [pgpool-general: 2395] native replication PITR problems<br> </font> </div> <div class="y_msg_container"><br><div
id="yiv4761271400"><div><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></div></div><br>_______________________________________________<br>pgpool-general mailing list<br><a ymailto="mailto:pgpool-general@pgpool.net" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br><br><br></div> </div> </div> </div></body></html>