Pgpool-II: sequences advanced by 1 after PITR recovery

Question

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:~> psql -h primary_node -p 5432 -d serial_test -c 'create schema sch' 
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'create table sch.mytable (id serial, name text);'
postgres@server01:~> 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:~> psql -h primary_node -p 5432 -d serial_test -c "insert into sch.mytable (name) values ('usera')"
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'select * from sch.mytable'
 id | name  
----+-------
  1 | usera
postgres@server01:~> 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:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 1 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4
postgres@server01:~> 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:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 2 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 5
postgres@server01:~> 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 1

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.