Pgpool-II: sequences advanced by 1 after PITR recovery I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4
In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes enabled in pgpool have old serial values.
Here is my commands:

postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c 'create schema sch' 
postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c 'create table sch.mytable (id serial, name text);'
postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c '\d sch.mytable'
                             Table "sch.mytable"
 Column |  Type   |                        Modifiers          
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('sch.mytable_id_seq'::regclass)
 name   | text    |    
postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c "insert into sch.mytable (name) values ('usera')"
postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c 'select * from sch.mytable'
 id | name  
----+-------
  1 | usera
postgres@server01:~&gt; psql -h primary_node -p 5432 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 2

# Online recovery via PITR (http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery)
# Recover the first standby node: standby_1
postgres@server01:~&gt; /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 1 &amp;
# DONE recovery
postgres@server01:~&gt; psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4
postgres@server01:~&gt; psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4

# Recover the second node: standby_2
postgres@server01:~&gt; /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 2 &amp;
# DONE recovery
postgres@server01:~&gt; psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6
postgres@server01:~&gt; psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 5
postgres@server01:~&gt; psql -h standby_2 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6


What am I doing wrong?

I've tried to set this options without success:

black_function_list = 'lastval,currval,nextval,setval'
insert_lock = true 


Resolved

Got it! Script pgpool_recvery_pitr:

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


After recovery all sequences in database advance by 1. 

There is no information in official docs about it:
They forgot about other enabled nodes in pgpool. This statement must be run for every enabled node in pgpool to keep all sequence points on all nodes equal.
Answer per the question:



Script pgpool_recvery_pitr:

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


After recovery all sequences in
database advance 1. 

There is no information in official docs about it: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery.

They forgot about other enabled nodes in pgpool. This statement MUST BE RUN for every enabled node in pgpool to keep all sequence points on all nodes equal.