[Pgpool-general] Online recovery during load

[Christophe Philemotte]

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.

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

Christophe Philemotte