[Pgpool-general] Online recovery during load

[Tatsuo Ishii]

> Hi,
> 
> Finally, I've found two reasons why it didn't work before:
> 1. after review of the client software, I've found that, sometimes, it
> didn't close the connection, and
> 2. we are not sure that the current value of sequences are flushed in
> the logs with the switch_xlog() function.
> 
> For 1., the solution simply consists in fixing the close connection bug.
> We have also left back persistent connections. It was finally not a
> good idea for a multithreaded application. Simple connections allow a
> better turnaround than with persistent connections.
> 
> For 2., I've noticed this problem by comparing the replicated back-end
> after online recovery. And the sequences are not synchronized if they
> have been used during the recovery. It seems that the switch_xlog
> doesn't flush the current value of sequences.
> 
> After looking for a solution, I've found by chance in the script
> pgpool_recovery_pitr (located in sample directory of pgpool-II 2.3.1)
> how to fix it:
> 
> # Force to flush current value of sequences to xlog
> psql -p $port -t -c 'SELECT datname FROM pg_database WHERE NOT
> datistemplate AND datallowconn' template1|
> while read i
> do
>  if [ "$i" != "" ];then
>    psql -p $port -c "SELECT setval(oid, nextval(oid)) FROM pg_class
> WHERE relkind = 'S'" $i
>  fi
> done
> 
> Mentioning this issue and its fix in the pgpool documentation should
> worthful IMHO.

Oops. I forgot to mention about it in the doc. Will fix.

> Now, the online recovery works like a charm during high load of read
> and write requests.

Great!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp