[Pgpool-general] Online recovery with sequence

[Tatsuo Ishii]

Hi,

In local pgpool mailing list in Japan, a user found that sequence
values among servers are out of sync after online recovery. This is
caused by the way how PostgreSQL logs sequence: it logs 32 on WAL
*before* PostgreSQL uses all of 32 sequence values. So after recovery,
recovered node's sequence advances about 32 than the one of the online
node. To handle the problem, I recommend to place following code in
your second stage recovery script.

psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1|
while read i
do
  if [ "$i" != "" ];then
    psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i
  fi
done

This should be put before:

psql -c 'SELECT pg_switch_xlog()' postgres

or some such.

Please note that with this script, after recovery all sequences in
database advance 1.
--
Tatsuo Ishii
SRA OSS, Inc. Japan